1. 首页
  2. IT资讯

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2669477/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182792.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code