为什么Oracle在ORDER BY时忽略索引？

Question

为什么Oracle在ORDER BY时忽略索引？

sqlperformanceoracledatabase-performancesql-execution-plan

18

我想要获取一个分页结果集的客户信息。我使用的是这个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;

我在"顾客"."名字"这一列上也定义了一个索引：

CREATE INDEX CUSTOMER_FIRST_NAME_TEST ON CUSTOMER (FIRST_NAME ASC);

查询返回了预期的结果集，但从执行计划中我注意到索引并未被使用：

--------------------------------------------------------------------------------------
| 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)

我正在使用Oracle 11g。由于我只查询前20行，按索引列排序，我期望索引会被使用。

为什么Oracle优化器没有使用索引？我猜测这可能是分页算法的问题，但是我无法确定具体原因。

谢谢。

- Bogdan Minciu

2个回答

0

您正在查询比first_name更多的列。 first_name上的索引仅包含first_name列和对表的引用。因此，为了检索其他列，Oracle必须为每一行执行对表本身的查找。大多数数据库会尽力避免这种情况，如果无法保证记录数量较少。

通常，数据库不足够聪明，无法知道where子句对row_number列的影响。但是，您的提示/*+ FIRST_ROWS(20) */可能已经解决了问题。

也许表格真的很小，因此即使只有20行，Oracle也期望表格扫描比查找更便宜。

- Andomar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DazzaL · Accepted Answer

很可能您的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>

在那里添加一个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>