其中 rownum=1 查询在 Oracle 中花费时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5307152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
where rownum=1 query taking time in Oracle
提问by Harish
I am trying to execute a query like
我正在尝试执行类似的查询
select * from tableName where rownum=1
This query is basically to fetch the column names of the table.There are more than million records in the table.When I put the above condition its taking so much time to fetch the first row.Is there any alternate to get the first row.
这个查询基本上是为了获取表的列名。表中有超过一百万条记录。当我把上面的条件放在获取第一行时，它花费了很多时间。是否有其他替代方法来获取第一行。
回答by Vincent Malgrat
This question has already been answered, I will just provide an explanation as to why sometimes a filter ROWNUM=1 or ROWNUM <= 1 may result in a long response time.
这个问题已经回答过了，我只是解释一下为什么有时过滤器 ROWNUM=1 或 ROWNUM <= 1 可能会导致响应时间过长。
When encountering a ROWNUM filter (on a single table), the optimizer will produce a FULL SCAN with COUNT STOPKEY. This means that Oracle will start to read rows until it encounters the first N rows (here N=1). A full scan reads blocks from the first extent to the high water mark. Oracle has no way to determine which blocks contain rows and which don't beforehand, all blocks will therefore be read until N rows are found. If the first blocks are empty, it could result in many reads.
当遇到 ROWNUM 过滤器（在单个表上）时，优化器将生成带有 COUNT STOPKEY 的 FULL SCAN。这意味着 Oracle 将开始读取行，直到遇到前 N 行（这里 N=1）。完整扫描从第一个范围读取块到高水位标记。Oracle 无法预先确定哪些块包含行，哪些不包含行，因此将读取所有块，直到找到 N 行。如果第一个块是空的，则可能会导致多次读取。
Consider the following:
考虑以下：
SQL> /* rows will take a lot of space because of the CHAR column */
SQL> create table example (id number, fill char(2000));
Table created
SQL> insert into example
2 select rownum, 'x' from all_objects where rownum <= 100000;
100000 rows inserted
SQL> commit;
Commit complete
SQL> delete from example where id <= 99000;
99000 rows deleted
SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from example where rownum = 1;
Elapsed: 00:00:05.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=2015)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'EXAMPLE' (TABLE) (Cost=7 Card=1588 [..])
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33211 consistent gets
25901 physical reads
0 redo size
2237 bytes sent via SQL*Net to client
278 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see the number of consistent gets is extremely high (for a single row). This situation could be encountered in some cases where for example, you insert rows with the /*+APPEND*/hint (thus above high water mark), and you also delete the oldest rows periodically, resulting in a lot of empty space at the beginning of the segment.
如您所见，一致获取的数量非常高（对于单行）。在某些情况下可能会遇到这种情况，例如，您插入带有/*+APPEND*/提示的行（因此高于高水位线），并且您还定期删除最旧的行，从而导致段开头有大量空白空间。
回答by Jon Heller
Try this:
尝试这个：
select * from tableName where rownum<=1
There are some weird ROWNUM bugs, sometimes changing the query very slightly will fix it. I've seen this happen before, but I can't reproduce it.
有一些奇怪的 ROWNUM 错误，有时稍微更改查询会修复它。我以前见过这种情况，但我无法重现。
Here are some discussions of similar issues: http://jonathanlewis.wordpress.com/2008/03/09/cursor_sharing/and http://forums.oracle.com/forums/thread.jspa?threadID=946740&tstart=1
以下是对类似问题的一些讨论：http: //jonathanlewis.wordpress.com/2008/03/09/cursor_sharing/和http://forums.oracle.com/forums/thread.jspa?threadID=946740&tstart=1
回答by paxdiablo
Surely Oracle has meta-data tables that you can use to get column names, like the sysibm.syscolumnstable in DB2?
Oracle 肯定有可以用来获取列名的元数据表，比如sysibm.syscolumnsDB2 中的表？
And, after a quick web search, that appears to be the case: see ALL_TAB_COLUMNS.
并且，在快速网络搜索之后，情况似乎是这样：请参阅ALL_TAB_COLUMNS。
I'd use those rather than go to the actual table, something like (untested):
我会使用这些而不是去实际的桌子，比如（未经测试）：
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = "MYTABLE"
ORDER BY COLUMN_NAME;
If you arehell-bent on finding out why your query is slow, you should revert to the standard method: asking your DBMS to explain the execution plan of the query for you. For Oracle, see section 9 of this document.
如果你是拼命找出为什么你的查询速度很慢，你应该恢复到标准的方法：问你的DBMS解释查询你的执行计划。对于 Oracle，请参阅本文档的第 9 节。
There's a conversation over at Ask Tom - Oraclethat seems to suggest the row numbers are created afterthe select phase, which may mean the query is retrieving all rows anyway. The explainwill probably help establish that. If it contains FULLwithout COUNT STOPKEY, then that may explain the performance.
有一个对话Ask Tom - Oracle似乎表明行号是在选择阶段之后创建的，这可能意味着查询无论如何都会检索所有行。这explain可能有助于确立这一点。如果它包含FULLwithout COUNT STOPKEY，那么这可以解释性能。
Beyond that, my knowledge of Oracle specifics diminishes and you will have to analyse the explainfurther.
除此之外，我对 Oracle 细节的了解减少了，您将不得不explain进一步分析。
回答by DCookie
Your query is doing a full table scan and then returning the first row.
您的查询正在执行全表扫描，然后返回第一行。
Try
尝试
SELECT * FROM table WHERE primary_key = primary_key_value;
The first row, particularly as it pertains to ROWNUM, is arbitrarily decided by Oracle. It may not be the same from query to query, unless you provide an ORDER BY clause.
第一行，特别是与 ROWNUM 相关的行，由 Oracle 任意决定。除非您提供 ORDER BY 子句，否则查询之间的情况可能不同。
So, picking a primary key value to filter by is as good a method as any to get a single row.
因此，选择要过滤的主键值与获取单行的任何方法一样好。
回答by mazaneicha
I think you're slightly missing the concept of ROWNUM - according to Oracle docs: "ROWNUM is a pseudo-column that returns a row's position in a result set. ROWNUM is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause."So it returns ANY row that it consideres #1 in the result set which in your case will contain 1M rows.
我认为您稍微错过了 ROWNUM 的概念 - 根据 Oracle 文档：“ROWNUM 是一个伪列，它返回结果集中的行位置。在从数据库中选择记录之后和执行 ORDER 之前评估 ROWNUM BY 条款。” 因此它返回它认为结果集中#1 的任何行，在您的情况下将包含1M 行。
You may want to check out a ROWID pseudo-column: http://psoug.org/reference/pseudocols.html
您可能想查看一个 ROWID 伪列：http: //psoug.org/reference/pseudocols.html
回答by jjurach
I've recently had the same problem you're describing: I want one row from the very large table as a quick, dirty, simple introspection, and "where rownum=1" alone behaves very poorly. Below is a remedy which worked for me.
我最近遇到了你所描述的同样问题：我想要从非常大的表中取出一行作为快速、肮脏、简单的内省，而“where rownum=1”本身的行为非常糟糕。以下是对我有用的补救措施。
Select the max() of the first term of some index, and then use it to choose some small fraction of all rows with "rownum=1". Suppose my table has some index on numerical "group-id", and compare this:
选择某个索引的第一项的 max()，然后用它从所有具有“rownum=1”的行中选择一小部分。假设我的表在数字“group-id”上有一些索引，然后比较一下：
select * from my_table where rownum = 1;
-- Elapsed: 00:00:23.69
with this:
有了这个：
select * from my_table where rownum = 1
and group_id = (select max(group_id) from my_table);
-- Elapsed: 00:00:00.01
