1.分别给出一个B-tree索引针对全表扫描性能高和低的例子。
--查看数据分布量
SQL> select count(1) from t;
COUNT(1)
----------
3199936
SQL> select owner,count(1) from t group by rollup(owner) order by count(1) desc;
OWNER COUNT(1)
------------------------------ ----------
3199936
SYS 1473088
PUBLIC 1285376
ORDSYS 110144
SYSMAN 83904
MDSYS 58304
OLAPSYS 45952
SYSTEM 27904
XDB 22016
CTXSYS 19968
WMSYS 19520
OWNER COUNT(1)
------------------------------ ----------
EXFSYS 17920
CUSTOMER_NEW 17088
DMSYS 12096
DBSNMP 3200
TEST 704
ORDPLUGINS 640
ORACLE_OCM 512
OUTLN 512
SI_INFORMTN_SCHEMA 512
SCOTT 448
TSMSYS 128
SQL> set autotrace traceonly
SQL> set linesize 1000
--测试全表扫描性能高手B-tree索引的情况：
---全表扫描
SQL> select * from t where owner='SYS';
1473088 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1452K| 132M| 9856 (2)| 00:01:59 |
|* 1 | TABLE ACCESS FULL| T | 1452K| 132M| 9856 (2)| 00:01:59 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
140952 consistent gets
0 physical reads
568 redo size
73282966 bytes sent via SQL*Net to client
1080747 bytes received via SQL*Net from client
98207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1473088 rows processed
--同样的条件B-tree索引扫描
SQL> select /*+ index(t ind1)*/ * from t where owner='SYS';
1473088 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3619256011
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1452K| 132M| 47773 (1)| 00:09:34 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1452K| 132M| 47773 (1)| 00:09:34 |
|* 2 | INDEX RANGE SCAN | IND1 | 1452K| | 5748 (1)| 00:01:09 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
235465 consistent gets
0 physical reads
612 redo size
73283084 bytes sent via SQL*Net to client
1080747 bytes received via SQL*Net from client
98207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1473088 rows processed
结论：
全表扫描逻辑读为：140952
索引扫描逻辑读为：235465
因符合条件的记录大概是总记录数的一半，所以全表的效率高于使用索引的效率。
--测试B-tree索引高于全表扫描性能的情况：
----使用全表扫描情况
SQL> select /*+ full(t)*/ * from t where owner='TEST';
704 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 275 | 26400 | 9812 (2)| 00:01:58 |
|* 1 | TABLE ACCESS FULL| T | 275 | 26400 | 9812 (2)| 00:01:58 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='TEST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
44124 consistent gets
0 physical reads
524 redo size
50528 bytes sent via SQL*Net to client
998 bytes received via SQL*Net from client
48 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
704 rows processed
--使用索引情况
SQL> select * from t where owner='TEST';
704 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3619256011
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 275 | 26400 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 275 | 26400 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND1 | 275 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='TEST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
296 consistent gets
0 physical reads
0 redo size
50528 bytes sent via SQL*Net to client
998 bytes received via SQL*Net from client
48 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
704 rows processed
总结：
全表扫描逻辑读为：44124
索引扫描逻辑读为：296
因符合条件的记录远小于总记录数，所以索引的效率高于全表扫描的效率。
2.分别给出一个Bitmap索引针对b-tree索引性能高和低的例子。
|
--测试Bitmap索引性能高于B-tree索引的情况： ----查询数据量的分布 SQL> Select status,Count(1) From t Group By status; STATUS COUNT(1) ------- ---------- INVALID 64 VALID 3199872 ----使用Bitmap索引 SQL> Create bitmap Index bm_ind On t(status); Index created SQL> set autotrace traceonly SQL> set linesize 1000 SQL> Select /*+ index(t bm_ind)*/ * From t Where status = 'VALID'; 3199872 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4279350716 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3204K| 293M| 108K (1)| 00:21:43 | | 1 | TABLE ACCESS BY INDEX ROWID | T | 3204K| 293M| 108K (1)| 00:21:43 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | BM_IND | | | | | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("STATUS"='VALID') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 254609 consistent gets 79 physical reads 656 redo size 163402324 bytes sent via SQL*Net to client 2347056 bytes received via SQL*Net from client 213326 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3199872 rows processed SQL> set autotrace off SQL> Drop Index bm_ind; Index dropped SQL> Create Index bt_ind On t(status); Index created SQL> set autotrace traceonly SQL> Select /*+ index(t bt_ind)*/ * From t Where status = 'VALID'; 3199872 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1628163082 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3204K| 293M| 51977 (1)| 00:10:24 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 3204K| 293M| 51977 (1)| 00:10:24 | |* 2 | INDEX RANGE SCAN | BT_IND | 3199K| | 7666 (2)| 00:01:32 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("STATUS"='VALID') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 474857 consistent gets 7590 physical reads 700 redo size 163402324 bytes sent via SQL*Net to client 2347056 bytes received via SQL*Net from client 213326 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3199872 rows processed 总结： Bitmap索引逻辑读：254609 B-tree索引逻辑读：474857 由于status字段选择性比较差，只有两个值INVALID和VALID，所以建Bitmap索引性能要高于B-tree索引 --测试B-tree索引性能高于Bitmap索引的情况： ----创建表t2，建B-tree索引 SQL> Create Table t2 As Select * From dba_objects; SQL> Create Index bt_ind2 On t2(owner,object_name); SQL> Exec dbms_stats.gather_table_stats(User,'t2',cascade => True); ----创建相同表t3，建Bitmap索引 SQL> Create Table t3 As Select * From dba_objects; SQL> Create bitmap Index bm_ind2 On t3(owner,object_name); SQL> Exec dbms_stats.gather_table_stats(User,'t3',cascade => True); --查询数据量的分布 SQL> Select Count(object_name),Count(Distinct object_name) From t3; COUNT(OBJECT_NAME) COUNT(DISTINCTOBJECT_NAME) ------------------ -------------------------- 50654 30178 SQL> set autotrace traceonly SQL> Select /*+ index(t3 bm_ind2)*/* From t3 Where object_name Like 'D%'; 3019 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3749039269 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 693 | 64449 | 2113 (1)| 00:00:26 | | 1 | TABLE ACCESS BY INDEX ROWID | T3 | 693 | 64449 | 2113 (1)| 00:00:26 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX FULL SCAN | BM_IND2 | | | | | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("OBJECT_NAME" LIKE 'D%') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2068 consistent gets 0 physical reads 0 redo size 187185 bytes sent via SQL*Net to client 2703 bytes received via SQL*Net from client 203 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3019 rows processed SQL> Select /*+ index(t2 bt_ind2)*/* From t2 Where object_name Like 'D%'; 3019 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 718311200 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 686 | 63798 | 768 (1)| 00:00:10 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 686 | 63798 | 768 (1)| 00:00:10 | |* 2 | INDEX FULL SCAN | BT_IND2 | 686 | | 292 (1)| 00:00:04 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME" LIKE 'D%') filter("OBJECT_NAME" LIKE 'D%') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2013 consistent gets 0 physical reads 0 redo size 187185 bytes sent via SQL*Net to client 2703 bytes received via SQL*Net from client 203 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3019 rows processed 结论： Bitmap索引逻辑读：2068 B-tree索引逻辑读：2013 由于owner和object_name复合索引选择性比较高，B-tree索引的性能略高于Bitmap索引。 但实际的测试当中，在选择性比较高的列上创建bitmap索引和B-tree索引的性能也相差无几，甚至有时比较B-tree索引效率还要高。 在种情况下B-tree索引的性能优势并不明显，但由于Bitmap索引的特性，它最适用于在选择性比较差的列上和在OLAP环境中使用。 |
3.演示DML操作导致位图索引锁定示例。
--创建表和位图索引
SQL> Create Table t2 As Select * From dba_objects;
Table created
SQL> Create bitmap Index bm_ind2 On t2(owner);
Index created
SQL> Exec dbms_stats.gather_table_stats(User,'t2',cascade => True);
PL/SQL procedure successfully completed
--查询数据分布情况
SQL> Select owner,Count(1) From t2 Group By rollup(owner) Order By Count(1) Desc;
OWNER COUNT(1)
------------------------------ ----------
50654
SYS 23225
PUBLIC 20086
ORDSYS 1721
SYSMAN 1341
MDSYS 937
OLAPSYS 720
XDB 678
SYSTEM 465
WMSYS 315
CTXSYS 313
EXFSYS 282
CUSTOMER_NEW 267
DMSYS 189
DBSNMP 50
TEST 20
ORDPLUGINS 10
OUTLN 9
ORACLE_OCM 8
SI_INFORMTN_SCHEMA 8
实验一：
session1:
--更新数据并不提交
SQL> update t2 set owner='PUBLIC' where owner='SYS' and object_id=20;
1 row updated
session2:
SQL> update t2 set owner='PUBLIC' where owner='SYS' and object_id=21;
--waiting....
session3:
SQL> update t2 set owner='PUBLIC' where owner='TEST' ;
20 rows updated
结论：
在有Bitmap索引更新条件中，Bitmap索引会锁定所有键值相关记录的DML操作。
实验二：
session1:
--更新数据并不提交
SQL> update t2 set owner='PUBLIC' where owner in('SYS','SYSTEM');
23690 rows updated
session2:
SQL> insert into t2 select * from t where owner='SYS' and rownum=1;
--waiting....
session3:
SQL> insert into t2 select * from t where owner='SYSTEM' and rownum=1;
--waiting....
session4:
SQL> insert into t2 select * from t where owner='PUBLIC' and rownum=1;
--waiting....
结论：
在有bitmap的表中修改数据，会对所有受影响的键值关联的记录做锁定！
扫一扫
6693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
