欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

所获,不止Oracle之索引篇(1) 博客分类: Oracle 优化  

程序员文章站 2024-03-22 12:51:04
...
/*
索引
索引由三部分组成,根块(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