索引系列十一--索引特性之有序與存列值優(yōu)化max

--MAX/MIN 的索引優(yōu)化

在吉隆等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站 網(wǎng)站設(shè)計(jì)制作按需開(kāi)發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計(jì),網(wǎng)絡(luò)營(yíng)銷推廣,成都外貿(mào)網(wǎng)站制作,吉隆網(wǎng)站建設(shè)費(fèi)用合理。

drop table t purge;

create table t as select * from dba_objects;

update t set object_id=rownum;

alter table t add constraint pk_object_id primary key (OBJECT_ID);

set autotrace on

set linesize 1000

select max(object_id) from t;

執(zhí)行計(jì)劃

-------------------------------------------------------------------------------------------

| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |              |     1 |    13 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |              |     1 |    13 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        431  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

          

--最小值老師的試驗(yàn)就無(wú)需展現(xiàn)執(zhí)行計(jì)劃結(jié)果了，必然和最大值的執(zhí)行計(jì)劃一樣！          

select min(object_id) from t;

--如果沒(méi)用到索引的情況是如下,請(qǐng)看看執(zhí)行計(jì)劃有何不同，請(qǐng)看看代價(jià)和邏輯讀的差異！

select /*+full(t)*/ max(object_id) from t;

執(zhí)行計(jì)劃

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    13 |   292   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |

|   2 |   TABLE ACCESS FULL| T    | 92407 |  1173K|   292   (1)| 00:00:04 |

---------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1047  consistent gets

          0  physical reads

          0  redo size

        431  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

---另外，可以做如下試驗(yàn)觀察在有索引的情況下，隨這記錄數(shù)增加，性能差異是否明顯？

set autotrace off

drop table t_max purge;

create table t_max as select * from dba_objects;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

insert into t_max select * from t_max;

select count(*) from t_max;

create index idx_t_max_obj on t_max(object_id);

set autotrace on 

select max(object_id) from t_max;

執(zhí)行計(jì)劃

--------------------------------------------------------------------------------------------

| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |               |     1 |    13 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE            |               |     1 |    13 |            |          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_T_MAX_OBJ |     1 |    13 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        431  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

          

/*

object_id如果允許為空，加個(gè)索引后，會(huì)走INDEX FULL SCAN (MIN/MAX)高效算法嗎，

當(dāng)然會(huì)了！取最大最小還怕啥空值？

*/ 

drop table t purge;

create table t as select * from dba_objects ;

create index idx_object_id on t(object_id);

set autotrace on

set linesize 1000

select max(object_id) from t;       

新聞名稱：索引系列十一--索引特性之有序與存列值優(yōu)化max
文章路徑：http://bm7419.com/article0/psdeoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)、云服務(wù)器、軟件開(kāi)發(fā)、外貿(mào)網(wǎng)站建設(shè)、App開(kāi)發(fā)、全網(wǎng)營(yíng)銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站網(wǎng)頁(yè)設(shè)計(jì)