/*
几个索引的扫描方式：
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
相关推荐
收获 不止Oracle Oracle Oracle入门 Oracle学习
Oracle优质电子书： 收获不止Oracle 收获不止SQL优化
Oracle优质电子书： 收获不止Oracle 收获不止SQL优化
《收获，不止Oracle》颠覆IT技术图书的传统写作方式，在妙趣横生的故事中学到Oracle核心知识与优化方法论，让你摆脱技术束缚，超越技术。
收获，不止Oracle. (梁敬彬,梁敬弘) PDF.pdf收获，不止Oracle. (梁敬彬,梁敬弘) PDF.pdf
《收获,不止Oracle》.( 梁敬彬,梁敬弘) 电子工业出版社
《收获，不止Oracle》很不错的一本书，相信大家很多人都听过，一直没找到全集的版本，终于找到了，分享给大家。 注：由于很大，只能压缩成两个rar，需要下载两个rar才能解压，请谅解！
在这《收获,不止oracle》里读者将会跟随作者一同对oracle数据库的相关知识进行梳理，最终共同提炼出必须最先掌握的那部分知识，无论你是数据库开发、管理、优化、设计人员，还是从事java、c的开发人员。接下来作者再...
【资源仅供技术交流，如有帮助建议购买正版，侵立删】 《收获，不止Oracle》粱敬彬 印次：2013年5月第一次印刷 版本：扫描版 书签：有 清晰度：一般 扫描页数：502页
《收获，不止Oracle》很不错的一本书，相信大家很多人都听过，一直没找到全集的版本，终于找到了，分享给大家。 注：由于很大，只能压缩成两个rar，需要下载两个rar才能解压，请谅解！
收获，不止oracle！很好的一本书，推荐。
《 收获,不止Oracle》，真的让你收获不止Oracle。
收获，不止Oracle Oracle Oracle入门书籍 Oracle学习
收获 不止Oracle-高清版-2013年5月，分享给所有需要的人！
oracle学习很不错的书----收获不止oracle oracle学习很不错的书----收获不止oracle
这部分知识就是Oracle的物理体系结构、逻辑体系结构、表、索引以及表连接五大部分。通过阅读这些章节，读者将会在最短时间内以一种有史以来最轻松的方式，完成对Oracle数据库的整体认识，不仅能在工作中解决常规问题...
这部分知识就是Oracle的物理体系结构、逻辑体系结构、表、索引以及表连接五大部分。通过阅读这些章节，读者将会在最短时间内以一种有史以来最轻松的方式，完成对Oracle数据库的整体认识，不仅能在工作中解决常规问题...
收获不止Oracle,梁老师著作，循序渐进地学习方式，慢慢体会，收益匪浅。
废话不多说，要多清楚有多清楚。 超清扫描版，下载之后绝不后悔！