[bbk3152] 第61集 - Chapter 15-Application Tuning(01)
Compressed Indexes
CREATE INDEX t_idx on t(owner,object_type,object_name);
SQL> ANALYZE INDEX t_idx validate structure; Index analyzed.
SQL> create table idx_stats 2 as 3 select 'noncompressed' what,a.* from index_stats a; Table created.
lab1->data
SQL> drop index t_idx; Index dropped. SQL> CREATE INDEX t_idx on t(owner,object_type,object_name) compress 1; Index created. SQL> ANALYZE INDEX t_idx validate structure; Index analyzed. SQL> insert into idx_stats select 'compress 1',a.* from index_stats a; 1 row created. Execution Plan ---------------------------------------------------------- Plan hash value: 1590009556 -------------------------------------------------------------------------------- -------------------- | Id | Operation | Name | Rows | Bytes | Cos t (%CPU)| Time | -------------------------------------------------------------------------------- -------------------- | 0 | INSERT STATEMENT | | 3 | 999 | 2 87 (1)| 00:00:04 | | 1 | LOAD TABLE CONVENTIONAL | IDX_STATS | | | | | | 2 | VIEW | INDEX_STATS | 3 | 999 | 2 87 (1)| 00:00:04 | | 3 | UNION-ALL | | | | | | | 4 | NESTED LOOPS | | 1 | 344 | 2 14 (0)| 00:00:03 | | 5 | NESTED LOOPS | | 7 | 2331 | 2 07 (0)| 00:00:03 | | 6 | NESTED LOOPS | | 100 | 31800 | 2 00 (0)| 00:00:03 | | 7 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 2 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 15 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 0 (0)| 00:00:01 | | 12 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | | 14 | NESTED LOOPS | | | | | | | 15 | NESTED LOOPS | | 1 | 345 | 7 0 (2)| 00:00:01 | |* 16 | HASH JOIN | | 7 | 2191 | 5 6 (2)| 00:00:01 | | 17 | TABLE ACCESS FULL | INDPART$ | 360 | 5760 | 5 (0)| 00:00:01 | |* 18 | HASH JOIN | | 104 | 30888 | 5 1 (2)| 00:00:01 | | 19 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 20 | TABLE ACCESS FULL | SEG$ | 5966 | 65626 | 5 0 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 22 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 32 | 2 (0)| 00:00:01 | | 23 | NESTED LOOPS | | | | | | | 24 | NESTED LOOPS | | 1 | 381 | 3 (0)| 00:00:01 | | 25 | NESTED LOOPS | | 1 | 349 | 2 (0)| 00:00:01 | | 26 | MERGE JOIN CARTESIAN | | 1 | 338 | 2 (0)| 00:00:01 | | 27 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 52 | 2 (0)| 00:00:01 | | 28 | BUFFER SORT | | 100 | 28600 | 0 (0)| 00:00:01 | | 29 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 30 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 0 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 33 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 32 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- -------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - access("KDXSTOBJ"="O"."OBJ#") 11 - access("I"."OBJ#"="O"."OBJ#") 13 - access("S"."TS#"="I"."TS#" AND "S"."FILE#"="I"."FILE#" AND "S"."BLOCK#"=" I"."BLOCK#") filter("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"= "S"."TS#") 16 - access("S"."FILE#"="IP"."FILE#" AND "S"."BLOCK#"="IP"."BLOCK#" AND "S"."TS#"="IP"."TS#") 18 - access("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"= "S"."TS#") 21 - access("IP"."OBJ#"="O"."OBJ#") filter("KDXSTOBJ"="O"."OBJ#") 31 - access("S"."TS#"="ISP"."TS#" AND "S"."FILE#"="ISP"."FILE#" AND "S"."BLOCK#"="ISP"."BLOCK#") filter("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"= "S"."TS#") 32 - access("ISP"."OBJ#"="O"."OBJ#") filter("KDXSTOBJ"="O"."OBJ#") Statistics ---------------------------------------------------------- 829 recursive calls 31 db block gets 401 consistent gets 0 physical reads 0 redo size 840 bytes sent via SQL*Net to client 821 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed
lab2->data
SQL> cldrop index t_idx; Index dropped. SQL> CREATE INDEX t_idx on t(owner,object_type,object_name) compress 2; Index created. SQL> ANALYZE INDEX t_idx validate structure; Index analyzed. SQL> insert into idx_stats select 'compress 2',a.* from index_stats a; 1 row created. Execution Plan ---------------------------------------------------------- Plan hash value: 1590009556 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 3 | 999 | 287 (1)| 00:00:04 | | 1 | LOAD TABLE CONVENTIONAL | IDX_STATS | | | | | | 2 | VIEW | INDEX_STATS | 3 | 999 | 287 (1)| 00:00:04 | | 3 | UNION-ALL | | | | | | | 4 | NESTED LOOPS | | 1 | 344 | 214 (0)| 00:00:03 | | 5 | NESTED LOOPS | | 7 | 2331 | 207 (0)| 00:00:03 | | 6 | NESTED LOOPS | | 100 | 31800 | 200 (0)| 00:00:03 | | 7 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 2 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 15 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 0 (0)| 00:00:01 | | 12 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | | 14 | NESTED LOOPS | | | | | | | 15 | NESTED LOOPS | | 1 | 345 | 70 (2)| 00:00:01 | |* 16 | HASH JOIN | | 7 | 2191 | 56 (2)| 00:00:01 | | 17 | TABLE ACCESS FULL | INDPART$ | 360 | 5760 | 5 (0)| 00:00:01 | |* 18 | HASH JOIN | | 104 | 30888 | 51 (2)| 00:00:01 | | 19 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 20 | TABLE ACCESS FULL | SEG$ | 5966 | 65626 | 50 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 22 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 32 | 2 (0)| 00:00:01 | | 23 | NESTED LOOPS | | | | | | | 24 | NESTED LOOPS | | 1 | 381 | 3 (0)| 00:00:01 | | 25 | NESTED LOOPS | | 1 | 349 | 2 (0)| 00:00:01 | | 26 | MERGE JOIN CARTESIAN | | 1 | 338 | 2 (0)| 00:00:01 | | 27 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 52 | 2 (0)| 00:00:01 | | 28 | BUFFER SORT | | 100 | 28600 | 0 (0)| 00:00:01 | | 29 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 30 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 0 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 33 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 32 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - access("KDXSTOBJ"="O"."OBJ#") 11 - access("I"."OBJ#"="O"."OBJ#") 13 - access("S"."TS#"="I"."TS#" AND "S"."FILE#"="I"."FILE#" AND "S"."BLOCK#"="I"."BLOCK#") filter("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"="S"."TS#") 16 - access("S"."FILE#"="IP"."FILE#" AND "S"."BLOCK#"="IP"."BLOCK#" AND "S"."TS#"="IP"."TS#") 18 - access("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"="S"."TS#") 21 - access("IP"."OBJ#"="O"."OBJ#") filter("KDXSTOBJ"="O"."OBJ#") 31 - access("S"."TS#"="ISP"."TS#" AND "S"."FILE#"="ISP"."FILE#" AND "S"."BLOCK#"="ISP"."BLOCK#") filter("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"="S"."TS#") 32 - access("ISP"."OBJ#"="O"."OBJ#") filter("KDXSTOBJ"="O"."OBJ#") Statistics ---------------------------------------------------------- 8 recursive calls 5 db block gets 211 consistent gets 0 physical reads 0 redo size 840 bytes sent via SQL*Net to client 821 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
lab3->data
SQL> clCREATE INDEX t_idx on t(owner,object_type,object_name) compress 3; Index created. SQL> ANALYZE INDEX t_idx validate structure; Index analyzed. SQL> insert into idx_stats select 'compress 3',a.* from index_stats a; 1 row created. Execution Plan ---------------------------------------------------------- Plan hash value: 1590009556 -------------------------------------------------------------------------------- -------------------- | Id | Operation | Name | Rows | Bytes | Cos t (%CPU)| Time | -------------------------------------------------------------------------------- -------------------- | 0 | INSERT STATEMENT | | 3 | 999 | 2 87 (1)| 00:00:04 | | 1 | LOAD TABLE CONVENTIONAL | IDX_STATS | | | | | | 2 | VIEW | INDEX_STATS | 3 | 999 | 2 87 (1)| 00:00:04 | | 3 | UNION-ALL | | | | | | | 4 | NESTED LOOPS | | 1 | 344 | 2 14 (0)| 00:00:03 | | 5 | NESTED LOOPS | | 7 | 2331 | 2 07 (0)| 00:00:03 | | 6 | NESTED LOOPS | | 100 | 31800 | 2 00 (0)| 00:00:03 | | 7 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 32 | 2 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 15 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 0 (0)| 00:00:01 | | 12 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | | 14 | NESTED LOOPS | | | | | | | 15 | NESTED LOOPS | | 1 | 345 | 7 0 (2)| 00:00:01 | |* 16 | HASH JOIN | | 7 | 2191 | 5 6 (2)| 00:00:01 | | 17 | TABLE ACCESS FULL | INDPART$ | 360 | 5760 | 5 (0)| 00:00:01 | |* 18 | HASH JOIN | | 104 | 30888 | 5 1 (2)| 00:00:01 | | 19 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 20 | TABLE ACCESS FULL | SEG$ | 5966 | 65626 | 5 0 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 22 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 32 | 2 (0)| 00:00:01 | | 23 | NESTED LOOPS | | | | | | | 24 | NESTED LOOPS | | 1 | 381 | 3 (0)| 00:00:01 | | 25 | NESTED LOOPS | | 1 | 349 | 2 (0)| 00:00:01 | | 26 | MERGE JOIN CARTESIAN | | 1 | 338 | 2 (0)| 00:00:01 | | 27 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 52 | 2 (0)| 00:00:01 | | 28 | BUFFER SORT | | 100 | 28600 | 0 (0)| 00:00:01 | | 29 | FIXED TABLE FULL | X$KDXST | 100 | 28600 | 0 (0)| 00:00:01 | | 30 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 0 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | 33 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 32 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- -------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - access("KDXSTOBJ"="O"."OBJ#") 11 - access("I"."OBJ#"="O"."OBJ#") 13 - access("S"."TS#"="I"."TS#" AND "S"."FILE#"="I"."FILE#" AND "S"."BLOCK#"=" I"."BLOCK#") filter("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"= "S"."TS#") 16 - access("S"."FILE#"="IP"."FILE#" AND "S"."BLOCK#"="IP"."BLOCK#" AND "S"."TS#"="IP"."TS#") 18 - access("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"= "S"."TS#") 21 - access("IP"."OBJ#"="O"."OBJ#") filter("KDXSTOBJ"="O"."OBJ#") 31 - access("S"."TS#"="ISP"."TS#" AND "S"."FILE#"="ISP"."FILE#" AND "S"."BLOCK#"="ISP"."BLOCK#") filter("KDXSTFIL"="S"."FILE#" AND "KDXSTBLK"="S"."BLOCK#" AND "KDXSTTSN"= "S"."TS#") 32 - access("ISP"."OBJ#"="O"."OBJ#") filter("KDXSTOBJ"="O"."OBJ#") Statistics ---------------------------------------------------------- 0 recursive calls 5 db block gets 213 consistent gets 0 physical reads 0 redo size 842 bytes sent via SQL*Net to client 821 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
lab result
SQL> select what,height,lf_blks,btree_space,opt_cmpr_count,opt_cmpr_pctsave from idx_stats; WHAT HEIGHT LF_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ------------- ---------- ---------- ----------- -------------- ---------------- noncompressed 3 402 3238476 2 28 compress 1 3 358 2885220 2 19 compress 2 3 287 2317788 2 0 compress 3 3 449 3612492 2 35 Execution Plan ---------------------------------------------------------- Plan hash value: 3468018568 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 320 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| IDX_STATS | 4 | 320 | 19 (0)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 45 consistent gets 0 physical reads 0 redo size 1063 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed SQL> select what,height,lf_blks,br_blks,btree_space,opt_cmpr_count,opt_cmpr_pctsave from idx_stats; WHAT HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ------------- ---------- ---------- ---------- ----------- -------------- ---------------- noncompressed 3 402 3 3238476 2 28 compress 1 3 358 3 2885220 2 19 compress 2 3 287 3 2317788 2 0 compress 3 3 449 3 3612492 2 35 Execution Plan ---------------------------------------------------------- Plan hash value: 3468018568 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 372 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| IDX_STATS | 4 | 372 | 19 (0)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 45 consistent gets 0 physical reads 0 redo size 1138 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed
Creating Reverse Key Index
- Create a reverse key unqiue index:
CREATE UNIQUE INDEX i1_t1 ON t1(c1) REVERSE PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;
- Create a unqiue index,then alter the index to be reverse key:
CREATE UNIQUE INDEX i2_t2 ON t1(c2); ALTER INDEX i2_t1 REBUILD REVERSE;
Bitmap Indexes
bitmap indexes适合数据仓库系统，不适合联机事务处理系统.
- Used for low-cardinality columns(类似于all_objects表中的owner,object_type字段，就属于low-cardinality columns)
- Good for multiple predicates.
- Uses minimal storage space.
- Best for read-only systems.
- Good for very large tables.
Creating and Maintaining
CREATE BITMAP INDEX departments_idx ON hr.departments(manager_id) STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;
B-Tree vs Bitmap Indexes
| B-Tree Indexes | Bitmap Indexes |
| Suitable for high-cardinality columns | Suitable for low-cardinality columns |
| Updates on keys relatively inexpensive | Updates to key columns very expensive |
| Inefficient for queries using AND/OR predicates | Efficient for queries using AND/OR predicates |
| Row-level locking | Bitmap segment-level locking |
| More storage | Less storage |
| Useful for OLTP | Useful for DSS,OLAP |
ALL_OBJECTS表中的owner,object_type字段适合Bitmap index；object_name索引适合B-Tree index
如果要是对bitmap index进行更新的话，就会将整个segment进行锁住;所以不适合更新.而b-tree index只是对行进行锁定.
Index Management
