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

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

--//連結提到降序索引取最大最小值走的是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