为什么 Oracle 使用 ORDER BY 忽略索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13497887/
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
Why is Oracle ignoring index with ORDER BY?
提问by Bogdan Minciu
My intention is to obtain a paginated resultset of customers. I am using this algorithm, from Tom:
我的目的是获得分页的客户结果集。我正在使用Tom 的这个算法：
select * from (
select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
from CUSTOMER C
)
where RN between 1 and 20
order by RN;
I also have an index defined on the column "CUSTOMER"."FIRST_NAME":
我还在“CUSTOMER”.“FIRST_NAME”列上定义了一个索引：
CREATE INDEX CUSTOMER_FIRST_NAME_TEST ON CUSTOMER (FIRST_NAME ASC);
The query returns the expected resultset, but from the explain plan I notice that the index is not used:
查询返回预期的结果集，但从解释计划中我注意到未使用索引：
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15467 | 679K| 157 (3)| 00:00:02 |
| 1 | SORT ORDER BY | | 15467 | 679K| 157 (3)| 00:00:02 |
|* 2 | VIEW | | 15467 | 679K| 155 (2)| 00:00:02 |
|* 3 | WINDOW SORT PUSHED RANK| | 15467 | 151K| 155 (2)| 00:00:02 |
| 4 | TABLE ACCESS FULL | CUSTOMER | 15467 | 151K| 154 (1)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=1 AND "RN"<=20)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)
I am using Oracle 11g. Since I just query for the first 20 rows, ordered by the indexed column, I would expect the index to be used.
我正在使用 Oracle 11g。由于我只查询按索引列排序的前 20 行，因此我希望使用索引。
Why is the Oracle optimizer ignoring the index? I assume it's something wrong with the pagination algorithm, but I can't figure out what.
为什么 Oracle 优化器会忽略索引？我认为分页算法有问题，但我不知道是什么。
Thanks.
谢谢。
回答by DazzaL
more than likely your FIRST_NAME column is nullable.
您的 FIRST_NAME 列很可能可以为空。
SQL> create table customer (first_name varchar2(20), last_name varchar2(20));
Table created.
SQL> insert into customer select dbms_random.string('U', 20), dbms_random.string('U', 20) from dual connect by level <= 100000;
100000 rows created.
SQL> create index c on customer(first_name);
Index created.
SQL> explain plan for select * from (
2 select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
3 from CUSTOMER C
4 )
5 where RN between 1 and 20
6 order by RN;
Explained.
SQL> @explain ""
Plan hash value: 1474094583
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 117K| 2856K| | 1592 (1)| 00:00:20 |
| 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 1592 (1)| 00:00:20 |
|* 2 | VIEW | | 117K| 2856K| | 744 (2)| 00:00:09 |
|* 3 | WINDOW SORT PUSHED RANK| | 117K| 1371K| 2304K| 744 (2)| 00:00:09 |
| 4 | TABLE ACCESS FULL | CUSTOMER | 117K| 1371K| | 205 (1)| 00:00:03 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=1 AND "RN"<=20)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)
Note
-----
- dynamic sampling used for this statement (level=2)
21 rows selected.
SQL> alter table customer modify first_name not null;
Table altered.
SQL> explain plan for select * from (
2 select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
3 from CUSTOMER C
4 )
5 where RN between 1 and 20
6 order by RN;
Explained.
SQL> @explain ""
Plan hash value: 1725028138
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 117K| 2856K| | 850 (1)| 00:00:11 |
| 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 850 (1)| 00:00:11 |
|* 2 | VIEW | | 117K| 2856K| | 2 (0)| 00:00:01 |
|* 3 | WINDOW NOSORT STOPKEY| | 117K| 1371K| | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | C | 117K| 1371K| | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=1 AND "RN"<=20)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)
Note
-----
- dynamic sampling used for this statement (level=2)
21 rows selected.
SQL>
add a NOT NULL in there to resolve it.
在那里添加一个 NOT NULL 来解决它。
SQL> explain plan for select * from (
2 select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
3 from CUSTOMER C
4 where first_name is not null
5 )
6 where RN between 1 and 20
7 order by RN;
Explained.
SQL> @explain ""
Plan hash value: 1725028138
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 117K| 2856K| | 850 (1)| 00:00:11 |
| 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 850 (1)| 00:00:11 |
|* 2 | VIEW | | 117K| 2856K| | 2 (0)| 00:00:01 |
|* 3 | WINDOW NOSORT STOPKEY| | 117K| 1371K| | 2 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN | C | 117K| 1371K| | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=1 AND "RN"<=20)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "FIRST_NAME")<=20)
4 - filter("FIRST_NAME" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
SQL>
回答by Andomar
You're querying for more columns than first_name. The index on first_namejust contains the first_namecolumn and a reference to the table. So to retrieve the other columns, Oracle has to perform a lookup to the table itself for each row. Most databases try to avoid this if they can't guarantee a low record count.
您要查询的列数多于first_name。上的索引first_name仅包含first_name列和对表的引用。因此，要检索其他列，Oracle 必须为每一行查找表本身。大多数数据库如果不能保证低记录数，就会尽量避免这种情况。
A database is typically not smart enough to know the effects of a whereclause on a row_numbercolumn. However, your hint /*+ FIRST_ROWS(20) */might have done the trick.
数据库通常不够聪明，无法知道where子句对row_number列的影响。但是，您的提示/*+ FIRST_ROWS(20) */可能成功了。
Perhaps the table is really small, so that Oracle expects the table scan to be cheaper than lookups, even for just 20 rows.
也许表真的很小，所以 Oracle 期望表扫描比查找便宜，即使只有 20 行。
