全表扫描访问方法
当时一个对象全扫描时，与该对象相关的所有数据块都必须取出并进行处理，以确定块中所包含的数据行是否是查询所需要的。Oracle必须将整个数据块读取到内存中以取得这个块中所存储的数据行的数据。
如何选择全扫描操作
创建两个测试表
sys@cdb$root:orclcdb> drop table t1;
Table dropped.
sys@cdb$root:orclcdb> drop table t2;
Table dropped.
sys@cdb$root:orclcdb> create table t1 as
2 select trunc((rownum-1)/100) id,
3 rpad(rownum,100) t_pad
4 from dba_source
5 where rownum <= 10000;
Table created.
sys@cdb$root:orclcdb> create index t1_idx1 on t1(id);
Index created.
sys@cdb$root:orclcdb> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
PL/SQL procedure successfully completed.
sys@cdb$root:orclcdb> create table t2 as
2 select mod(rownum,100) id,
3 rpad(rownum,100) t_pad
4 from dba_source
5 where rownum <= 10000;
Table created.
sys@cdb$root:orclcdb> create index t2_idx1 on t2(id);
Index created.
sys@cdb$root:orclcdb> exec dbms_stats.gather_table_stats(user,'t2',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
PL/SQL procedure successfully completed.
sys@cdb$root:orclcdb> select count(*) ct from t1 where id = 1;
CT
----------
100
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3695297570
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_IDX1 | 100 | 300 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
sys@cdb$root:orclcdb> select count(*) ct from t2 where id = 1;
CT
----------
100
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3450262756
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T2_IDX1 | 100 | 300 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
401 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
sys@cdb$root:orclcdb> 这个例子向你展示了基于数据存储方式的不同优化器的执行计划选择也可能不同。然而直到这点并不一定会使你写SQL查询时有所改变，它可用影响的是如何确定查询性能是否能满足服务水平协议（Service Level Agreement ,SLA）.
全表扫描与舍弃
全扫描是否为高效的选择取决于需要访问的数据块个数以及最终的结果集行数。
访问的数据块数据以及舍弃的数量越大，全表扫描的成本也就越高。
表T1和表T2的数据行和数据块统计信息。
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb> select table_name,num_rows,blocks from user_tables where table_name = 'T2';
TABLE_NAME
----------------------------------------------------------------------------------------------------
NUM_ROWS BLOCKS
---------- ----------
T2
10000 152
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3803619458
----------------------------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
----------------------------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 1 | 210 | 10
(0)| 00:00:01 |
|* 1 | FILTER | | | |
| |
| 2 | NESTED LOOPS | | 1 | 210 | 10
(0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 207 | 9
(0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 196 | 8
(0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 192 | 7
(0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 184 | 6
(0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 179 | 5
(0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 153 | 5
(0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 119 | 4
(0)| 00:00:01 |
| 10 | MERGE JOIN CARTESIAN | | 1 | 95 | 3
(0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 39 | 0
(0)| 00:00:01 |
|* 12 | FIXED TABLE FIXED INDEX | X$KSPPI (ind:1) | 1 | 32 | 0
(0)| 00:00:01 |
|* 13 | FIXED TABLE FIXED INDEX | X$KSPPCV (ind:1) | 1 | 7 | 0
(0)| 00:00:01 |
| 14 | BUFFER SORT | | 1 | 56 | 3
(0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 56 | 3
(0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2
(0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_USER2 | 1 | 24 | 1
(0)| 00:00:01 |
|* 18 | TABLE ACCESS CLUSTER | TAB$ | 1 | 34 | 1
(0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0
(0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | I_IMSVC1 | 1 | 26 | 0
(0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1
(0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 1
(0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1
(0)| 00:00:01 |
| 24 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1
(0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0
(0)| 00:00:01 |
| 26 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1
(0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0
(0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2
(0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1
(0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2
(0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1
(0)| 00:00:01 |
| 32 | NESTED LOOPS SEMI | | 1 | 29 | 2
(0)| 00:00:01 |
|* 33 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1
(0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1
(0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR "O"."TYPE#"<>88 AN
D NOT
EXISTS (SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE" WHERE "UE"."USER#"=:B1 AND "TYPE#"
=:B2) OR EXISTS
(SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE" WHERE "UE"."USER#"=:B3 AND "UE"."TYPE#"=:
B4) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."T
YPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT
0 FROM "SYS"."USER$"
"U2","SYS"."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."
DATAOBJ#"=:B5 AND
"U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')
))))
12 - filter("KSPPINM"='_dml_monitoring_enabled' AND ("CON_ID"=0 OR "CON_ID"=3))
13 - filter("INDX"="INDX" AND ("CON_ID"=0 OR "CON_ID"=3))
15 - filter(BITAND("O"."FLAGS",128)=0)
16 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."NAME"='T2')
17 - access("O"."OWNER#"="U"."USER#")
18 - filter(BITAND("T"."PROPERTY",1)=0 AND BITAND("T"."PROPERTY",36893488147419103232)=0)
19 - access("O"."OBJ#"="T"."OBJ#")
20 - access("T"."OBJ#"="SVC"."OBJ#"(+) AND "SVC"."SUBPART#"(+) IS NULL)
21 - access("T"."BOBJ#"="CO"."OBJ#"(+))
22 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
23 - access("CX"."OWNER#"="CU"."USER#"(+))
25 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+)
)
27 - access("T"."TS#"="TS"."TS#")
28 - filter("TYPE#"=:B1)
29 - access("UE"."USER#"=:B1)
30 - filter("UE"."TYPE#"=:B1)
31 - access("UE"."USER#"=:B1)
33 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')
))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')
))
34 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
308 recursive calls
0 db block gets
301 consistent gets
7 physical reads
0 redo size
713 bytes sent via SQL*Net to client
436 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
1 rows processed
sys@cdb$root:orclcdb> 全扫描与多块读取
全扫描运算将会进行多块读取。也就是说一个单独的IO调用将会请求多个块而不是仅仅一个。所请求的数据块数目是可变的。
- Oracle不得不读取超过一定边界范围的数据块。
- 如果一个数据块已经在缓冲区缓存，那么它将会作为多块读取的一部分重新读取一遍。
- 超出了操作系统限制的多块读取大小。
全扫描与高水位线
当对扫描进行多块读取调用时，Oracle将最多读取到位于表中高水位线的数据块。高水位线标出了表中最后一块有数据写入的数据块。
高水位线
sys@cdb$root:orclcdb> -- List number of allocated blocks
sys@cdb$root:orclcdb> -- The highwater mark is the last block containing data.
sys@cdb$root:orclcdb> -- While this query doesn't specifically show the HWM,it gives you an idea.
sys@cdb$root:orclcdb> select blocks from user_segments where segment_name = 'T2';
BLOCKS
----------
256
1 row selected.
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb> -- List how many blocks contain data
sys@cdb$root:orclcdb> select count(distinct (dbms_rowid.rowid_block_number(rowid))) block_ct from t2;
BLOCK_CT
----------
152
1 row selected.
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb> -- List the lowest and highest block numbers for this table
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb> select min(dbms_rowid.rowid_block_number(rowid)) min_blk, max(dbms_rowid.rowid_block_number(rowid)) max_blk from t2;
MIN_BLK MAX_BLK
---------- ----------
37265 37528
1 row selected.
sys@cdb$root:orclcdb>
索引扫描访问方法
默认的索引类型是B-树索引，索引建立在表中的一个或多个列或者是列的表达式上，将列值和行编号一起存储。在索引中还保存着一些其他的信息，但是对你的来说仅仅需要关心列值和编号即可。
行编号：是用来唯一标记表中的行的伪列。它是物理表中行数据的内部地址，包含两个地址，其一指向数据表中包含该行的块所存放数据文件的地址，另一个是可用直接定位到数据行自身的这一行在数据块中的地址。
行编码解析
hr@orclpdb1:orclcdb> select e.rowid,
2 (select file_name
3 from dba_data_files
4 where file_id = dbms_rowid.rowid_to_absolute_fno(e.rowid,user,'EMPLOYEES')) filen,
5 dbms_rowid.rowid_block_number(e.rowid) block_no,
6 dbms_rowid.rowid_row_number(e.rowid) row_no
7 from employees e
8 where e.email = 'SKING';
ROWID File Name BLOCK_NO ROW_NO
------------------ -------------------------------------------------- ---------- ----------
AAASHtAAMAAAADPAAA /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 207 0
1 row selected.
hr@orclpdb1:orclcdb>
行编号指向一个特定行的准确位置。当使用索引访问数据行时，就会对谓语中提供的访问标准进行匹配，然后使用行编号访问指定的文件/数据块/数据行。索引扫描的数据块访问是通过单块读取来完成的。
索引结构
索引具有一定的逻辑结构，索引包含一个或多个层次的分支块以及一个层次的叶子块。分支块保存着下一层级分支所包含的值范围信息，它被用来在索引结构中进行搜索以获得所需的叶子块。索引的高度是指第一个分支块（根数据块）到叶子块的分支层级数目。
索引扫描类型
索引扫描有好几种不同的类型，但每种类型都有的共同点：它们都必须遍历索引结构以访问匹配搜索值的叶子索引块。
索引扫描类型包括索引范围扫描、索引唯一扫描、索引全扫描、索引跳跃扫描以及索引快速全扫描。
聚簇因子
索引的聚簇因子统计信息帮助优化器生成使用索引的成本信息，并且是表中建立了索引的数据排序优良度的一个度量值。
索引的聚簇因子向优化器表明了具有同样索引值的数据行是不是存放在同一个或连续的一系列数据块中，或者数据行是否被分散存放在表的多个数据块中。
索引聚簇因子
sys@cdb$root:orclcdb> set linesize 100
sys@cdb$root:orclcdb> col IDX_NAME format a30
sys@cdb$root:orclcdb> select t.table_name||'.'||i.index_name idx_name,
2 i.clustering_factor,t.blocks, t.num_rows
3 from user_indexes i,user_tables t
4 where i.table_name = t.table_name
5 and t.table_name in ('T1','T2')
6 order by t.table_name,i.index_name;
IDX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------ ----------------- ---------- ----------
T1.T1_IDX1 152 152 10000
T2.T2_IDX1 10000 152 10000
2 rows selected.
sys@cdb$root:orclcdb>由于聚簇因子是一个与索引相关的统计信息，它通过查看表中数据块来进行计算。
计算索引的聚簇因子
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb> select t.table_name||'.'||i.index_name idx_name,
2 i.clustering_factor,t.blocks,t.num_rows
3 from all_indexes i, all_tables t
4 where i.table_name = t.table_name
5 and t.table_name = 'EMPLOYEES'
6 and t.owner = 'HR'
7 and i.index_name = 'EMP_DEPARTMENT_IX'
8 order by t.table_name, i.index_name;
IDX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------ ----------------- ---------- ----------
EMPLOYEES.EMP_DEPARTMENT_IX 7 5 107
1 row selected.
sys@cdb$root:orclcdb> select department_id,last_name,blk_no,
2 lag(blk_no,1,blk_no) over(order by department_id) prev_blk_no,
3 case when blk_no != lag(blk_no,1,blk_no) over
4 (order by department_id) or rownum = 1
5 then '*** + 1'
6 else null
7 end cluf_ct
8 from (
9 select department_id,last_name,
10 dbms_rowid.rowid_block_number(rowid) blk_no
11 from hr.employees
12 where department_id is not null
13 order by department_id
14 );
DEPARTMENT_ID LAST_NAME BLK_NO PREV_BLK_NO CLUF_CT
------------- ------------------------- ---------- ----------- -------
10 Whalen 203 203 *** + 1
20 Hartstein 203 203
20 Fay 203 203
30 Raphaely 207 203 *** + 1
30 Colmenares 207 207
30 Khoo 207 207
30 Baida 207 207
30 Tobias 207 207
30 Himuro 207 207
40 Mavris 203 207 *** + 1
50 OConnell 203 203
50 Grant 203 203
50 Weiss 207 203 *** + 1
50 Fripp 207 207
50 Kaufling 207 207
50 Vollman 207 207
50 Mourgos 207 207
50 Nayer 207 207
50 Mikkilineni 207 207
50 Landry 207 207
50 Markle 207 207
50 Bissot 207 207
50 Atkinson 207 207
50 Marlow 207 207
50 Olson 207 207
50 Mallin 207 207
50 Rogers 207 207
50 Gee 207 207
50 Philtanker 207 207
50 Ladwig 207 207
50 Stiles 207 207
50 Seo 207 207
50 Patel 207 207
50 Rajs 207 207
50 Davies 207 207
50 Matos 207 207
50 Vargas 207 207
50 Taylor 207 207
50 Fleaur 207 207
50 Sullivan 207 207
50 Geoni 207 207
50 Sarchand 207 207
50 Bull 207 207
50 Dellinger 207 207
50 Cabrio 207 207
50 Chung 207 207
50 Dilly 207 207
50 Gates 207 207
50 Perkins 207 207
50 Bell 207 207
50 Everett 207 207
50 McCain 207 207
50 Jones 207 207
50 Walsh 207 207
50 Feeney 207 207
60 Lorentz 207 207
60 Pataballa 207 207
60 Austin 207 207
60 Ernst 207 207
60 Hunold 207 207
70 Baer 203 207 *** + 1
80 Olsen 207 203 *** + 1
80 Livingston 207 207
80 Taylor 207 207
80 Hutton 207 207
80 Abel 207 207
80 Kumar 207 207
80 Bates 207 207
80 Smith 207 207
80 Fox 207 207
80 Bloom 207 207
80 Ozer 207 207
80 Russell 207 207
80 Partners 207 207
80 Errazuriz 207 207
80 Cambrault 207 207
80 Zlotkey 207 207
80 Tucker 207 207
80 Bernstein 207 207
80 Hall 207 207
80 Johnson 207 207
80 Cambrault 207 207
80 Tuvault 207 207
80 King 207 207
80 Sully 207 207
80 McEwen 207 207
80 Smith 207 207
80 Doran 207 207
80 Sewall 207 207
80 Vishney 207 207
80 Greene 207 207
80 Marvins 207 207
80 Lee 207 207
80 Ande 207 207
80 Banda 207 207
90 King 207 207
90 Kochhar 207 207
90 De Haan 207 207
100 Chen 207 207
100 Faviet 207 207
100 Sciarra 207 207
100 Urman 207 207
100 Popp 207 207
100 Greenberg 207 207
110 Higgins 203 207 *** + 1
110 Gietz 203 203
106 rows selected.
sys@cdb$root:orclcdb> 这并不是准确计算聚簇因子的方法，但这个查询可用帮助你了解计算的概况。
每次当前行的块编号与前一行的块编号不同时把计数器加一从而计算出聚簇因子。
一种新的统计信息收集偏好——TABLE_CACHED_BLOCKS。
索引唯一扫描
当谓语中包含使用UNIQUE或PRIMARY KEY 索引的列作为条件时就会选用索引唯一扫描。这种类型的索引能够保证对于某个特定的值只返回一行数据。索引结构将会被从根到叶子进行遍历直到某个条目，取出其行编号，然后使用这个行编号访问包含这一行的表数据块。计划中的TABLE ACCESS BY INDEX ROWID步骤表明了对于表数据块的访问。
索引唯一扫描
hr@orclpdb1:orclcdb> set autotrace on
hr@orclpdb1:orclcdb> select * from employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------------------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 24000 90
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1263 bytes sent via SQL*Net to client
398 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@orclpdb1:orclcdb> 索引范围扫描
当谓语中包含将会返回一定范围数据的条件时，就会选用索引范围扫描。索引可以是唯一或者不唯一的。
所指定的条件可以使用诸如<、>、LIKE、BETWEEN、=
索引范围扫描
hr@orclpdb1:orclcdb> select * from employees where department_id = 60;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------------------- ---------- ---------- -------------- ---------- -------------
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-2006 00:00:00 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-2007 00:00:00 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 25-JUN-2005 00:00:00 IT_PROG 4800 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-2006 00:00:00 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-2007 00:00:00 IT_PROG 4200 103 60
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 235881476
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 345 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 5 | 345 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=60)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1751 bytes sent via SQL*Net to client
631 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
hr@orclpdb1:orclcdb> 使用索引范围扫描来避免排序
hr@orclpdb1:orclcdb> select * from employees
2 where department_id in (90,100)
3 order by department_id desc;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------------------- ---------- ---------- -------------- ---------- -------------
113 Luis Popp LPOPP 515.124.4567 07-DEC-2007 00:00:00 FI_ACCOUNT 6900 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-2006 00:00:00 FI_ACCOUNT 7800 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-2005 00:00:00 FI_ACCOUNT 7700 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-2005 00:00:00 FI_ACCOUNT 8200 108 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-2002 00:00:00 FI_ACCOUNT 9000 108 100
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-2002 00:00:00 FI_MGR 12008 101 100
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-2001 00:00:00 AD_VP 17000 100 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-2005 00:00:00 AD_VP 17000 100 90
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 24000 90
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 621 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 9 | 621 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX | 9 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2033 bytes sent via SQL*Net to client
477 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
hr@orclpdb1:orclcdb> 索引全扫描
在好几种情况下都会选择索引全扫描，包括：当没有谓语但是所需列的列表可以通过其中一列的索引获得，谓语中包含一个位于索引中非引导列上的条件，或者数据可以通过一个排过序的索引来获取并且会省去单独的排序步骤。
hr@orclpdb1:orclcdb> select email from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_EMAIL_UK | 107 | 856 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2936 bytes sent via SQL*Net to client
466 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> select first_name,last_name from employees
2 where first_name like 'A%';
FIRST_NAME LAST_NAME
-------------------- -------------------------
Amit Banda
Alexis Bull
Anthony Cabrio
Alberto Errazuriz
Adam Fripp
Alexander Hunold
Alyssa Hutton
Alexander Khoo
Allan McEwen
Alana Walsh
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 234440638
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | EMP_NAME_IX | 3 | 45 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME" LIKE 'A%')
filter("FIRST_NAME" LIKE 'A%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
857 bytes sent via SQL*Net to client
668 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> select * from employees order by employee_id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2761389396
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| EMP_EMP_ID_PK | 107 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
10587 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
hr@orclpdb1:orclcdb> 索引全扫描运算将会扫描索引结构中的每一个叶子块，读取每个条目的行编号，并且取出数据行。每个叶子块都要访问。通常比全表扫描效率要高，因为每一个索引块要比表数据块包含更多条目，从而总的需要访问的块数也就相应减少。
索引全扫描求最小、最大值的最优方法
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> select min(department_id) from employees;
MIN(DEPARTMENT_ID)
------------------
10
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 613773769
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
560 bytes sent via SQL*Net to client
402 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@orclpdb1:orclcdb> select max(department_id) from employees;
MAX(DEPARTMENT_ID)
------------------
110
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 613773769
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
561 bytes sent via SQL*Net to client
402 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@orclpdb1:orclcdb> select min(department_id),max(department_id) from employees;
MIN(DEPARTMENT_ID) MAX(DEPARTMENT_ID)
------------------ ------------------
10 110
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
652 bytes sent via SQL*Net to client
642 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@orclpdb1:orclcdb> select (select min(department_id) from employees) min_id,
2 (select max(department_id) from employees) max_id
3 from dual;
MIN_ID MAX_ID
---------- ----------
10 110
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2189307159
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 3 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
628 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@orclpdb1:orclcdb> 索引跳跃扫描
hr@orclpdb1:orclcdb> create index emp_jobfname_ix on employees2(job_id,first_name,salary);
Index created.
hr@orclpdb1:orclcdb> select * from employees2 where first_name = 'William';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------------------- ---------- ---------- -------------- ---------- -------------
206 William Gietz WGIETZ 515.123.8181 07-JUN-2002 00:00:00 AC_ACCOUNT 8300 205 110
171 William Smith WSMITH 011.44.1343.629268 23-FEB-2007 00:00:00 SA_REP 7400 .15 148 80
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 776999030
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES2 | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | EMP_JOBFNAME_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FIRST_NAME"='William')
filter("FIRST_NAME"='William')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1544 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
hr@orclpdb1:orclcdb> select /*+ full(employees2) */ * from employees2 where first_name = 'William';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------------------- ---------- ---------- -------------- ---------- -------------
206 William Gietz WGIETZ 515.123.8181 07-JUN-2002 00:00:00 AC_ACCOUNT 8300 205 110
171 William Smith WSMITH 011.44.1343.629268 23-FEB-2007 00:00:00 SA_REP 7400 .15 148 80
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2513133951
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES2 | 1 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FIRST_NAME"='William')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1536 bytes sent via SQL*Net to client
439 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
hr@orclpdb1:orclcdb> -- How many distinct values of job_id?
hr@orclpdb1:orclcdb> select count(distinct job_id) ct from employees;
CT
----------
19
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3524063234
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | VIEW | VW_DAG_0 | 19 | 133 | 1 (0)| 00:00:01 |
| 3 | SORT GROUP BY NOSORT| | 19 | 171 | 1 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | EMP_JOB_IX | 107 | 963 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
1 physical reads
0 redo size
544 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@orclpdb1:orclcdb> 对于索引扫描类型，需要记住的一点是索引引导列的唯一值越少，所需要的逻辑子索引数目也越少，从而需要访问的总块数也越少。
索引快速全扫描
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> alter table employees2 modify(email null);
Table altered.
hr@orclpdb1:orclcdb> select email from employees2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2513133951
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES2 | 107 | 856 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
2936 bytes sent via SQL*Net to client
471 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
107 rows processed
hr@orclpdb1:orclcdb> alter table employees2 modify(email not null);
Table altered.
hr@orclpdb1:orclcdb> select email from employees2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2513133951
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES2 | 107 | 856 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
20 recursive calls
0 db block gets
43 consistent gets
0 physical reads
0 redo size
2936 bytes sent via SQL*Net to client
471 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
107 rows processed
hr@orclpdb1:orclcdb>
扫一扫
1000
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
