--DISTINCT測試前的準(zhǔn)備
成都創(chuàng)新互聯(lián)專注于企業(yè)全網(wǎng)營銷推廣、網(wǎng)站重做改版、麻城網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5開發(fā)、成都商城網(wǎng)站開發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計(jì)等建站業(yè)務(wù),價格優(yōu)惠性價比高,為麻城等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
alter table T modify OBJECT_ID not null;
update t set object_id=2;
update t set object_id=3 where rownum<=25000;
commit;
/*
在oracle10g的R2環(huán)境之后,DISTINCT由于其 HASH UNIQUE的算法導(dǎo)致其不會產(chǎn)生排序,其調(diào)整的
ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE
*/
set linesize 1000
set autotrace traceonly
select distinct object_id from t ;
執(zhí)行計(jì)劃
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88780 | 1127K| | 717 (1)| 00:00:09 |
| 1 | HASH UNIQUE | | 88780 | 1127K| 1752K| 717 (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL| T | 88780 | 1127K| | 292 (1)| 00:00:04 |
-----------------------------------------------------------------------------------
統(tǒng)計(jì)信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1047 consistent gets
0 physical reads
0 redo size
462 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
/*不過雖然沒有排序,通過觀察TempSpc可知distinct消耗PGA內(nèi)存進(jìn)行HASH UNIQUE運(yùn)算,
接下來看看建了索引后的情況,TempSpc關(guān)鍵字立即消失,COST也立即下降許多,具體如下*/
--為T表的object_id列建索引
create index idx_t_object_id on t(object_id);
set linesize 1000
set autotrace traceonly
select /*+index(t)*/ distinct object_id from t ;
執(zhí)行計(jì)劃
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88780 | 1127K| 582 (1)| 00:00:07 |
| 1 | SORT UNIQUE NOSORT| | 88780 | 1127K| 582 (1)| 00:00:07 |
| 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 88780 | 1127K| 158 (1)| 00:00:02 |
--------------------------------------------------------------------------------------
統(tǒng)計(jì)信息
----------------------------------------------------------
0 recursive calls
0 db block gets
145 consistent gets
0 physical reads
0 redo size
462 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
名稱欄目:索引系列九--索引特性之有序優(yōu)化distinct
分享地址:http://muchs.cn/article48/pihgep.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供虛擬主機(jī)、服務(wù)器托管、用戶體驗(yàn)、App設(shè)計(jì)、網(wǎng)站營銷、小程序開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)