Oracle學(xué)習(xí)之性能優(yōu)化（六）訪(fǎng)問(wèn)路徑
訪(fǎng)問(wèn)路徑是指Oracle找到用戶(hù)需要的數(shù)據(jù)的方法，這些方法很少，包括：
為高陵等地區(qū)用戶(hù)提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及高陵網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為網(wǎng)站制作、成都網(wǎng)站建設(shè)、高陵網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專(zhuān)業(yè)、用心的態(tài)度為用戶(hù)提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶(hù)的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！
聲名狼藉的全表掃描--人們不惜一切視圖避免的（曲解的）訪(fǎng)問(wèn)路徑。
各種類(lèi)型的索引掃描--這是人們感覺(jué)良好的訪(fǎng)問(wèn)路徑（多數(shù)情況下是被曲解的）。
通過(guò)hash或者rowid的方式直接訪(fǎng)問(wèn)，通常對(duì)于單數(shù)據(jù)行來(lái)說(shuō)，是最快的。
并沒(méi)有一種訪(fǎng)問(wèn)路徑是最好的，如果有，那么Oracle只需提供這一種訪(fǎng)問(wèn)路徑就好了。
全表掃描
全掃描就是順序的讀取表中的所有數(shù)據(jù)塊。采用多塊讀的方式，從頭開(kāi)始掃描表中的塊，直到高水位線(xiàn)。全掃描是處理大數(shù)據(jù)量行之有效的方法。需要牢記：全掃描并不邪惡，多數(shù)情況下全掃描是獲得結(jié)果的最快方法。
全掃描每次讀取的塊數(shù)由參數(shù)db_file_multiblock_read_count指定
SQL> show parameter db_file_mu NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_file_multiblock_read_count integer 128
2.rowid 訪(fǎng)問(wèn)
rowid是一行數(shù)據(jù)的物理位置，訪(fǎng)問(wèn)單行數(shù)據(jù)的速度是最快的。
SQL> select * from emp where rowid ='AAASZHAAEAAAACXAAN'; 7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10
通過(guò)索引的方式訪(fǎng)問(wèn)數(shù)據(jù)，其實(shí)也是通過(guò)索引，先找到這行數(shù)據(jù)的rowid，然后再通過(guò)rowid訪(fǎng)問(wèn)數(shù)據(jù)。
SQL> set autotrace on traceonly SQL> select * from emp where empno=7934; Execution Plan ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------
rowid還可以進(jìn)行范圍掃描。
SQL> select * from emp where rowid between 'AAASZHAAEAAAACXAAA' and 'AAASZHAAEAAAACXAAN'; 7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20 7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30 7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10 14 rows selected.
3. 索引掃描
索引掃描是最常見(jiàn)的數(shù)據(jù)訪(fǎng)問(wèn)之一，例如
SQL> set autotrace on traceonly SQL> select * from emp where empno=7934; Execution Plan ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------
我們下面主要以b-tree索引為例
索引唯一性?huà)呙?/p>
優(yōu)化器知道索引列的值是唯一的，查詢(xún)結(jié)果只返回一行。這種索引的訪(fǎng)問(wèn)速度最快，找到一行數(shù)據(jù)就不再繼續(xù)掃描索引，直接返回。
SQL> select * from emp where empno=7934; 7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10 Execution Plan ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------
實(shí)際上Oracle中并沒(méi)有非唯一索引，在非唯一索引中，Oracle將數(shù)據(jù)的rowid添加到索引鍵中使其唯一。
索引范圍掃描
SQL> set autot traceonly SQL> select empno from emp where empno<5000; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1567865628 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| PK_EMP | 1 | 4 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------
有取的是，索引可以按照兩個(gè)方向去掃描索引
SQL> select empno from emp where empno<5000 order by empno;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1567865628
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_EMP | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"<5000)
SQL> select empno from emp where empno<5000 order by empno desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2474278666
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN DESCENDING| PK_EMP | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------這個(gè)的好處是避免排序操作
如果你建立的是非唯一索引，即便你使用=查詢(xún)，也是范圍掃描
SQL> create index ind_emp_ename on emp(ename);
Index created.
SQL> select * from emp where ename='KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 2929622481
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='KING')索引全掃描
SQL> select empno from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 179099197 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------
索引全掃描，并不是掃描全部的索引。它實(shí)際上只需掃描索引的葉子節(jié)點(diǎn)。但是為了找到葉子節(jié)點(diǎn)的位置，也會(huì)掃描部分的分支節(jié)點(diǎn)。
我們看如下查詢(xún)
SQL> select empno,ename from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 140 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
查詢(xún)列ename并不在索引中，所以走的是全表掃描。但是如果我們將語(yǔ)句做如下修改。
SQL> select empno,ename from emp order by empno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4170700152 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Oracle為了避免排序操作，而使用了索引全掃描。因?yàn)樗饕怯行虻臄?shù)據(jù)，并且索引全掃描是按順序的單塊讀操作。
max和min
SQL> select max(empno) from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 1707959928 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| PK_EMP | 1 | 4 | 1 (0)| 00:00:01 |
該掃描使用了索引全掃描，但其實(shí)并非真正的全掃描，max和min限定詞使得Oracle知道何時(shí)停止，它只是掃描最高塊或者最低塊。
索引跳躍掃描
通常要使用索引，索引的前置列一定要出現(xiàn)在查詢(xún)條件中。
SQL> create table t(a int,b int ,c int,d int,e int,f int,g int); SQL> create index t_idx on t(a,b,c);
通常情況下只有如下的查詢(xún)才會(huì)使用索引
select * from t where a =:a; select * from t where a =:a and b =:b; select * from t where a =:a and b =:b and c =:c;
但是如下查詢(xún)不會(huì)使用索引（除了使用hint強(qiáng)制索引全掃描）
select * from t where b =:b; select * from t where c =:c; select * from t where b =:b and c =:c;
Oracle 9i后實(shí)現(xiàn)了跳躍索引掃描，條件如下：
謂詞中使用了索引中其他的列。
前置列值的DISTINCT_NUM比較少。
我們看看如下示例
SQL> create table t as
2 select mod(rownum,3) a,rownum b,rownum c,object_name d
3 from all_objects;
Table created.
SQL> create index t_idx on t(a,b,c);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select * from t where b=1 and c=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2053318169
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 34 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T_IDX | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1 AND "C"=1)
filter("B"=1 AND "C"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
724 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed因?yàn)閍的值比較少，只有3個(gè)，Oracle把索引(a,b,c) 看成3個(gè)小索引 。
索引快速全掃描
索引快速全掃描與索引全掃描明顯的不同，它有如下特征
它讀取索引中的每個(gè)塊，包括所有分支塊。
它采用多塊讀，像全表掃描一樣。
它不按排序順序掃描索引。
我們先建立一個(gè)表，并插入大量數(shù)據(jù)。
SQL> create table big_table as select * from dba_objects; Table created. SQL> insert into big_table select * from big_table; 74577 rows created. SQL> insert into big_table select * from big_table; 223731 rows created. SQL> / 447462 rows created. SQL> commit; Commit complete. SQL> alter table big_table modify object_id not null; Table altered. SQL> create index idx_big_table_objid on big_table(object_id); Index created. SQL> analyze table big_table compute statistics; Table analyzed.
執(zhí)行如下查詢(xún)
SQL> set autot traceonly SQL> select object_id from big_table; 894924 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 205523069 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 894K| 3495K| 544 (2)| 00:00:07 | | 1 | INDEX FAST FULL SCAN| IDX_BIG_TABLE_OBJID | 894K| 3495K| 544 (2)| 00:00:07 | -------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 15 recursive calls 0 db block gets 61534 consistent gets 2 physical reads 0 redo size 15755358 bytes sent via SQL*Net to client 656794 bytes received via SQL*Net from client 59663 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 894924 rows processed
查詢(xún)使用的是索引快速全掃描。
有心的人可以思考一下，如下查詢(xún)?yōu)樯稕](méi)有使用索引快速全掃描，而使用了索引全掃描。
SQL> select empno from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 179099197 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 686 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
索引連接
索引連接（index join）是在表中存在多個(gè)索引時(shí)針對(duì)某個(gè)查詢(xún)所選中的索引路徑。
我們看如下例子
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index t1_idx1 on t1(object_id);
Index created.
SQL> create index t1_idx2 on t1(owner,object_type);
Index created.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> set autot traceonly
SQL> select object_id,owner,object_type from t1
2 where object_id between 100 and 2000
3 and owner='SYS';
1478 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2563395799
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 1173 | 18 (6)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 69 | 1173 | 18 (6)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| T1_IDX1 | 69 | 1173 | 7 (15)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T1_IDX2 | 69 | 1173 | 12 (9)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<=2000 AND "OWNER"='SYS' AND "OBJECT_ID">=100)
2 - access(ROWID=ROWID)
3 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
4 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
215 consistent gets
0 physical reads
0 redo size
32014 bytes sent via SQL*Net to client
1601 bytes received via SQL*Net from client
100 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1478 rows processed優(yōu)化器通過(guò)掃描T1_IDX1，T1_IDX2得到結(jié)果集，用兩個(gè)結(jié)果集的rowid進(jìn)行join運(yùn)算，得到返回集。
這樣避免掃描表。
分享名稱(chēng)：Oracle學(xué)習(xí)之性能優(yōu)化（六）訪(fǎng)問(wèn)路徑
轉(zhuǎn)載來(lái)源：http://bm7419.com/article28/igojcp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、企業(yè)建站、App開(kāi)發(fā)、搜索引擎優(yōu)化、服務(wù)器托管、網(wǎng)站導(dǎo)航
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 網(wǎng)站建設(shè)設(shè)置面包屑導(dǎo)航 2016-10-24
- 該如何布置網(wǎng)站建設(shè)的面包屑導(dǎo)航 2021-05-05
- 什么是面包屑導(dǎo)航和它的作用是什么 2021-09-21
- 網(wǎng)站面包屑導(dǎo)航設(shè)計(jì)注意事項(xiàng) 2017-02-17
- 網(wǎng)站的創(chuàng)建中面包屑導(dǎo)航欄的作用有哪些 2021-07-04
- 給你網(wǎng)站頁(yè)面設(shè)計(jì)的面包屑導(dǎo)航你真的會(huì)用么? 2022-08-25
- 北京網(wǎng)站建設(shè)講解面包屑導(dǎo)航有什么作用？ 2021-05-21
- 網(wǎng)站面包屑導(dǎo)航詳情使用方法和作用 2014-10-10
- 面包屑導(dǎo)航在網(wǎng)站中的作用 2021-12-20
- 成都網(wǎng)絡(luò)公司告訴你有關(guān)面包屑導(dǎo)航的事 2016-11-01
- 面包屑導(dǎo)航對(duì)于設(shè)計(jì)網(wǎng)站來(lái)說(shuō)必不可少 2022-10-20
- 該如何布置網(wǎng)站建設(shè)的面包屑導(dǎo)航 2022-11-11