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，比全表扫描代价要小很多。
