Oracle이 ORDER BY로 인덱스를 무시하는 이유는 무엇입니까?

Oracle이 ORDER BY로 인덱스를 무시하는 이유는 무엇입니까?

제 의도는 페이지별 고객 결과 집합을 얻는 것입니다.저는 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;

또한 "고객" 열에 정의된 색인이 있습니다. "FIRST_NAME":

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 Optimizer가 인덱스를 무시하는 이유는 무엇입니까?페이지 번호 알고리즘이 잘못된 것 같은데, 뭐가 뭔지 모르겠어요.

감사해요.

FIRST_NAME 열이 null일 가능성이 높습니다.

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>

다음보다 많은 열을 쿼리하고 있습니다.first_name인덱스:first_name다음을 포함할 뿐입니다.first_name열 및 표에 대한 참조.따라서 다른 열을 검색하려면 Oracle은 각 행에 대해 테이블 자체를 검색해야 합니다.대부분의 데이터베이스는 낮은 레코드 수를 보장할 수 없는 경우 이 문제를 방지하려고 합니다.

데이터베이스는 일반적으로 다음의 효과를 알 수 있을 만큼 충분히 똑똑하지 않습니다.where에 대한 조항.row_number기둥.하지만 당신의 힌트는/*+ FIRST_ROWS(20) */그럴 수도 있었을 겁니다

아마도 테이블이 정말 작기 때문에 Oracle은 테이블 스캔이 검색보다 저렴할 것으로 예상합니다. 심지어 20개의 행에 대해서도 말입니다.

언급URL : https://stackoverflow.com/questions/13497887/why-is-oracle-ignoring-index-with-order-by