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

[20231116]降序索引取最大值.txt --//链接https://jonathanlewis.wordpress.com/2023/11/01/descending-max/,提到降序索引取最大最小值走的是INDEX FAST FULL SCAN. --//我前面提过,许多场合下不需要建立降序索引,大部分普通索引都可以解决问题,我不像我以前系统建立一大堆降序索引.重复测试作者 --//的例子: 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 create table t1 as select  * from    all_objects where   rownum <= 10000; alter table t1 modify object_name not null; -- create index t1_i1a on t1(object_name); create index t1_i1d on t1(object_name desc); execute dbms_stats.gather_table_stats(user,'t1',cascade=>true) 2.测试: SCOTT@book> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book> select  max(object_name) from t1; MAX(OBJECT_NAME) ------------------------------ _utl$_lnc_ind_parts SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  a5086qgsk6f7r, child number 0 ------------------------------------- select  max(object_name) from t1 Plan hash value: 219064265 -------------------------------------------------------------------------------------------------------------------------- | Id  | Operation             | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |        |      1 |        |       |    13 (100)|          |      1 |00:00:00.01 |      48 | |   1 |  SORT AGGREGATE       |        |      1 |      1 |    19 |            |          |      1 |00:00:00.01 |      48 | |   2 |   INDEX FAST FULL SCAN| T1_I1D |      1 |  10000 |   185K|    13   (0)| 00:00:01 |  10000 |00:00:00.01 |      48 | -------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T1@SEL$1 --//作者给出了一个重写的sql语句,好像我以前也写过类似语句. SCOTT@book> select /*+ index(t1) */ utl_raw.cast_to_varchar2( sys_op_undescend( min(sys_op_descend(object_name)))) from t1; UTL_RAW.CAST_TO_VARCHAR2(SYS_OP_UNDESCEND(MIN(SYS_OP_DESCEND(OBJECT_NAME)))) ---------------------------------------------------------------------------- _utl$_lnc_ind_parts SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8cqv0nbw9j5fn, child number 0 ------------------------------------- select /*+ index(t1) */ utl_raw.cast_to_varchar2( sys_op_undescend( min(sys_op_descend(object_name)))) from t1 Plan hash value: 2867767823 -------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |        |      1 |        |       |    44 (100)|          |      1 |00:00:00.01 |       2 | |   1 |  SORT AGGREGATE             |        |      1 |      1 |    20 |            |          |      1 |00:00:00.01 |       2 | |   2 |   FIRST ROW                 |        |      1 |  10000 |   195K|    44   (0)| 00:00:01 |      1 |00:00:00.01 |       2 | |   3 |    INDEX FULL SCAN (MIN/MAX)| T1_I1D |      1 |  10000 |   195K|    44   (0)| 00:00:01 |      1 |00:00:00.01 |       2 | -------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    3 - SEL$1 / T1@SEL$1 SCOTT@book> create index t1_i1a on t1(object_name); Index created.     SCOTT@book> select  max(object_name) from t1; MAX(OBJECT_NAME) ------------------------------ _utl$_lnc_ind_parts SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  a5086qgsk6f7r, child number 0 ------------------------------------- select  max(object_name) from t1 Plan hash value: 1421318352 ---------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                  | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | ---------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |        |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |      1 | |   1 |  SORT AGGREGATE            |        |      1 |      1 |    19 |            |          |      1 |00:00:00.01 |       2 |      1 | |   2 |   INDEX FULL SCAN (MIN/MAX)| T1_I1A |      1 |      1 |    19 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 | ---------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T1@SEL$1