/*
索引
索引由三部分组成，根块(root),Branch(茎块)，Leaf(叶子块)。其中Leaf块主要存储了key column value(索引列具体值)，以及能具体
定位到数据块位置的rowid。
索引的创建过程：
1.要建索引先排序，将索引列的值顺序取出，及该行的rowid放入到内存中。
2.依次将内存中的顺序存放的值和rowid存放入索引块中。
3.当填满两个索引块后，oracle会产生一个块，用于管理同级的叶子块。这个块记录了叶子块的信息，并不记录索引列的键值，所以使用的空间比较少。
4.当管理叶子块的块被填满后，oracle又会产生一个上一级管理块，依次循环。同级两块需要管理。
索引结构的三大重要特点
1.索引的高度比较低
2.索引存储列值
3.索引本身是有序的*/
----------------------------------------------索引高度较低验证-----------------------------------------
--索引的大小和高度是巨大差别的，可能大小差好多倍，但高度却一样。
--构造T1-T7表，记录从5到500W
CREATE TABLE T1 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5;
CREATE TABLE T2 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=50;
CREATE TABLE T3 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=500;
CREATE TABLE T4 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5000;
CREATE TABLE T5 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=50000;
CREATE TABLE T6 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=500000;
CREATE TABLE T7 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5000000;
--创建索引
CREATE INDEX IDX_ID_T1 ON T1(ID);
CREATE INDEX IDX_ID_T2 ON T2(ID);
CREATE INDEX IDX_ID_T3 ON T3(ID);
CREATE INDEX IDX_ID_T4 ON T4(ID);
CREATE INDEX IDX_ID_T5 ON T5(ID);
CREATE INDEX IDX_ID_T6 ON T6(ID);
CREATE INDEX IDX_ID_T7 ON T7(ID);
--查看索引大小
SELECT SEGMENT_NAME,BYTES/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN
(
'IDX_ID_T1', 'IDX_ID_T2', 'IDX_ID_T3', 'IDX_ID_T4', 'IDX_ID_T5', 'IDX_ID_T6', 'IDX_ID_T7'
);
--查看索引高度
SELECT INDEX_NAME,
BLEVEL,--索引高度,BLEVEL=0表示1层,BLEVEL=1表示2层
LEAF_BLOCKS,--Number of leaf blocks in the index
NUM_ROWS,
DISTINCT_KEYS,
CLUSTERING_FACTOR
FROM USER_IND_STATISTICS
WHERE TABLE_NAME IN ('T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'T7');
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
----------------------- ----------- ---------- ------------- -----------------
IDX_ID_T1 0 1 5 5 1
IDX_ID_T2 0 1 50 50 1
IDX_ID_T3 1 2 500 500 1
IDX_ID_T4 1 11 5000 5000 9
IDX_ID_T5 1 110 50000 50000 101
IDX_ID_T6 2 1113 500000 500000 1035
IDX_ID_T7 2 12023 5134040 4994100 22527
--相比T6表与T7表的查询效率，使用索引的查询效率一致，因为索引的高度低
--以下内容已多次执行消除 物理读和递归
admin@ORCL> select * from T6 where id = 10;
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1902844584
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:
| 1 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 9 | 4 (0)| 00:00:
|* 2 | INDEX RANGE SCAN | IDX_ID_T6 | 1 | | 3 (0)| 00:00:
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
462 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 * from T7 where id = 10;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1124755243
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T7 | 1 | 11 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID_T7 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
462 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
--DUMP索引结构，一探究竟
admin@ORCL> column object_name for a20
admin@ORCL> SELECT OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_NAME IN ('IDX_ID_T1');
OBJECT_ID OBJECT_NAME
---------- --------------------
65143 IDX_ID_T1
--DUMP后存放的trace文件后缀
SELECT SPID
FROM V$PROCESS
WHERE ADDR = (SELECT PADDR
FROM V$SESSION
WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));
SPID
------------
6704
--DUMP索引"结构"
alter system set events 'immediate trace name treedump level 65143';
--存放在以22408结尾的trace文件中
SELECT SPID
FROM V$PROCESS
WHERE ADDR = (SELECT PADDR
FROM V$SESSION
WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));
--索引结构如下：
--只有一个叶子节点 0x代表16进制数，16798212是前面16进制转化为10进制的数
--以下内容记录了leaf对应的文件号和块号
----- begin tree dump
leaf: 0x1005204 16798212 (0: nrow: 5 rrow: 5)
----- end tree dump
--将16进制为10进制的数
admin@ORCL> select to_number('1005204','xxxxxxx') from dual;
TO_NUMBER('1005204','XXXXXXX')
------------------------------
16798212
--获取数据块的文件编号,及块编号
admin@ORCL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16798212) FROM DUAL;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16798212)
----------------------------------------------
4
admin@ORCL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16798212) FROM DUAL;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16798212)
-----------------------------------------------
20996
--从以下结果可以看出leaf块确实在分配的空间中
admin@ORCL> SELECT SEGMENT_NAME,BLOCK_ID,BLOCKS,RELATIVE_FNO FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'IDX_ID_T1';
SEGMENT_NAME BLOCK_ID BLOCKS RELATIVE_FNO
-------------------- ---------- ---------- ------------
IDX_ID_T1 20993 8 4
--DUMP 数据块
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 20996;
--DUMP内容如下
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02 --十六进制
col 1; len 6; (6): 01 00 03 cc 00 00
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 03 cc 00 01
row#2[8000] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 00 03 cc 00 02
row#3[7988] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 00 03 cc 00 03
row#4[7976] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 00 03 cc 00 04
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 20996 maxblk 20996
--查看1转化为16进制的值为 c1,2
admin@ORCL> select dump(1,16) from dual;
DUMP(1,16)
-----------------
Typ=2 Len=2: c1,2
--4位二进制数表示16进制数 a在16进制中表示 10,b 代表 11,c为12 d为13
01 00 03 cc 00 00
0000 0001
0000 0000
0000 0011
1100 1100
0000 0000
0000 0000
--上面转化为二进制后，前十位为文件编号，后22个二进制数据为块编号，后面的数字为第几行
--从结果可以看出，索引内容中包含了索引列的值，及该条记录的rowid 后3部分内容。表段的OBJECT_ID是没有必要的
0000 0001 00: 4
00 0000 0000 0011 1100 1100 : 972
0000 0000 0000 0000: 0
SELECT ROWID, T1.* FROM T1;
admin@ORCL> SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,
2 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID,
3 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_ID,
4 DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) NUM
5 FROM T1;
OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
65136 4 972 0
65136 4 972 1
65136 4 972 2
65136 4 972 3
65136 4 972 4
--分区索引与全局索引的总结
--若建立的非前缀的索引，且无法有效的应用到分区条件，则最好建为全局索引。
--若考虑业务的查询只会集中在一个或少数几个分区，则最好建成分区索引
----------------------------------------------巧用索引存储列植-----------------------------------------
/*
总结：
sum/avg在是否为空都不会影响结果值，但Oracle必须列为非空才可以用到索引
max/min在列在非空或空，均可以用到索引
count(*)必须索引列不为空才可以，因为count(*)会统计空值.
count(column)时，不论column是否为空，都会用到此列的索引列。因为count(column)不统计空。
*/
--count
--什么时候索引扫描比全表扫描高效很多？
--当在大表上一个非空而且长度很短的列创建索引，这时索引的体积相对表来说特别小，那效率就高很多了。
DROP TABLE T PURGE;
CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
--没有应用到索引，因为object_id 可能为空
admin@ORCL> select count(*) from t;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 57075 | 161 (2)| 00:00:02 |
-------------------------------------------------------------------
--限制后为非空后，Oracle会使用到索引，因为索引不存储空值
admin@ORCL> SELECT COUNT(*) FROM T WHERE OBJECT_ID IS NOT NULL;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |
---------------------------------------------------------------------------------------
--若是只统计object_id，Oracle会走索引，这点有点小奇怪，但经过测试，Oracle count单列的时候是不统计空值的，但统计count(*)时，
--哪怕整行都是空值，Oracle仍会记录此条记录。
admin@ORCL> SELECT COUNT(OBJECT_ID) FROM T;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |
---------------------------------------------------------------------------------------
--修改字段OBJECT_ID 为非空，Oracle则会自动应用OBJECT_ID上面的索引
ALTER TABLE T MODIFY OBJECT_ID NOT NULL;
admin@ORCL> SELECT COUNT(*) FROM T;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 57075 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------------------
--SUM/AVG优化
DROP TABLE T PURGE;
CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX IDX_T_OBJECT_ID ON T(OBJECT_ID);
--SUM与AVG均没有用到索引，这个蛮奇怪，因为SUM与AVG的计算中均没有NULL记录的运算
admin@ORCL> SELECT SUM(OBJECT_ID) FROM T;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 57075 | 724K| 162 (2)| 00:00:02 |
---------------------------------------------------------------------------
admin@ORCL> SELECT AVG(OBJECT_ID) FROM T;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 57075 | 724K| 162 (2)| 00:00:02 |
---------------------------------------------------------------------------
--将OBJECT_ID设置为非空后，就可以用到索引。这点也蛮奇怪，可以sum是不包含空值的，但Oracle却不走索引。这点记住就好了
ALTER TABLE T MODIFY OBJECT_ID NOT NULL;
admin@ORCL> SELECT SUM(OBJECT_ID) FROM T;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
admin@ORCL> SELECT AVG(OBJECT_ID) FROM T;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
0 recursive calls
0 db block gets
119 consistent gets
0 physical reads
0 redo size
433 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
--执行计划可以看出，count,sum,avg连续三个聚合语句写在一块使用的资源与单个avg是一致的。这是因为一次扫描索引块，就可以同时解决三个问题。
admin@ORCL> SELECT SUM(OBJECT_ID),AVG(OBJECT_ID),COUNT(*) FROM T;
执行计划
----------------------------------------------------------
Plan hash value: 1572773910
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
0 recursive calls
0 db block gets
119 consistent gets
0 physical reads
0 redo size
567 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
--测试MAX/MIN函数的优化,这时哪怕字段可以为NULL,仍可以用到索引
--INDEX FULL SCAN (MIN/MAX)：无论多大的索引，此操作都会非常快，因为索引是有序的，它只要去索引头块和索引尾块，即可查询到MIN/MAX值
ALTER TABLE T MODIFY OBJECT_ID NULL;
admin@ORCL> SELECT MAX(OBJECT_ID) FROM T;
执行计划
----------------------------------------------------------
Plan hash value: 1152835269
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 57075 | 724K| | |
----------------------------------------------------------------------------------------------
admin@ORCL> SELECT MIN(OBJECT_ID) FROM T;
执行计划
----------------------------------------------------------
Plan hash value: 1152835269
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 57075 | 724K| | |
----------------------------------------------------------------------------------------------
--此处没有用到索引,看到Oracle的优化器也不是太智能。
admin@ORCL> select max(object_id),min(object_id) from t;
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 162 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 50826 | 248K| 162 (2)| 00:00:02 |
---------------------------------------------------------------------------
--换种写法，就可以走索引了
SELECT MAX,MIN FROM (SELECT MAX(OBJECT_ID) MAX FROM T) A,(SELECT MIN(OBJECT_ID) MIN FROM T) B;
admin@ORCL> SELECT MAX,MIN FROM (SELECT MAX(OBJECT_ID) MAX FROM T) A,(SELECT MIN(OBJECT_ID) MIN FROM T) B
执行计划
----------------------------------------------------------
Plan hash value: 3758149690
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 323 (2)| 00:00:04 |
| 1 | NESTED LOOPS | | 1 | 26 | 323 (2)| 00:00:04 |
| 2 | VIEW | | 1 | 13 | 162 (2)| 00:00:02 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 50826 | 248K| | |
| 5 | VIEW | | 1 | 13 | 162 (2)| 00:00:02 |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 50826 | 248K| | |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
--索引回表
--TABLE ACCESS BY INDEX ROWID，实际上是查询返回的值不在索引里面，索引需要根据rowid返回表，去查询得出。
--若查询的列可以在索引中查询到，则可以避免回表
--若需要查询多列，可以考虑建立复合索引，但要注意平衡，如果联合索引太多，必然导致索引过大
--虽然减少了回表动作，但增加了索引的大小，意味着查询就要遍历更多的索引块
--建议不要超过3个
admin@ORCL> select * from t where object_id = 20;
执行计划
----------------------------------------------------------
Plan hash value: 4285561625
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
admin@ORCL> select object_id from t where object_id = 20;
执行计划
----------------------------------------------------------
Plan hash value: 2498590897
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
--Order by 之排序优化
--看来按字段排序，考虑用到索引，也会考虑到null值
SELECT * FROM T ORDER BY OBJECT_ID
admin@ORCL> SELECT * FROM T;
已选择50826行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 4616K| 163 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50826 | 4616K| 163 (3)| 00:00:02 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4058 consistent gets
0 physical reads
0 redo size
5460695 bytes sent via SQL*Net to client
37653 bytes received via SQL*Net from client
3390 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50826 rows processed
--来看下没有排序，访问T表的资源
admin@ORCL> SELECT * FROM T ORDER BY OBJECT_ID;
已选择50826行。
执行计划
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 4616K| | 1259 (2)| 00:00:16 |
| 1 | SORT ORDER BY | | 50826 | 4616K| 12M| 1259 (2)| 00:00:16 |
| 2 | TABLE ACCESS FULL| T | 50826 | 4616K| | 163 (3)| 00:00:02 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
704 consistent gets
0 physical reads
0 redo size
2470336 bytes sent via SQL*Net to client
37653 bytes received via SQL*Net from client
3390 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50826 rows processed
--查看段大小，发现只有6M，而排序段用了12M
admin@ORCL> SELECT OWNER, SEGMENT_NAME, BYTES / 1024 / 1024
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME = 'T'
4 AND OWNER = 'ADMIN';
OWNER SEGMENT_NAME BYTES/1024/1024
------------------------------ -------------------- ---------------
ADMIN T 6
--来看下没有排序的资源消耗，cost为163,排序使用了1259,逻辑读为4058，而排序只使用了704.这点蛮奇怪，但
--真正决定性能的是cost的高低和真实完成的时间(cost右边的时间)，Oracle执行计划也是根据cost来决定执行路径
admin@ORCL> SELECT * FROM T;
已选择50826行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 4616K| 163 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50826 | 4616K| 163 (3)| 00:00:02 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4058 consistent gets
0 physical reads
0 redo size
5460695 bytes sent via SQL*Net to client
37653 bytes received via SQL*Net from client
3390 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50826 rows processed
--看看索引的cost
ALTER TABLE T MODIFY OBJECT_ID NOT NULL;
--发现oracle第一次走索引扫描，也会有排序的动作
admin@ORCL> SELECT * FROM T ORDER BY OBJECT_ID;
已选择50826行。
执行计划
----------------------------------------------------------
Plan hash value: 2718353294
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 4616K| 946 (1)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50826 | 4616K| 946 (1)| 00:00:12 |
| 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 50826 | | 115 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
543 recursive calls
0 db block gets
7749 consistent gets
864 physical reads
0 redo size
5460695 bytes sent via SQL*Net to client
37653 bytes received via SQL*Net from client
3390 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
50826 rows processed
--第二次执行就没有排序动作咯，这点也是有点奇怪噻(5 sorts (memory))。但是走全表扫描排序时，会走SORT ORDER BY 步骤，此步骤会使用
--大约表大小的2倍去排序。此点比较消耗时间
--另外发现使用索引时，逻辑读会大很多，这个是因为走索引，只能通过单个块查询，而全表扫描可以读多个块。
admin@ORCL> SELECT * FROM T ORDER BY OBJECT_ID;
已选择50826行。
执行计划
----------------------------------------------------------
Plan hash value: 2718353294
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 4616K| 946 (1)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50826 | 4616K| 946 (1)| 00:00:12 |
| 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 50826 | | 115 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7666 consistent gets
0 physical reads
0 redo size
5460695 bytes sent via SQL*Net to client
37653 bytes received via SQL*Net from client
3390 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50826 rows processed
--DISTINCT 排重优化
--大多数情况，使用索引消除重复，收效不是太明显。最好从业务上考虑，不保留重复。
UPDATE T SET OBJECT_ID =3 WHERE ROWNUM <=10000;
COMMIT;
--可以看出,虽然执行计划中，sorts为0.但HASH UNIQUE仍然使用了1M多的空间去重复。
admin@ORCL> SELECT DISTINCT OBJECT_ID FROM T;
已选择40827行。
执行计划
----------------------------------------------------------
Plan hash value: 1793979440
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 248K| | 325 (4)| 00:00:04 |
| 1 | HASH UNIQUE | | 50826 | 248K| 1208K| 325 (4)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T | 50826 | 248K| | 162 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
704 consistent gets
0 physical reads
0 redo size
598769 bytes sent via SQL*Net to client
30316 bytes received via SQL*Net from client
2723 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40827 rows processed
--修改T为非空
ALTER TABLE T MODIFY OBJECT_ID NULL;
--table access full变为index fast full scan,存在hash unique.
--cost提高不少。但执行计划没有去除排序
--index fast full scan 会一次性读取多个索引块，而index full scan扫描一次只能读取一个块.
--所以 index fast full scan无法排重,而index full scan是可以排重的。
--count(*)和sum无须用到排序，所以一般走 index fast null scan
admin@ORCL> SELECT DISTINCT OBJECT_ID FROM T;
已选择40827行。
执行计划
----------------------------------------------------------
Plan hash value: 2729247865
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 248K| | 191 (5)| 00:00:03 |
| 1 | HASH UNIQUE | | 50826 | 248K| 1208K| 191 (5)| 00:00:03 |
| 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| | 27 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
186 consistent gets
0 physical reads
0 redo size
598769 bytes sent via SQL*Net to client
30316 bytes received via SQL*Net from client
2723 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40827 rows processed
--强制走索引，效率不如上面
admin@ORCL> SELECT /*+ index(t) */DISTINCT OBJECT_ID FROM T;
已选择40827行。
执行计划
----------------------------------------------------------
Plan hash value: 503711260
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50826 | 248K| 278 (4)| 00:00:04 |
| 1 | SORT UNIQUE NOSORT| | 50826 | 248K| 278 (4)| 00:00:04 |
| 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 50826 | 248K| 115 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2864 consistent gets
0 physical reads
0 redo size
598769 bytes sent via SQL*Net to client
30316 bytes received via SQL*Net from client
2723 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40827 rows processed
相关推荐
"数据库创建索引SQL Oracle" 数据库索引是数据库性能优化的重要手段之一。创建索引可以提高查询速度，降低数据库的负载，提高数据的安全性。本文将详细介绍数据库创建索引的原则、分类、创建方法、管理和优化等方面...
### Oracle数据库索引的维护 在Oracle数据库管理与优化的...综上所述，Oracle数据库索引的维护是一项复杂但至关重要的任务。通过合理地创建、管理和优化索引，可以显著提升数据库性能，为企业级应用提供更高效的支持。
Oracle数据库是世界上最广泛使用的数据库管理系统之一，其在数据管理和性能优化方面有着强大的功能。本笔记主要探讨了两个关键概念：约束和索引，这些都是Oracle数据库中的基础但至关重要的元素。 **1. 约束...
Oracle数据库是世界上最广泛使用的数据库系统之一，其性能优化的一个关键因素就是索引的使用。索引可以帮助快速查找和访问数据库中的数据，显著提高查询效率。本篇将详细讲解Oracle数据库中的索引及其重要性。 一、...
### Oracle 创建索引的基本规则 ...综上所述，正确创建和维护索引是Oracle数据库性能调优的重要组成部分。通过遵循上述基本原则，可以根据具体的应用场景灵活选择和调整索引策略，从而实现最佳的查询性能和资源利用率。
这篇博文将深入探讨Oracle中索引的基本概念、类型、创建与管理，以及如何通过索引来优化查询性能。 首先，我们来理解什么是索引。索引就像是书的目录，当我们需要查找某个特定的信息时，通过目录可以直接定位到相应...
Oracle 索引与分区索引介绍 Oracle 索引与分区索引是 Oracle 数据库中两个重要的概念，它们都是为了提高查询性能和数据存储效率而设计的。在本文中，我们将详细介绍 Oracle 索引与分区索引的概念、特点、分类、创建...
1. Oracle 数据库索引是一种数据结构，用于快速访问数据库表的特定信息。 2. 索引优化方法包括重新设计索引、避免系统排序和使用函数索引等。 3. 重新设计索引可以避免全表扫描操作，降低资源消耗，缩短执行时间。 4...
本篇文章将深入探讨Oracle索引的学习，重点关注索引的原理、类型、优缺点以及如何使用和分析执行计划。 首先，理解索引的基本概念。索引是一种特殊的数据结构，它存储在表空间中，用于加速对表数据的访问。当执行...
oracle 基本索引原理，oracle 学习真必不可少的资料。
1. 文本索引类型：Oracle提供了两种类型的全文索引，即BINARY XML索引和CTX（Context）索引。BINARY XML索引适用于XML文档，而CTX索引则适用于非XML文本。 2. CTX（Context）索引：这是Oracle全文索引的主要实现...
当查询条件包含对特定列的降序排序需求时，使用降序索引可以直接按照所需顺序进行查找，避免额外的排序操作，提升查询速度。 函数索引（Function-Based Index，简称FBI）：函数索引允许在索引创建时应用函数，即...
综上所述，Oracle中的索引管理是一项重要的数据库优化任务。正确地创建和维护索引可以帮助提高数据库性能，降低查询响应时间，并确保系统的高效运行。在实际应用中，还需要根据具体的业务需求和数据特性来调整索引...
Oracle数据库是全球广泛使用的数据库系统之一，其性能优化的关键之一在于有效的索引设计与使用。本文将深入探讨Oracle索引的原理、创建方法，并结合SQL优化策略，帮助你提升数据库查询速度，降低资源消耗。 首先，...
Oracle数据库中的索引是优化查询性能的关键工具，它允许快速定位和访问数据。常见的索引类型包括B*树索引和位图索引，每种都有其特定的应用场景和优势。 1. **B*树索引**：这是最常见的索引类型，类似于二叉树结构...
1. **INDEX SKIP SCAN**: 当创建了一个组合索引，但是查询只使用了索引的非第一列时，Oracle可能会选择不使用索引，因为它无法直接通过索引来定位数据。INDEX SKIP SCAN是一种优化策略，允许数据库跳过索引的第一个...
本篇文章将深入探讨Oracle中的B*Tree索引、反向索引、降序索引、位图索引和函数索引。 1. **B*Tree索引**： - B*Tree索引是Oracle中最常见的索引类型，其结构类似于二叉树，可以高效地处理高基数数据列，即具有...
Oracle 索引 使用方法，索引 使用原理， 索引 使用顺序过程
### Oracle 分区与索引详解 #### 一、Oracle 分区概述 ...综上所述，Oracle中的分区与索引是提高数据库性能的关键工具。通过合理的设计和管理，可以显著提升查询效率，同时降低数据管理和维护的成本。
### Oracle在线建立超大表的索引 #### 需求背景 在Oracle数据库中，为含有千万级别记录的大表创建索引是一项挑战性任务，尤其是对于那些处于高并发在线生产环境中的表。本文将详细介绍如何为一个核心大表（INFO_...