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

来源：这里教程网时间：2026-03-03 14:43:46 作者：

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