[20191219]降序索引與取最大值.txt

[20191219]降序索引與取最大值.txt

--//開發濫用降序索引,今天發現一個問題就是取最大值.透過例子說明:

1.環境:
SCOTT@book> @ ver1
PORT_STRING         VERSION        BANNER
------------------- -------------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter system set pga_aggregate_target=4G;
System altered.

SCOTT@book> create table t (id1 number,id2 number,vc varchar2(50));
Table created.

create index i_t_id1 on t (id1);
create index i_t_id2desc on t (id2 desc);

SCOTT@book> insert into t select rownum,rownum,lpad('a',50,'a') from dual connect by level<=1e6;
1000000 rows created.

SCOTT@book> commit ;
Commit complete.

--//分析略.
SCOTT@book> select index_name,index_type,blevel,leaf_blocks,distinct_keys from dba_indexes where owner=user  and table_name='T';
INDEX_NAME                     INDEX_TYPE                      BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------------------------- ---------- ----------- -------------
I_T_ID1                        NORMAL                               2        1999       1000000
I_T_ID2DESC                    FUNCTION-BASED NORMAL                2        4283       1000000
--//I_T_ID2DESC的LEAF_BLOCKS=4283.

2.測試:
SCOTT@book> select max(id1) from t;
  MAX(ID1)
----------
   1000000

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  25ktx1ht4fs1u, child number 0
-------------------------------------
select max(id1) from t
Plan hash value: 2049239052
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE            |         |      1 |      1 |     5 |            |          |      1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_ID1 |      1 |      1 |     5 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
--//id1欄位建立的是普通索引,取最大值僅僅3個邏輯讀.

SCOTT@book> select max(id2) from t;
  MAX(ID2)
----------
   1000000

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gtagtkz33v9n8, child number 0
-------------------------------------
select max(id2) from t
Plan hash value: 2966233522
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |  2743 (100)|          |      1 |00:00:00.22 |    9285 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     5 |            |          |      1 |00:00:00.22 |    9285 |
|   2 |   TABLE ACCESS FULL| T    |      1 |   1000K|  4882K|  2743   (1)| 00:00:33 |   1000K|00:00:00.13 |    9285 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
--//執行計劃走的是全表掃描.無法充分利用降序索引.

SCOTT@book> alter table t modify (id2 not null);
Table altered.

SCOTT@book> select  max(id2) from t;
  MAX(ID2)
----------
   1000000

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6pj15dkuv35kb, child number 0
-------------------------------------
select  max(id2) from t
Plan hash value: 2206409122
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |        |       |  1166 (100)|          |      1 |00:00:00.25 |    4362 |
|   1 |  SORT AGGREGATE       |             |      1 |      1 |     5 |            |          |      1 |00:00:00.25 |    4362 |
|   2 |   INDEX FAST FULL SCAN| I_T_ID2DESC |      1 |   1000K|  4882K|  1166   (1)| 00:00:14 |   1000K|00:00:00.11 |    4362 |
-------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
--//即使設定id2 not null,執行計劃選擇的也是INDEX FAST FULL SCAN,邏輯讀依舊很高.
--//幾乎很少有人這麼寫:
SCOTT@book> select id2 from (select  id2 from t order by id2 desc) where rownum=1;
       ID2
----------
   1000000

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8aska3nqm81p2, child number 0
-------------------------------------
select id2 from (select  id2 from t order by id2 desc) where rownum=1
Plan hash value: 893305471
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY    |             |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |
|   2 |   VIEW            |             |      1 |      1 |    13 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   3 |    INDEX FULL SCAN| I_T_ID2DESC |      1 |   1000K|  4882K|     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)

3.繼續探究:
--//如果執行如下,看執行計劃可以發現:
select  * from t where id2=1 ;

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SYS_OP_DESCEND("ID2")=HEXTORAW('3EFDFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ID2"))=1)

--//但是如果你執行如下:
SCOTT@book> select SYS_OP_UNDESCEND(SYS_OP_DESCEND("ID2")),id2  from t where rownum=1;
SYS_OP_UNDESCEND(SYS_OP_DESCEND("ID2")) ID2
--------------------------------------- ---
C20539                                  456
--//利用這樣的也可以獲得對應編碼.但是SYS_OP_UNDESCEND(SYS_OP_DESCEND("ID2"))不會等於id2.

SCOTT@book> select SYS_OP_UNDESCEND(SYS_OP_DESCEND(1)),1 from dual;
SYS_          1
---- ----------
C102          1

--//為什麼oracle執行中filter可以(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ID2"))=1)?有點搞不懂....
--//而實際上返回的是對應數字的oracle編碼.

SCOTT@book> select utl_raw.cast_to_number(x) from (select (SYS_OP_UNDESCEND(min(SYS_OP_DESCEND(ID2)))) x from t);
UTL_RAW.CAST_TO_NUMBER(X)
-------------------------
                  1000000
--//注意是取最小值.開發更不可能這樣寫!!
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3vk18a82yxt7y, child number 0
-------------------------------------
select utl_raw.cast_to_number(x) from (select
(SYS_OP_UNDESCEND(min(SYS_OP_DESCEND(ID2)))) x from t)
Plan hash value: 2062024120
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  VIEW                       |             |      1 |      1 |    19 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   2 |   SORT AGGREGATE            |             |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       3 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| I_T_ID2DESC |      1 |      1 |     6 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$2 / T@SEL$2

--//補充一點如果這些寫:
SCOTT@book> select utl_raw.cast_to_number(SYS_OP_UNDESCEND(min(SYS_OP_DESCEND(ID2)))) from t;
UTL_RAW.CAST_TO_NUMBER(SYS_OP_UNDESCEND(MIN(SYS_OP_DESCEND(ID2))))
------------------------------------------------------------------
                                                           1000000

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9bd7fdyvd2sh6, child number 0
-------------------------------------
select utl_raw.cast_to_number(SYS_OP_UNDESCEND(min(SYS_OP_DESCEND(ID2)))
) from t
Plan hash value: 2206409122
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |        |       |  1166 (100)|          |      1 |00:00:00.22 |    4362 |
|   1 |  SORT AGGREGATE       |             |      1 |      1 |     6 |            |          |      1 |00:00:00.22 |    4362 |
|   2 |   INDEX FAST FULL SCAN| I_T_ID2DESC |      1 |   1000K|  5859K|  1166   (1)| 00:00:14 |   1000K|00:00:00.12 |    4362 |
-------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
21 rows selected.

--//這樣寫oracle無法充分利用取min/max的特性.不知道為什麼,那位解析看看.

4.總結:
--//1.降序索引不能亂用.
--//2.降序索引適應的場景很少,僅僅oraer by a desc,b asc之類的一正一反可以使用.我僅僅能找到這個例子.
--//3.降序索引對於自增序列欄位會導致索引變大的可能.
--//4.總之不要張冠李戴不加思索的亂用任何技術,再次看到一個豆腐渣中豆腐渣工程.