今天在檢視awr報告的時候，有一句很簡單的sql語句引起了我的注意，因為它排在SQL Order by Reads的第2位。

Physical Reads	Executions	Reads per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module
13,092,700	0		9.47	294.8	52.7	41.06	6src3hcd9mpt3	T.O.A.D.

SQL Text

select * from mo1_memo where entity_id in (11889308, 11889311, 11888262, 11888261, 11889301) order by memo_date desc

這條語句看起來很簡單，自己印象中這個表中有一個相關的索引。
INDEX_NAME INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
MO1_MEMO_1IX FUNCTION-BASED NORMAL NONUNIQUE YES SYS_NC00031$,ENTITY_TYPE_ID,APP_ID TABLE N/A 554899259 01-APR-15 N
MO1_MEMO_2IX NORMAL NONUNIQUE YES MEMO_EXTERNAL_ID TABLE N/A 478847583 01-APR-15 N
MO1_MEMO_PK NORMAL UNIQUE YES MEMO_ID,APP_ID,ENTITY_KEY,PERIOD_KEY TABLE N/A 554900387 01-APR-15 N
有一個基於函式的索引，我們可以透過exp 或者dbms_metadata來得到相關的語句，發現索引是類似下面的形式。
create index MO1_MEMO_1IX on MO1_MEMO(entity_id desc,entity_type_id,app_id);
這是一個降序索引。對於這種降序索引，會在表中建立一個隱藏列。
SQL> select owner,column_name from all_tab_cols where table_name='MO1_MEMO' and hidden_column='YES';
APPO SYS_NC00031$

有了這些資訊，感覺應該是可以走索引掃描的。
但是得到的執行計劃中卻走了全表掃描，對一個資料量5億多資料的表走全表掃描，殺傷力是很大的。

但是奇怪的是使用下面兩種形式就沒有任何問題，索引都能正常啟用。
select * from mo1_memo where entity_id in (11889308) order by memo_date desc
select * from mo1_memo where entity_id =11889308 order by memo_date desc

一般來說降序索引在其值不為空的情況會啟用，根據目前的表結構來看entity_type_id和app_id有著not null constraint，所以應該能夠啟用才對。
帶著這個問題，我在11g的環境中簡單模擬了一把。
SQL> create table test as select *from all_objects where rownum<10000;
Table created.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
SQL> create index idx_test on test(object_id desc,object_name,object_type);
Index created.
SQL> select object_id from test where rownum<3;
OBJECT_ID
----------
2880583
2880575
SQL> set autot trace exp stat
下面兩種情況的執行計劃是一致的。

SQL> select *from test where object_id in (2880583,2880575);
SQL> select *from test where object_id=2880583;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 127 | 20066 | 5 |
|* 1 | TABLE ACCESS FULL| TEST | 127 | 20066 | 5 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2880575 OR "OBJECT_ID"=2880583)

SQL> select *from test where object_id in (2880583)
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 316 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 316 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3BFCA6F9ABFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=2880583)

根據上面的輸出，感覺降序索引的細節上還有存在一定的問題，在最佳化器中可能沒有很好的支援，檢視MOS也沒有找到相關的bug.

但是在12c的環境中，結果卻明顯不同，可見再最佳化器內部對於這種場景已經做了最佳化。
SQL> create table test as select *from all_objects where rownum<10000;
Table created.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
SQL> create index idx_test on test(object_id desc,object_name,object_type);
Index created.
SQL> select object_id from test where rownum<3;
OBJECT_ID
----------
10359
10358
SQL> set autot trace exp stat
SQL> select object_name from test where object_id in(10359,10358);
Execution Plan
----------------------------------------------------------
Plan hash value: 3459894390
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 158 | 3 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST | 2 | 158 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3CFDFBC4FF') OR
SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3CFDFBC3FF'))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=10358 OR
SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=10359)

所以技術的進步總是一點一滴，新版本中已經做了修復，但是目前來看11g還是主流，所以我們在建立降序索引的時候還是需要注意，避免一些不必要的情況發生。

一條簡單的sql在11g和12c中的不同

相關文章