26.读书笔记收获不止Oracle之 位图索引即席查询
|
26.读书笔记收获不止Oracle之 位图索引即席查询 Drop table t purge; create table t (name_id, gender not null, location not null, age_group not null, data) as selectrownum,decode(ceil(dbms_random.value(0,2)), 1,'m', 2,'f')gender, ceil(dbms_random.value(1,50)) location, decode(ceil(dbms_random.value(0,3)),'child','young', 3,'middle_age', 4,'old'), rpad('*',20,'*') from dual connect byrownum<=100000; 1. 无索引进行查询Set linesize 1000 Set autotrace traceonly SQL> select * from t wheregender='m' and location in (1,10,30) andage_group='child'; 656 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0| SELECT STATEMENT | |1013 | 39507 | 171 (1)| 00:00:01 | |* 1| TABLE ACCESS FULL| T | 1013 | 39507 | 171(1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 1- filter("AGE_GROUP"='child' AND "GENDER"='m' AND("LOCATION"=1 OR "LOCATION"=10 OR"LOCATION"=30)) Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 652 consistent gets 0physical reads 0 redosize 16591 bytes sent via SQL*Net toclient 1024 bytes received via SQL*Netfrom client 45SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 656 rows processed 2. 建立联合索引建立三个列的联合索引 SQL> create index idx_union ont(gender,location,age_group); Index created. SQL> select * from t where gender='m' and location in (1,30) andage_group='child'; 656 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0| SELECT STATEMENT | |680 | 26520 | 171 (1)| 00:00:01 | |* 1| TABLE ACCESS FULL| T |680 | 26520 | 171 (1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 1- filter("AGE_GROUP"='child' AND "GENDER"='m' AND("LOCATION"=1 OR "LOCATION"=10 OR"LOCATION"=30)) Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 652 consistent gets 0physical reads 0 redosize 16591 bytes sent via SQL*Net toclient 1024 bytes received via SQL*Netfrom client 45SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 656 rows processed 还是走的全表扫描，进行强制走索引如下： 3. 强制走索引SQL> select /*+index(t,idx_union)*/ * from t where gender='m' and location in (1,30) andage_group='child'; 656 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 886844991 -------------------------------------------------------------------------------------------------- | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 680 | 26520 | 525(0)| 00:00:01 | | 1| INLIST ITERATOR | | | | | | | 2| TABLE ACCESS BY INDEX ROWID BATCHED|T |680 | 26520 | 525 (0)| 00:00:01 | |* 3| INDEX RANGE SCAN | IDX_UNION | 680 | | 5(0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 3- access("GENDER"='m' AND ("LOCATION"=1 OR"LOCATION"=10 OR "LOCATION"=30) AND "AGE_GROUP"='child') Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 581 consistent gets 6physical reads 0 redosize 36681 bytes sent via SQL*Net toclient 1024 bytes received via SQL*Netfrom client 45SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 656 rows processed 强制走索引的代价比用全表扫描高很多的。主要集中在回表这个阶段。 4. 位图索引看看走位图索引 SQL> create bitmap index gender_idx ont(gender); Index created. SQL> create bitmap index location_idx ont(location); Index created. SQL> create bitmap index age_group_idxon t(age_group); Index created. SQL> select * from t wheregender='m' and location in (1,30) andage_group='child'; 656 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3416549716 ----------------------------------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 680| 26520 | 118 (0)| 00:00:01 | | 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 680| 26520 | 118 (0)| 00:00:01 | | 2| BITMAP CONVERSION TO ROWIDS | | | | | | | 3| BITMAP AND | | | | | | | 4| BITMAP OR | | | | | | |* 5| BITMAP INDEX SINGLE VALUE |LOCATION_IDX | | | | | |* 6| BITMAP INDEX SINGLE VALUE |LOCATION_IDX | | | | | |* 7| BITMAP INDEX SINGLE VALUE |LOCATION_IDX | | | | | |* 8| BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX | | | | | |* 9| BITMAP INDEX SINGLE VALUE | GENDER_IDX | | | | | ----------------------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 5- access("LOCATION"=1) 6- access("LOCATION"=10) 7- access("LOCATION"=30) 8- access("AGE_GROUP"='child') 9- access("GENDER"='m') Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 443 consistent gets 5physical reads 0 redosize 36681 bytes sent via SQL*Net toclient 1024 bytes received via SQL*Netfrom client 45SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 656 rows processed 代价只有118，比全表扫描代价要小很多。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |