/*
几个索引的扫描方式:
INDEX FULLL SCAN:扫描一次只读取一个索引块
INDEX FAST FULL SCAN:一次性会读取多个索引块,读取多个数据块不容易保证有序。因此COUNT(*),SUM等不需要排序动作的操作会走INDEX FAST FULL SCAN
INDEX FULL SCAN(MIN/MAX):索引扫描最大值和最小值
*/
--UNION优化
--看出来UNION会用到排序的步骤 SORT UNIQUE
admin@ORCL> SELECT OBJECT_ID FROM T
2 UNION
3 SELECT OBJECT_ID FROM T1;
已选择50890行。
执行计划
----------------------------------------------------------
Plan hash value: 631167089
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 894K| | 471 (62)| 00:00:06 |
| 1 | SORT UNIQUE | | 101K| 894K| 3216K| 471 (62)| 00:00:06 |
| 2 | UNION-ALL | | | | | | |
| 3 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| | 27 (4)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_T1_OBJECT_ID | 50898 | 646K| | 30 (4)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
305 consistent gets
0 physical reads
0 redo size
736599 bytes sent via SQL*Net to client
37697 bytes received via SQL*Net from client
3394 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50890 rows processed
--union all不会用到排序动作,对比下cost 471,而UNION ALL 的COST为57
admin@ORCL> SELECT OBJECT_ID FROM T1
2 UNION ALL
3 SELECT OBJECT_ID FROM T;
已选择101617行。
执行计划
----------------------------------------------------------
Plan hash value: 1727178076
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 894K| 57 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | INDEX FAST FULL SCAN| IDX_T1_OBJECT_ID | 50898 | 646K| 30 (4)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| 27 (4)| 00:00:01 |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7061 consistent gets
0 physical reads
0 redo size
1460206 bytes sent via SQL*Net to client
74899 bytes received via SQL*Net from client
6776 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
101617 rows processed
--尝试使用HINT去消除UNION 尝试消除排序段.发现并没有消除掉
--这是会因为两个结果集的筛选,各自的索引当然无法奏效。
admin@ORCL> SELECT /*+ INDEX (T) */OBJECT_ID FROM T
2 UNION
3 SELECT /*+ INDEX (T1) */OBJECT_ID FROM T1;
已选择50890行。
执行计划
----------------------------------------------------------
Plan hash value: 2084608915
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 894K| | 654 (59)| 00:00:08 |
| 1 | SORT UNIQUE | | 101K| 894K| 3216K| 654 (59)| 00:00:08 |
| 2 | UNION-ALL | | | | | | |
| 3 | INDEX FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| | 115 (2)| 00:00:02 |
| 4 | INDEX FULL SCAN| IDX_T1_OBJECT_ID | 50898 | 646K| | 126 (2)| 00:00:02 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
261 consistent gets
0 physical reads
0 redo size
736599 bytes sent via SQL*Net to client
37697 bytes received via SQL*Net from client
3394 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50890 rows processed
--索引之主外键设计
CREATE TABLE T_P(ID NUMBER,NAME VARCHAR2(30));
--创建主键
ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY(ID);
CREATE TABLE T_C(ID NUMBER,FID NUMBER,NAME VARCHAR2(30));
--创建外键约束
ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY(FID) REFERENCES T_P(ID);
--INSERT 数据
INSERT INTO T_P SELECT ROWNUM,TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM,MOD(ROWNUM,1000)+1,OBJECT_NAME FROM ALL_OBJECTS;
--看下两表join的执行计划
admin@ORCL> SELECT A.ID,A.NAME,B.NAME FROM T_P A,T_C B WHERE A.ID = B.FID AND A.ID = 880;
已选择50行。
执行计划
----------------------------------------------------------
Plan hash value: 727955870
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 2340 | 72 (3)| 00:00:01 |
| 1 | NESTED LOOPS | | 39 | 2340 | 72 (3)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T_C | 39 | 1170 | 71 (3)| 00:00:01 |
------------------------------------------------------------------------------------------
--在T_C表上创建索引,再来看下执行计划
--
CREATE INDEX IDX_T_C_FID ON T_C(FID);
admin@ORCL> SELECT A.ID,A.NAME,B.NAME FROM T_P A,T_C B WHERE A.ID = B.FID AND A.ID = 880;
已选择50行。
执行计划
----------------------------------------------------------
Plan hash value: 4290308465
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 2900 | 54 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 50 | 2900 | 54 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_C | 50 | 1400 | 52 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T_C_FID | 50 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
--分析下外键约束的危害
--若没有在外键上创建索引,则在做DML操作外键所在的表时,会锁住整个主键表.
--删除索引
DROP INDEX IDX_T_C_FID;
--删除外键所在的表一条记录,会造成主键所在的表全表锁住。
admin@ORCL> DELETE T_C WHERE ID = 2;
已删除 1 行。
--执行任何DML都会锁住
admin@ORCL> DELETE T_P WHERE ID =2000;
--创建索引后,试试看.
--这样后,就不会锁住主键所在的表。
CREATE INDEX IDX_T_C_FID ON T_C(FID);
admin@ORCL> DELETE T_C WHERE ID = 2;
已删除 1 行。
admin@ORCL> DELETE T_P WHERE ID =2000;
已删除0行。
--尝试删除主键的表的记录
admin@ORCL> DELETE T_P WHERE ID =2;
DELETE T_P WHERE ID =2
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (ADMIN.FK_T_C) - 已找到子记录
--指定ON DELETE CASCADE
ALTER TABLE T_C DROP CONSTRAINT FK_T_C;
ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY(FID) REFERENCES T_P(ID) ON DELETE CASCADE;
admin@ORCL> DELETE T_P WHERE ID =2;
已删除 1 行。
--再看组合索引
/*
1.适合的场合能避免回表
2.组合列返回越少越高效(过多的字段建立组合索引往往是不可取的,这样索引也必然过大,不宜超过三个)
3.组合索引,对于性能来将,谁放在前面都一样。
4.当时范围查询与等值查询结合时,等值查询列在前,范围查询列在后,这样的组合索引才高效
5.当只是范围查询时,肯定是范围查询的列在前时,查询效率高。
6.建立组合索引时,要考虑单列查询的情况,要把经常查询的列放在组合索引的第一列
*/
--测试下第三点
DROP TABLE T PURGE;
CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX IDX1_OBJECT_ID ON T(OBJECT_ID,OBJECT_TYPE);
CREATE INDEX IDX2_OBJECT_ID ON T(OBJECT_TYPE,OBJECT_ID);
admin@ORCL> SELECT * FROM T WHERE OBJECT_ID = 20 AND OBJECT_TYPE = 'TABLE';
执行计划
----------------------------------------------------------
Plan hash value: 1913591113
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 177 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=20)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1198 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--利用hint,测试第二个索引
admin@ORCL> SELECT /*+INDEX(T,IDX1_OBJECT_ID)*/* FROM T WHERE OBJECT_ID = 20 AND OBJECT_TYPE = 'TABLE';
执行计划
----------------------------------------------------------
Plan hash value: 2486998213
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 885 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1198 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--看看组合索引对单列查询的影响
DROP INDEX IDX2_OBJECT_ID;
--会用到索引,当查询列在组合索引的前列时
admin@ORCL> SELECT * FROM T WHERE OBJECT_ID = 12;
执行计划
----------------------------------------------------------
Plan hash value: 2486998213
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 177 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
DROP INDEX IDX1_OBJECT_ID;
--创建非前缀索引看看,发现并不会走索引
CREATE INDEX IDX2_OBJECT_ID ON T(OBJECT_TYPE,OBJECT_ID);
admin@ORCL> SELECT * FROM T WHERE OBJECT_ID = 12;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 162 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8 | 1416 | 162 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=12)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
704 consistent gets
0 physical reads
0 redo size
1193 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--强制走下索引,对比下效率。发现并不如全表扫描的效率,因为全表扫描会有多块读。
admin@ORCL> SELECT /*+index(t,IDX2_OBJECT_ID)*/* FROM T WHERE OBJECT_ID = 12;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 187 (2)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 1416 | 187 (2)| 00:00:03 |
|* 2 | INDEX FULL SCAN | IDX2_OBJECT_ID | 190 | | 177 (2)| 00:00:03 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12)
filter("OBJECT_ID"=12)
Note
-----
- dynamic sampling used for this statement
统计信息
--------------------------------------
0 recursive calls
0 db block gets
177 consistent gets
0 physical reads
0 redo size
1197 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--有序插入与无序插入的执行时间
--当记录有序插入时,索引块的扩展和批量重组是可以批量做的。而无序插入是无法使用批量的。
--所以无序插入执行的速度比有序插入慢很多.
/*
索引对DML语句的影响
1.对INSERT影响最大,有百害而无一利,只要有索引,插入就慢,越多越慢。
2.对DELETE语句来说,有好有坏。海量数据中定位删除少量记录时,这个条件列时索引列时必要的。但过多列有索引还是会影响明显。
因为其他列的索引也要因此被更新。在经常需要删除大量记录的时候,危害加剧。
3.对UPDATE语句危害最小,快速定位少量并更新的场景和DELETE类似。
但具体修改某列时候,不会触发其他索引列的维护。
另外在创建索引的过程中,会产生锁,并把整个表锁住。任何该表的DML操作都将会被阻止。
这是因为建索引时,需要把索引列的列值全部取出来,加上锁是为了避免此时的列值被更新。
*/
--如何监控索引
alter index index_name monitoring usage;
--查看索引使用情况,进行跟踪
select * from v$object_usage;
--停止监控
alter index index_name nomonitoring usage;
--位图索引
--先来感受下位图索引的威力
/*
位图索引的弱点:当索引列的值在做DML时,其他包含此值的所有行都不能同时进行DML操作(其他的session)。
位图索引的适合场景:
1.位图索引大量重复 2.该表极少更新(两个SESSION以上同时更新)。
*/
drop table t purge;
CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
INSERT INTO T SELECT * FROM T;
INSERT INTO T SELECT * FROM T;
INSERT INTO T SELECT * FROM T;
INSERT INTO T SELECT * FROM T;
INSERT INTO T SELECT * FROM T;
CREATE INDEX IDX_T_OBJECT_ID ON T(OBJECT_ID);
CREATE BITMAP INDEX IDX_T_STATUS ON T(STATUS);
--count(*)会自动使用bitmap索引,虽然此字段可以为空。
admin@ORCL> SELECT COUNT(*) FROM T;
执行计划
----------------------------------------------------------
Plan hash value: 4078949922
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 1106K| 41 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| IDX_T_STATUS | | | |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--使用基于object_id字段创建的索引,对比下执行效率。cost为3716,而位图索引仅仅为41。逻辑读分别为45,3582
admin@ORCL> SELECT /*+INDEX(T,IDX_T_OBJECT_ID)*/COUNT(*) FROM T;
执行计划
----------------------------------------------------------
Plan hash value: 1172057573
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3716 (2)| 00:00:45 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_T_OBJECT_ID | 1106K| 3716 (2)| 00:00:45 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3582 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
----------------来看下位图索引对多列查询条件的调优
DROP TABLE T PURGE;
CREATE TABLE T
(
NAME_ID,
GENDER NOT NULL,
LOCATION NOT NULL,
AGE_GROUP NOT NULL,
DATA
)AS
SELECT ROWNUM,DECODE(CEIL(DBMS_RANDOM.VALUE(0,2)),
1,'M',
2,'F')GENDER,
CEIL(DBMS_RANDOM.VALUE(0,50)) LOCATION,
DECODE(CEIL(DBMS_RANDOM.VALUE(0,3)),
1,'CHILD',
2,'YOUNG',
3,'MIDDLE_AGE',
4,'OLD'),
RPAD('*',20,'*')
FROM DUAL CONNECT BY LEVEL <= 100000
admin@ORCL> SELECT COUNT(*)
2 FROM T
3 WHERE GENDER = 'M'
4 AND LOCATION IN (1, 10, 30)
5 AND AGE_GROUP = 'CHILD';
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 143 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
|* 2 | TABLE ACCESS FULL| T | 889 | 19558 | 143 (4)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR
"LOCATION"=30) AND "AGE_GROUP"='CHILD')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
608 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--创建三个普通组合索引,看下效率,cost 为81,全表扫描为143,效率还是有提高的。逻辑读分别为340,608
CREATE INDEX IDX_T_UNION ON T(GENDER,LOCATION,AGE_GROUP);
admin@ORCL> SELECT COUNT(*)
2 FROM T
3 WHERE GENDER = 'M'
4 AND LOCATION IN (1, 10, 30)
5 AND AGE_GROUP = 'CHILD';
执行计划
----------------------------------------------------------
Plan hash value: 3051164172
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 81 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T_UNION | 889 | 19558 | 81 (4)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR
"LOCATION"=30) AND "AGE_GROUP"='CHILD')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
340 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--创建三个位图索引来看看,COST只有10,逻辑读只有16
CREATE BITMAP INDEX IDX_T_GENDER ON T(GENDER);
CREATE BITMAP INDEX IDX_T_LOCATION ON T(LOCATION);
CREATE BITMAP INDEX IDX_T_AGE_GROUP ON T(AGE_GROUP);
admin@ORCL> SELECT COUNT(*)
2 FROM T
3 WHERE GENDER = 'M'
4 AND LOCATION IN (1, 10, 30)
5 AND AGE_GROUP = 'CHILD';
执行计划
----------------------------------------------------------
Plan hash value: 777186046
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 10 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
|* 2 | VIEW | index$_join$_001 | 889 | 19558 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
|* 4 | HASH JOIN | | | | | |
| 5 | INLIST ITERATOR | | | | | |
| 6 | BITMAP CONVERSION TO ROWIDS| | 889 | 19558 | 3 (0)| 00:00:01 |
|* 7 | BITMAP INDEX SINGLE VALUE | IDX_T_LOCATION | | | | |
| 8 | BITMAP CONVERSION TO ROWIDS | | 889 | 19558 | 4 (0)| 00:00:01 |
|* 9 | BITMAP INDEX SINGLE VALUE | IDX_T_AGE_GROUP | | | | |
| 10 | BITMAP CONVERSION TO ROWIDS | | 889 | 19558 | 5 (0)| 00:00:01 |
|* 11 | BITMAP INDEX SINGLE VALUE | IDX_T_GENDER | | | | |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR "LOCATION"=30) AND
"AGE_GROUP"='CHILD')
3 - access(ROWID=ROWID)
4 - access(ROWID=ROWID)
7 - access("LOCATION"=1 OR "LOCATION"=10 OR "LOCATION"=30)
9 - access("AGE_GROUP"='CHILD')
11 - access("GENDER"='M')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--将字段设置为允许为空
ALTER TABLE T MODIFY LOCATION NULL;
--从下面的执行计划可以看出,位图索引是保留空值的。(经过测试,当表中有NULL值,位图索引就会保存NULL值)
admin@ORCL> SELECT * FROM T WHERE LOCATION IS NULL;
执行计划
----------------------------------------------------------
Plan hash value: 2307794171
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 38 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_T_LOCATION | | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LOCATION" IS NULL)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
500 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--DUMP索引
--参考网站:http://www.itpub.net/thread-114023-1-1.html
--函数索引分析
DROP TABLE T PURGE;
CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
--看看全表扫描的效率
admin@ORCL> SELECT * FROM T WHERE OBJECT_NAME = 'T';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 162 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8 | 1416 | 162 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
705 consistent gets
0 physical reads
0 redo size
1283 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--建立普通索引,COST 仅仅为2, 5 consistent gets
CREATE INDEX IDX_T_OBJ_NAME ON T(OBJECT_NAME);
admin@ORCL> SELECT * FROM T WHERE OBJECT_NAME = 'T';
执行计划
----------------------------------------------------------
Plan hash value: 3992992723
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 354 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 354 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_NAME | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1310 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--创建下函数索引看看,此处的 逻辑读与普通索引一样,但cost却为103,比上面的cost 2大了很多
--此也是有点费解
drop index BX_T_OBJ_NAME;
CREATE INDEX IDX_T_FUNC ON T(UPPER(OBJECT_NAME));
admin@ORCL> SELECT * FROM T WHERE UPPER(OBJECT_NAME) = 'T';
执行计划
----------------------------------------------------------
Plan hash value: 1758637790
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 103 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 1416 | 103 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_T_FUNC | 228 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("OBJECT_NAME")='T')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1283 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--其实上面的sql可以改写下,使用普通索引
--此处只是测试,T表中的OBJECT_NAME全为大写
drop index IDX_T_FUNC;
CREATE INDEX IDX_T_OBJ_NAME ON T(OBJECT_NAME);
admin@ORCL> SELECT * FROM T WHERE OBJECT_NAME IN ('T')
2 UNION ALL
3 SELECT * FROM T WHERE OBJECT_NAME IN ('t');
执行计划
----------------------------------------------------------
Plan hash value: 3104074320
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 531 | 3 (34)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 354 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJ_NAME | 2 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 177 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T_OBJ_NAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_NAME"='T')
5 - access("OBJECT_NAME"='t')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1283 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
--若是使用IN反而效率不太好,cost 为104
SELECT * FROM T WHERE OBJECT_NAME IN ('T','t');
admin@ORCL> SELECT * FROM T WHERE OBJECT_NAME IN ('T','t');
执行计划
----------------------------------------------------------
Plan hash value: 1193873658
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 104 (0)| 00:00:02 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 1416 | 104 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJ_NAME | 228 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_NAME"='T' OR "OBJECT_NAME"='t')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1310 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed