[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.总之不要张冠李戴不加思索的乱用任何技术,再次看到一个豆腐渣中豆腐渣工程.