1.分别给出一个B-tree索引针对全表扫描性能高和低的例子。
B-tree比FTS性能高的例子:
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> set autotrace traceonly;
SQL> select /*+ index(t) */ * from t where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ full(t) */ * from t where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1578 (2)| 00:00:19 |
|* 1 | TABLE ACCESS FULL| T | 1 | 207 | 1578 (2)| 00:00:19 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
5795 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
B-tree比FTS性能低的例子:
这里继续使用上一步建立的表和索引.
SQL> select /*+ index(t) */ * from t where object_id > 2;400733 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 367K| 72M| 7508 (1)| 00:01:31 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 367K| 72M| 7508 (1)| 00:01:31 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 367K| | 954 (2)| 00:00:12 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
60479 consistent gets
6510 physical reads
0 redo size
23326350 bytes sent via SQL*Net to client
294215 bytes received via SQL*Net from client
26717 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
400733 rows processed
SQL> select /*+ full(t) */ * from t where object_id > 2;
400733 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 367K| 72M| 1588 (2)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| T | 367K| 72M| 1588 (2)| 00:00:20 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
32122 consistent gets
1 physical reads
0 redo size
23356412 bytes sent via SQL*Net to client
294215 bytes received via SQL*Net from client
26717 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
400733 rows processed
SQL>
--EOF--
Bitmap比B-tree性能高的例子:
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create bitmap index idx_t_objtyp_bm on t(object_type);
Index created.
SQL> set autotrace traceonly;
SQL> select * from t where object_type = 'TABLE';
36699 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1234337849
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33879 | 6848K| 629 (1)| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 33879 | 6848K| 629 (1)| 00:00:08 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_T_OBJTYP_BM | | | | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
312 recursive calls
0 db block gets
4625 consistent gets
56 physical reads
0 redo size
2188987 bytes sent via SQL*Net to client
27256 bytes received via SQL*Net from client
2448 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36699 rows processed
SQL> drop index idx_t_objtyp_bm;
Index dropped.
SQL> create index idx_t_objtyp_bt on t(object_type);
Index created.
SQL> select * from t where object_type = 'TABLE';
36699 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3915164313
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33879 | 6848K| 1568 (1)| 00:00:19 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 33879 | 6848K| 1568 (1)| 00:00:19 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJTYP_BT | 33879 | | 106 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
7115 consistent gets
89 physical reads
0 redo size
2188987 bytes sent via SQL*Net to client
27256 bytes received via SQL*Net from client
2448 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36699 rows processed
SQL>
Bitmap比B-tree性能低的例子:
这里继续使用上步使用的表.
SQL> create bitmap index idx_t_objid_bm on t(object_id);
Index created.
SQL> select * from t where object_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2899789933
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 629 (1)| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 207 | 629 (1)| 00:00:08 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_T_OBJID_BM | | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
110 consistent gets
2 physical reads
0 redo size
1195 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index idx_t_objid_bm;
Index dropped.
SQL> create index idx_t_objid_bt on t(object_id);
Index created.
SQL> select * from t where object_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2716432708
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID_BT | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
110 consistent gets
2 physical reads
0 redo size
1195 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--EOF--
3.演示DML操作导致位图索引锁定示例。
session1 建表和索引,并在建完索引后插入一条记录, 保持未提交状态:
SQL> drop table t purge;
Table dropped.
SQL> select * from v_mystat;
SID PID SPID
---------- ---------- ------------------------
286 95 17404
SQL> create table t(id int,sex char);
Table created.
SQL> insert into t values(1,'F');
1 row created.
SQL> insert into t values(2,'M');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID S
---------- -
1 F
2 M
SQL> create bitmap index idx_t_sex_bm on t(sex);
Index created.
SQL> insert into t values (3,'F');
1 row created.
SQL>
session2 插入一条和session具有同相同索引键值的记录, 发现被卡住:
SQL> select * from v_mystat;
SID PID SPID
---------- ---------- ------------------------
353 110 28648
SQL> insert into t values(4,'F');
--被卡住
session3查询锁信息:
SQL> select * from v$Lock where sid in (286,353);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009E875390 000000009E8753E8 286 AE 3269431 0 4 0 5911 0
000000009E879508 000000009E879560 286 TO 3290249 1 3 0 5300 0
000000009E87A8A0 000000009E87A8F8 353 AE 3269431 0 4 0 545 0
000000009E87B770 000000009E87B7C8 353 TX 458773 2479462 0 4 70 0
00007FA0E46A59A0 00007FA0E46A5A00 353 TM 3327771 0 3 0 70 0
00007FA0E46A59A0 00007FA0E46A5A00 286 TM 3327771 0 3 0 149 0
000000009BB37508 000000009BB37580 353 TX 655370 2970642 6 0 70 0
000000009BB5AD88 000000009BB5AE00 286 TX 458773 2479462 6 0 149 1
8 rows selected.
SQL>
--EOF--
4.创建一个全文索引，比较它和传统的模糊查询的性能。
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index idx_t_objname on t(object_name) indextype is ctxsys.context;
Index created.
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select /*+ index(t) */ * from t where contains(object_name,'%PART%')>0;
3119 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2504792988
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 184 | 40296 | 44 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 184 | 40296 | 44 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | IDX_T_OBJNAME | | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'%PART%')>0)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
753 recursive calls
0 db block gets
4694 consistent gets
1015 physical reads
0 redo size
208958 bytes sent via SQL*Net to client
2627 bytes received via SQL*Net from client
209 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3119 rows processed
SQL> drop index idx_t_objname;
Index dropped.
SQL> create index idx_t_objname on t(object_name);
Index created.
SQL> select /*+ index(t) */ * from t where object_name like '%PART%';
3008 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1725320753
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5662 | 1144K| 15619 (1)| 00:03:08 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5662 | 1144K| 15619 (1)| 00:03:08 |
|* 2 | INDEX FULL SCAN | IDX_T_OBJNAME | 18378 | | 1839 (1)| 00:00:23 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" LIKE '%PART%')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
4429 consistent gets
1821 physical reads
0 redo size
228590 bytes sent via SQL*Net to client
2550 bytes received via SQL*Net from client
202 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3008 rows processed
SQL>
从本例中可以看出,在模糊查询下,全文索引比传统索引性能要好.
--EOF--
5.分别演示分区索引的性能优化全局索引和差于全局索引的示例，并分析原因。
分区索引性能优于全局索引示例:
SQL> drop table part purge;
Table dropped.
SQL> create table part (object_id int, object_name varchar2(50))
2 partition by range(object_id) (
3 partition p1 values less than (1000),
4 partition p2 values less than (2000),
5 partition p3 values less than (3000),
6 partition p4 values less than (4000),
7 partition p5 values less than (5000),
8 partition p6 values less than (6000),
9 partition p7 values less than (7000),
10 partition p8 values less than (8000),
11 partition p9 values less than (9000),
12 partition pm values less than ( maxvalue) );
Table created.
SQL> insert into part select object_id, object_name from dba_objects;
400842 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_part_objid on part(object_id) local; --分区索引
Index created.
SQL> set autotrace traceonly
SQL> select /*+ index(part)*/ * from part where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2569589552
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 40 | 3 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART | 1 | 40 | 3 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | IDX_PART_OBJID | 1 | | 2 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
232 recursive calls
0 db block gets
65 consistent gets
1 physical reads
0 redo size
404 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index idx_part_objid;
Index dropped.
SQL> create index idx_part_objid on part(object_id); -- 全局索引
Index created.
SQL> select /*+ index(part)*/ * from part where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1288380872
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 7 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PART | 1 | 40 | 7 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_PART_OBJID | 1 | | 4 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
29 consistent gets
2 physical reads
0 redo size
404 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
分区索引性能差于全局索引示例:
这里继续沿用上一步所用的分区表,仅重建索引.
SQL> drop index idx_part_objid;
Index dropped.
SQL> create index idx_part_objid on part(object_id) local; --分区索引
Index created.
SQL> select /*+ index(part)*/ * from part where object_id > 100 and object_id < 10000;
8610 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2605378701
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 97K| 21 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 2500 | 97K| 21 (0)| 00:00:01 | 1 | 10 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART | 2500 | 97K| 21 (0)| 00:00:01 | 1 | 10 |
|* 3 | INDEX RANGE SCAN | IDX_PART_OBJID | 2500 | | 8 (0)| 00:00:01 | 1 | 10 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">100 AND "OBJECT_ID"<10000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
1350 consistent gets
22 physical reads
0 redo size
289798 bytes sent via SQL*Net to client
6653 bytes received via SQL*Net from client
575 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8610 rows processed
SQL> create index idx_part_objid on part(object_id); -- 全局索引
Index created.
SQL> select /*+ index(part)*/ * from part where object_id > 100 and object_id < 10000;
8610 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3605578706
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 97K| 22 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PART | 2500 | 97K| 22 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_PART_OBJID | 2500 | | 9 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">100 AND "OBJECT_ID"<10000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
1330 consistent gets
0 physical reads
0 redo size
289798 bytes sent via SQL*Net to client
6653 bytes received via SQL*Net from client
575 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8610 rows processed
SQL>
* 分区索引和全局索引cost仅差1，但是分区索引的逻辑读要略大于全局索引。
--EOF--
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
