设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 516|回复: 0

[经验分享] 收获，不止Oracle之索引篇(2)

[复制链接]
ibaz15m4ln

尚未签到
发表于 2016-8-12 06:25:24 | 显示全部楼层 |阅读模式
/*
几个索引的扫描方式：
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
　　

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.yunweiku.com/thread-256422-1-1.html 上篇帖子： oracle关于树形节点处理 下篇帖子： java操作oracle(SDE)空间信息介绍
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表