兩種基本的數(shù)據(jù)訪問途徑：全掃描或者索引掃描。
create table t1 as
select trunc((rownum-1)/100) id,
rpad(rownum,100) t_pad
from dba_source
where rownum<=10000;
create index t1_idx1 on t1(id);
create table t2 as
select mod(rownum,100) id,
rpad(rownum,100) t_pad
from dba_source
where rownum<=10000;
create index t2_idx1 on t2(id);
-- exec dbms_stats.gather_table_stats(user,'t2',method_opt=>'FOR ALL COLUMNS SIZE 1',cascade => TRUE);
SELECT * FROM T1 WHERE rownum<100;
SELECT * FROM T2 WHERE rownum<100;
select count(*) ct from t1 where id=1;
explain plan for
select count(*) ct from t1 where id=1;
select * from table(dbms_xplan.display);
select count(*) ct from t2 where id=1;
explain plan for
select count(*) ct from t2 where id=1;
select * from table(dbms_xplan.display);
上面這個(gè)例子展示了基于數(shù)據(jù)存儲(chǔ)方式的不同優(yōu)化器的執(zhí)行計(jì)劃選擇也可能不同。
Ｏracle 12c改進(jìn)了優(yōu)化器，使得兩種計(jì)劃都可以使用索引。
全掃描與舍棄
全掃描是否為高效取決于需要訪問的數(shù)據(jù)塊個(gè)數(shù)以及最終的結(jié)果集行數(shù)。如上例所示，數(shù)據(jù)的存儲(chǔ)方式在決策過程中扮演了重要的角色。此外，全掃描是否為高效選擇的另一個(gè)關(guān)鍵因素是舍棄。舍棄的行是那些通過篩選謂語驗(yàn)證，被證明是不符合篩選條件后從最終的結(jié)果集中剔除的數(shù)據(jù)行。
--表t1和t2的數(shù)據(jù)行和數(shù)據(jù)塊統(tǒng)計(jì)信息
select table_name, num_rows, blocks
from user_tables
where table_name = 'T1';
select table_name, num_rows, blocks
from user_tables
where table_name = 'T2';
全掃描與多塊讀取
全掃描將會(huì)時(shí)行多塊讀取，也就是一個(gè)單獨(dú)的ＩＯ調(diào)用將會(huì)請(qǐng)求多個(gè)塊而不僅僅是一個(gè)。請(qǐng)求的數(shù)據(jù)塊數(shù)量可以從１個(gè)到db_file_multiblock_read_count參數(shù)所指定的數(shù)目范圍之間的任意個(gè)。
全掃描與高水位
當(dāng)對(duì)全掃描多塊讀取調(diào)用時(shí)，oracle將最多讀取到位于表中高水位線的數(shù)據(jù)塊。高水位線標(biāo)出了表中最后一塊有數(shù)據(jù)寫入的數(shù)據(jù)塊。為了保持技術(shù)上的正確性，這實(shí)際上應(yīng)該稱為“底”高水位線。
--列出分配的塊數(shù)
select blocks from user_segments where segment_name = 'T2';
--列出多少塊包含數(shù)據(jù)
select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct
from T2;
--列出這個(gè)表的最低和最高塊數(shù)
select min(dbms_rowid.rowid_block_number(rowid)) min_blk,
max(dbms_rowid.rowid_block_number(rowid)) max_blk
from T2;
下面證明：全表掃描是正確的執(zhí)行了計(jì)劃選擇，讀取額外的空塊所帶來的成本也可能嚴(yán)重降低性能。對(duì)于頻繁加載和清除的表，你可能發(fā)現(xiàn)響應(yīng)時(shí)間會(huì)變慢。
SQL> delete from T2;
--列出多少塊包含數(shù)據(jù)
SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2;
--執(zhí)行全表掃描，注意邏輯讀
SQL> set autotrace traceonly
SQL> select * from T2;
SQL> set autotrace off
SQL> truncate table T2;
SQL> set autotrace on
SQL> select * from T2;
索引掃描訪問方法
默認(rèn)的索引是Ｂ－樹索引。
索引建立在表中的一個(gè)或多個(gè)列或者是表的表達(dá)式上，將列值和行編號(hào)一起存儲(chǔ)。
行編號(hào)是唯一標(biāo)記表中行的偽列。
行編號(hào)解碼
column filen format a50 head 'file name';
select e.rowid,
(select file_name
from dba_data_files
where file_id =
dbms_rowid.rowid_to_absolute_fno(e.rowid, user, 'EMPLOYEES')) filen,
dbms_rowid.rowid_block_number(e.rowid) block_no,
dbms_rowid.rowid_row_number(e.rowid) row_no
from employees e
where e.email = 'SKING@gmail.com';
--select * from employees;
行編號(hào)是物理表中的行數(shù)據(jù)的內(nèi)部地址，包含兩個(gè)地址，其一是指向數(shù)據(jù)表中包含該行的塊所豐放數(shù)據(jù)文件的地址，另一個(gè)可以直接定位到數(shù)據(jù)行自身的這一行在數(shù)據(jù)塊中的地址。
隨著新行加入表中，新的索引條目也會(huì)加到塊中，直到新的條目再也加不進(jìn)去為止。此時(shí)oracle就會(huì)分配兩個(gè)新的索引塊并將所有索引條目加入這個(gè)兩新的葉子塊中。之前被填滿的那個(gè)單獨(dú)的根數(shù)據(jù)塊現(xiàn)在就替代為指向兩個(gè)新數(shù)據(jù)塊的指針。這個(gè)指針由指向新索引塊的相對(duì)數(shù)據(jù)塊地址(relative block address, RBA)和表明相關(guān)葉子塊中最低索引值（即排序順序中的最小值）的值組成。利用根數(shù)據(jù)塊中的這個(gè)信息，oracle就可以搜索索引以找到存有所所需值的特定葉子塊。到止前為止，這個(gè)索引的高度為２，blevel為１。
隨著時(shí)間的推進(jìn)，更多的行插入到表中，索引條目被加入剛才創(chuàng)建的兩個(gè)葉子數(shù)據(jù)塊中。當(dāng)兩個(gè)葉子塊被填滿之后，oracle將會(huì)增加一個(gè)新的葉子塊并為它分配介于己填滿的塊與新葉子塊之間的索引條目。每次一個(gè)葉子數(shù)據(jù)塊填滿并分裂之后，就會(huì)為這個(gè)新的葉子塊增加一個(gè)新指針到根數(shù)據(jù)塊中。最終，根數(shù)據(jù)塊也被填滿了，然后再重復(fù)將根數(shù)據(jù)塊分裂為兩個(gè)新的分支塊，當(dāng)發(fā)生這樣的分裂時(shí)，索引的高度將會(huì)增加為３而blevel變?yōu)椋病?br>
現(xiàn)在，隨著新的索引條目的加入，葉子數(shù)據(jù)塊將會(huì)被填滿并分裂，但不是添加一個(gè)新指針到根數(shù)據(jù)塊中，而是將指針加到相應(yīng)的分支數(shù)據(jù)塊中。最終，分支數(shù)據(jù)塊也將被填滿并分裂，這時(shí)又將有一個(gè)新索引條目加入到根數(shù)據(jù)塊中。隨著這些過程的不斷繼續(xù)，最后根數(shù)據(jù)塊又會(huì)被填滿并分裂，再一次增加索引的高度。記住唯一引起索引高度增加的就是當(dāng)根數(shù)據(jù)塊分裂的時(shí)候。由于這一點(diǎn)，所有葉子數(shù)據(jù)塊到根數(shù)塊的距離都是一樣的。一定要保持索引的高度是保持平衡的。
準(zhǔn)備測試數(shù)據(jù)
drop table t1;
create table t1 as
select trunc((rownum-1)/100) id,
rpad(rownum,100) t_pad
from dba_source
where rownum<=10000;
create index t1_idx1 on t1(id);
drop table t2;
create table t2 as
select mod(rownum,100) id,
rpad(rownum,100) t_pad
from dba_source
where rownum<=10000;
create index t2_idx1 on t2(id);
commit;
SELECT * FROM T1 WHERE rownum<100;
SELECT * FROM T2 WHERE rownum<100;
![隨機(jī)與順序載入數(shù)據(jù)行的對(duì)比圖]](http://upload-images.jianshu.io/upload_images/2026576-5d7b9990fe112018.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
索引聚族因子
select t.TABLE_NAME || '.' || i.INDEX_NAME idx_name,
i.CLUSTERING_FACTOR,
t.BLOCKS,
t.NUM_ROWS
from user_indexes i, user_tables t
where i.TABLE_NAME = t.TABLE_NAME
and t.TABLE_NAME in ('T1', 'T2')
order by t.TABLE_NAME, i.INDEX_NAME;
計(jì)算索引的聚簇因子
create index EMP_DEPARTMENT_IX on employees(DEPARTMENT_ID);
select t.TABLE_NAME || '.' || i.INDEX_NAME idx_name,
i.CLUSTERING_FACTOR,
t.BLOCKS,
t.NUM_ROWS
from all_indexes i, all_tables t
where i.TABLE_NAME = t.TABLE_NAME
and t.TABLE_NAME = 'EMPLOYEES'
and t.owner = 'SCOTT'
and i.INDEX_NAME = 'EMP_DEPARTMENT_IX'
order by t.TABLE_NAME, i.INDEX_NAME;
select department_id,
last_name,
blk_no,
lag(blk_no, 1, blk_no) over(order by department_id) prev_blk_no,
case
when blk_no != lag(blk_no, 1, blk_no)
over(order by department_id) or rownum = 1 then
'*** +1'
else
null
end cluf_ct
from (select department_id,
last_name,
dbms_rowid.rowid_block_number(rowid) blk_no
from SCOTT.Employees
where department_id is not null
order by department_id);
在oracle 12版本中，可以定義一個(gè)新的統(tǒng)計(jì)信息收集偏好-TABLE_CACHED_BLOCKS。
過程如下：
-- conn sys/0529 as sysdba;
dbms_stats.set_table_prefs(
ownname=>'SCOTT',
tabname=>'EMPLOYEES',
pname=>'TABLE_CACHED_BLOCKS',
pvalue=>50
);
oracle 12c執(zhí)行
exec dbms_stats.set_table_prefs(user,'T2',pname => 'TABLE_CACHED_BLOCKS',pvalue => 255);
exec dbms_stats.gather_table_stats(user,'T2');
select t.TABLE_NAME || '.' || i.INDEX_NAME idx_name,
i.CLUSTERING_FACTOR,
t.BLOCKS,
t.NUM_ROWS
from user_indexes i, user_tables t
where i.TABLE_NAME = t.TABLE_NAME
and t.TABLE_NAME in ('T1', 'T2')
order by t.TABLE_NAME, i.INDEX_NAME;
聚簇因子與表中數(shù)據(jù)相關(guān)而不是與索引相關(guān)。因此，重建索引對(duì)優(yōu)化器不有任何影響。
唯一索引
當(dāng)謂語中包含使用unique或primary key索引的列作為條件時(shí)就會(huì)選用索引唯一掃描。這種類型的索引能夠保證對(duì)于某個(gè)特定的值只返回一行數(shù)據(jù)。這種情況下，索引結(jié)構(gòu)將會(huì)被從根到葉子進(jìn)行遍歷直到某個(gè)條目，取出其行編號(hào)，然后使用這個(gè)行編號(hào)訪問包含這一行的表數(shù)據(jù)塊。計(jì)劃中的TABLE ACCESS BY INDEX ROWID步驟表明了對(duì)表數(shù)據(jù)塊的訪問。
唯一索引掃描
SQL> set autotrace off
SQL> set autotrace traceonly
SQL> select * from employees where employee_id=60;
范圍索引掃描
當(dāng)謂語中包含將會(huì)返回一定范圍數(shù)據(jù)的條件時(shí)，就會(huì)選用索引范圍掃描。索引可以是唯一的或者是不唯一的。
指定的條件可以使用如<、>、LIKE、BETWEEN和＝等運(yùn)算符。
范圍索引掃描
SQL> set autotrace traceonly
SQL> select * from employees where department_id>60;
范圍掃描將會(huì)從根數(shù)據(jù)塊開始到第一個(gè)包含符合條件的條目所在的葉子數(shù)據(jù)塊來遍歷索引結(jié)構(gòu)。再從那一點(diǎn)開始，從索引條目中取出行編號(hào)然后取出相應(yīng)的表數(shù)據(jù)塊（通過索引行編號(hào)訪問數(shù)據(jù)表）。在第一行取出來之后，之前的葉子索引塊將再一次被訪問并讀取下一個(gè)索引條目來獲取下一個(gè)行編號(hào)。這種索引葉子塊和表數(shù)據(jù)塊之間的反復(fù)來回將會(huì)不斷持續(xù)直到所有匹配的索引條目都被讀出。因此，所需訪問數(shù)據(jù)塊的次數(shù)為索引中的分支塊數(shù)加上符合條件的索引條目乖以２.
如果返回５行數(shù)據(jù)且blevel為３，則總的需要訪問的數(shù)據(jù)塊次數(shù)將是： 5行*２+３＝１３
注意： 有時(shí)使用索引范圍掃描的謂語實(shí)際上沒有使用，如果通配符開如"%abc"的like運(yùn)算符，優(yōu)化器將不會(huì)選用該列上的索引范圍掃描，因?yàn)闂l件太寬泛了。另一情況就是使用了組合索引中的非引導(dǎo)列。
索引范圍掃描可以使用一個(gè)升序排列的索引（默認(rèn)是升序的）來返回降序排列的數(shù)據(jù)行。
使用索引掃描來避免排序
set autotrace off;
set autotrace traceonly;
--使用索引掃描來避免排序
select *
from employees
where department_id in (90, 60, 88)
order by department_id desc;
已選擇10行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 3707994525
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 369 |2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 9 | 369 |2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX | 9 | |1 (0)| 00:00:01 |
INDEX RANGE SCAN DESCENDING，索引條目以相返的順序進(jìn)行讀取，避免了再做單獨(dú)的排序運(yùn)算。
索引全掃描
好幾種情況都會(huì)使用選擇索引全掃描，包括：當(dāng)沒有謂語但是所需的列可以通過其中的一列的索引獲取，謂語中包含一個(gè)位于索引中非引導(dǎo)列上的條件，或者數(shù)據(jù)可以通過一個(gè)排過序的索引來獲取并且會(huì)省去單獨(dú)的排序步驟。
set autotrace off;
set autotrace traceonly;
-- 當(dāng)沒有謂語但是所需的列可以通過其中的一列的索引獲取
create index EMP_EMAIL_UK on employees(EMAIL);
select email from employees;
--謂語中包含一個(gè)位于索引中非引導(dǎo)列上的條件
select first_name,last_name from employees where first_name like 'Li%';
--通過一個(gè)排過序的索引來獲取并且會(huì)省去單獨(dú)的排序步驟。
create index emp_emp_id_pk on employees(employee_id);
select * from employees order by employee_id;
select * from employees order by employee_id desc;
索引全掃描求最小、最大值的最優(yōu)方法
--create index emp_dept_id_idx on employees(department_id);
select min(department_id) from employees;
select max(department_id) from employees;
select min(department_id),max(department_id) from employees;
select (select min(department_id) from employees) min_id,
(select max(department_id) from employees) max_id
from dual;
索引跳躍掃描
當(dāng)謂語中包含位于索引非引導(dǎo)列上的條件，且引導(dǎo)列的值唯一時(shí)會(huì)選擇索引跳躍掃描。
set autotrace off;
set autotrace traceonly;
drop table employees2;
create table employees2 as select * from employees;
create index emp_jobfname_ix on employees2(job_id,first_name,salary);
select * from employees2 where first_name='Polly';
select /*+ full(employees2) */ * from employees2 where first_name='Polly';
select count(distinct job_id) ct from employees;
索引快速全掃描
索引快速全掃描更像全表掃描而不像其它類型的索引掃描。當(dāng)選用索引快速全掃描時(shí)，所有索引塊都將通過多塊讀取來進(jìn)行。這種類型的索引掃描是用來在查詢列表中所有字段都包含在索引中并且索引中至少有一列具有非空約束時(shí)替代全表掃描的。這種情況下，數(shù)據(jù)通過索引訪問而不必訪問表數(shù)據(jù)塊。
索引快速全掃描
create index emp2_email_idx on employees2(email);
alter table employees2 modify (email null);
select email from employees2;
alter table employees2 modify (email not null);
select email from employees2;
選擇快速合掃描運(yùn)算的依據(jù)是包含非空約束，如果沒有這個(gè)約束，將會(huì)選擇全表掃描運(yùn)算。
聯(lián)結(jié)方法
如果查詢中有多張表，在優(yōu)化器確定了每個(gè)表最恰當(dāng)?shù)脑L問方法之后，下一步就是確定將這些表聯(lián)結(jié)起來的最佳方法以及最恰當(dāng)?shù)捻樞?。任何時(shí)候當(dāng)from中有多個(gè)表時(shí)，都需要時(shí)行聯(lián)結(jié)。表之間的關(guān)系通過where子句中的一個(gè)條件定義。如果沒有指定條件，聯(lián)結(jié)就會(huì)隱含地定義為一個(gè)表中的每一行與另一個(gè)表中的所有行匹配。這稱為笛卡兒聯(lián)結(jié)。
聯(lián)結(jié)的方法有： 嵌套循環(huán)聯(lián)結(jié)，散列聯(lián)結(jié)，排序－合并聯(lián)結(jié)以及笛卡兒聯(lián)結(jié)。
每種聯(lián)結(jié)方法都有一定的最適合使用的條件。對(duì)于每對(duì)需要聯(lián)結(jié)的表，優(yōu)化器還必須確定表聯(lián)結(jié)的順序。
在第一對(duì)表聯(lián)結(jié)后，下一張表是與第一個(gè)聯(lián)結(jié)得到的結(jié)果行進(jìn)行聯(lián)結(jié)。在這次聯(lián)結(jié)進(jìn)行完了之后，下一個(gè)聯(lián)結(jié)還是與其結(jié)果聯(lián)結(jié)。這個(gè)過程會(huì)不斷進(jìn)行直到所有的表被聯(lián)結(jié)為止。
每個(gè)聯(lián)結(jié)都有兩個(gè)分支，所訪問的第一個(gè)表為驅(qū)動(dòng)表，第二個(gè)表為內(nèi)層表或被驅(qū)表。優(yōu)化器使用統(tǒng)計(jì)信息和where子句中的篩選條件計(jì)算每個(gè)表分別返回多少行數(shù)據(jù)，從而確定哪張表是驅(qū)動(dòng)表。預(yù)估大小最小的表通常被作為驅(qū)動(dòng)表。尤其是當(dāng)優(yōu)化器確定其中的一張表基于unique或primary key約束將最多返回一行時(shí)，這樣的表在聯(lián)結(jié)過程中將放在前面。使用外聯(lián)結(jié)運(yùn)算符的表必須放在所聯(lián)結(jié)表的后面。除這兩中特殊情況，其它表的聯(lián)結(jié)順序都是由優(yōu)化器使用所能得到的表，表及索引統(tǒng)計(jì)信息計(jì)算得到的選擇比來進(jìn)行評(píng)估的。
嵌套循環(huán)聯(lián)結(jié)
嵌套循環(huán)聯(lián)結(jié)使用一次訪問運(yùn)算所得稅到的結(jié)果集中的每一行與另一個(gè)表進(jìn)行對(duì)碰。如果結(jié)果集是有限的而且在用來聯(lián)結(jié)的列上建有索引，那么這種聯(lián)結(jié)的效率通常是最高的。
嵌套循環(huán)聯(lián)結(jié)就是一個(gè)循環(huán)嵌在另一個(gè)循環(huán)當(dāng)中。外層循環(huán)基本來說就是只查詢where子句中的與驅(qū)動(dòng)表有關(guān)的條件。當(dāng)數(shù)據(jù)行經(jīng)過了外層條件篩選并被確認(rèn)匹配條件后，這些行就會(huì)逐個(gè)進(jìn)入到內(nèi)層循環(huán)中。然于基于聯(lián)結(jié)列進(jìn)行逐行檢查看是否與被聯(lián)結(jié)的表中的某一行相匹配。如果這一行與第二次的檢查相匹配，就會(huì)被傳遞到查詢計(jì)劃的下一步，或者如果沒有更多步驟則會(huì)直接包含在最終的結(jié)果集中。
嵌套回路
drop table emp;
create table emp(
empno number primary key not null ,
ename varchar2(30) not null,
deptno number not null,
job varchar2(20) not null,
mgr number(4) not null,
hiredate date default sysdate ,
sal number,
comm number
);
drop table dept;
create table dept(
deptno number primary key not null,
dname varchar2(30) unique not null ,
loc varchar2(30) not null
);
delete from dept;
insert into dept values(1,'sales','shenzhen');
insert into dept values(2,'develop','shanghai');
insert into dept values(3,'finance','beijing');
insert into dept values(4,'hr','tokyo');
delete from emp;
insert into emp values(1,'litao',1,'saler',2,default,9000,0);
insert into emp values(2,'liqian',1,'saler',2,default,9000,0);
insert into emp values(3,'polly',2,'it manager',2,default,9000,0);
insert into emp values(4,'hameimei',2,'engineer',2,default,9000,0);
insert into emp values(5,'uncle wang',2,'it director',2,default,9000,0);
insert into emp values(6,'lilei',2,'maintainer',2,default,9000,0);
insert into emp values(7,'jack',3,'cashier',2,default,9000,0);
insert into emp values(8,'tom',3,'cashier',2,default,9000,0);
insert into emp values(9,'andre',4,'hr supervisor',2,default,9000,0);
select * from emp;
select * from dept;
commit;
select /*+ leading (emp,dept) use_nl (emp) */
empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno;
SQL> select /*+ leading (emp,dept) use_nl (emp) */
2 empno, ename, dname, loc
3 from emp, dept
4 where emp.deptno = dept.deptno;
未選定行
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 1770543576
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 90 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 1 | 43 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0010235 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 47 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
執(zhí)行計(jì)劃展示了將emp表作為驅(qū)動(dòng)表，dept表作為內(nèi)層表的嵌套循環(huán)聯(lián)結(jié)方法。
嵌套循環(huán)聯(lián)結(jié)順序比較
select /*+ leading (dept,emp) use_nl (dept) */
empno, ename, dname, loc
from emp, dept
where emp.deptno = dept.deptno;
SQL> set autotrace traceonly
SQL> select /*+ leading (dept,emp) use_nl (dept) */
2 empno, ename, dname, loc
3 from emp, dept
4 where emp.deptno = dept.deptno;
未選定行
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 90 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 47 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 1 | 43 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
排序－合并聯(lián)結(jié)
排序－合并聯(lián)結(jié)獨(dú)立地讀取需要聯(lián)結(jié)的兩張表，對(duì)每張表中的數(shù)據(jù)行（僅是那些滿足where子句中條件的數(shù)據(jù)行）按照聯(lián)結(jié)鍵排序，然后將排序后的數(shù)據(jù)行集合并。
set autotrace traceonly
select /*+ ordered */
empno, ename, dname, loc
from dept,emp
where emp.deptno = dept.deptno;
首先關(guān)注的是對(duì)dept表使用的索引掃描。因?yàn)樗饕龑磁判蚝蟮捻樞蚍祷財(cái)?shù)據(jù)，優(yōu)化器選擇使用索引來讀取表數(shù)據(jù)。這就意味著避免一次單獨(dú)的排序運(yùn)算。對(duì)于emp表則必須進(jìn)行全表掃描，然后單獨(dú)排序，因?yàn)樵赿eptno這一列上沒有索引可用。在兩個(gè)數(shù)據(jù)行集都準(zhǔn)備好并排序后，它們將會(huì)被合并到一起。
排序－合并聯(lián)結(jié)一般最適合數(shù)據(jù)篩選條件有限并返回有限數(shù)據(jù)行的查詢。如果沒有可用的直接訪問數(shù)據(jù)的索引，那么排序－合并聯(lián)結(jié)通常是較好的選擇?？偟膩碚f，條件為非等式時(shí)，如where table1.column1 between table2.column1 and table2.column2,排序－合并聯(lián)結(jié)通常是最好的選擇。
散列聯(lián)結(jié)
散列聯(lián)結(jié)，與排序－合并聯(lián)結(jié)類似，首先應(yīng)用where子句中的篩選標(biāo)準(zhǔn)單獨(dú)讀取要進(jìn)行的聯(lián)結(jié)的兩個(gè)表?；诒砗退饕慕y(tǒng)計(jì)信息，確定返回最少行數(shù)的表將完全散列化到內(nèi)存中。這個(gè)散列表包含了原表的所有數(shù)據(jù)行并被基于將聯(lián)結(jié)鍵轉(zhuǎn)化為散列值的隨機(jī)函數(shù)載入到散列桶中。只要有足夠的內(nèi)存空間，這個(gè)散列表將一直放在內(nèi)存中。如果沒有足夠的內(nèi)存，散列表將會(huì)被寫入到臨時(shí)磁盤空間。
下一步就是讀取另一張較大的表并對(duì)聯(lián)結(jié)鍵列應(yīng)用散列函數(shù)，然后利用得到的散列值對(duì)較小的在內(nèi)存中的散列表進(jìn)行探測以尋找匹配的第一個(gè)表的行數(shù)據(jù)所在的散列桶。每個(gè)散列桶都有一個(gè)放在其中的數(shù)據(jù)行列表。這個(gè)列表用來與探測行進(jìn)行匹配。如果匹配成功，則返回這一行數(shù)據(jù)，否則丟棄。較大的表只讀取一次，并檢查其中每一行來尋找匹配。這與嵌套循環(huán)聯(lián)結(jié)的不同之處在于此處內(nèi)層表被多次讀取。
散列聯(lián)結(jié)
SQL> set autotrace traceonly;
SQL> select /*+ use_hash(dept,emp) */ empno,ename,dname,loc from dept,emp where emp.deptno=dept.deptno;
未選定行
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 90 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 47 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 1 | 43 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
散列聯(lián)結(jié)只有在相等聯(lián)結(jié)的情況下才能進(jìn)行。
散列值
SQL> select distinct deptno,ora_hash(deptno,1000) hv from emp order by deptno;
DEPTNO HV
---------- ----------
1 355
2 979
3 660
4 560
SQL> select deptno from ( select distinct deptno,ora_hash(deptno,1000) hv from emp order by deptno) where hv between 300 and 600;
DEPTNO
----------
1
4
SQL> select distinct deptno, ora_hash(deptno,1000,50) hv from emp order by deptno;
DEPTNO HV
---------- ----------
1 580
2 93
3 756
4 785
SQL> select deptno from (select distinct deptno, ora_hash(deptno,1000,50) hv from emp order by deptno) where hv between 300 and 600;
DEPTNO
----------
1
用ora_hash函數(shù)來說明值是如何生成的，有３個(gè)參數(shù)：一個(gè)任何基本類型的輸入，最大散列桶值（最小值為０），以及一個(gè)種子值（默認(rèn)值也是０）。因此ora_hash(10,1000)將會(huì)返回一個(gè)０~１０００之間的整數(shù)值。這兒主要是為了說明它們?yōu)槭裁床贿m合用于非等式聯(lián)結(jié)。
笛卡爾聯(lián)結(jié)
笛卡兒聯(lián)結(jié)發(fā)生在當(dāng)一張表中的所有行與另一張表的所有行聯(lián)結(jié)時(shí)，這種聯(lián)結(jié)所得到的結(jié)果集的總行為等于一張表中的數(shù)據(jù)行乖以另一張表中的數(shù)據(jù)行數(shù)。Ａ Ｘ?。隆。健〗Y(jié)果集的總行數(shù)。
笛卡爾聯(lián)結(jié)
SQL> set autotrace traceonly;
SQL> select empno, ename,dname, loc from dept,emp;
已選擇36行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 2304 | 10 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 36 | 2304 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 136 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 9 | 270 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 9 | 270 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
外聯(lián)結(jié)
外聯(lián)結(jié)返回一張表的所有行以及另一張表中滿足聯(lián)結(jié)條件的行數(shù)據(jù)。oracle使用+字符來表明進(jìn)行外聯(lián)結(jié)。+號(hào)放在一對(duì)圓括號(hào)中，位于只有匹配才會(huì)返回?cái)?shù)據(jù)行的表聯(lián)結(jié)條件旁。
外聯(lián)結(jié)
select * from orders;
select * from customers;
--增加一些沒有定單的客戶
insert into customers values(6,'M','Li','Lei',3);
insert into customers values(6,'M','Jiang','tao',3);
insert into customers values(6,'F','Lili','cao',3);
commit;
--對(duì)下單數(shù)５０００到１００００的顧客進(jìn)行統(tǒng)計(jì)
select c.cust_last_name, nvl(sum(o.order_total), 0) tot_orders
from customers c, orders o
where c.customer_id = o.customer_id
group by c.cust_last_name
having nvl(sum(o.order_total), 0) between 5000 and 100000
order by c.cust_last_name;
--計(jì)算一個(gè)總數(shù)
select count(*)
from (select c.cust_last_name, nvl(sum(o.order_total), 0) tot_orders
from customers c, orders o
where c.customer_id = o.customer_id
group by c.cust_last_name
having nvl(sum(o.order_total), 0) between 0 and 100000
order by c.cust_last_name);
--改變成外聯(lián)結(jié)，包括沒有定單的客戶
select count(*)
from (select c.cust_last_name, nvl(sum(o.order_total), 0) tot_orders
from customers c, orders o
where c.customer_id = o.customer_id(+)
group by c.cust_last_name
having nvl(sum(o.order_total), 0) between 0 and 100000
order by c.cust_last_name);
SQL> set autotrace traceonly;
SQL> select count(*)
2 from (select c.cust_last_name, nvl(sum(o.order_total), 0) tot_orders
3 from customers c, orders o
4 where c.customer_id = o.customer_id(+)
5 group by c.cust_last_name
6 having nvl(sum(o.order_total), 0) between 0 and 100000
7 order by c.cust_last_name);
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2112959513
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 1 | | 8 (25)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | HASH GROUP BY | | 1 | 15 | 8 (25)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 24 | 360 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL| CUSTOMERS | 5 | 40 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| ORDERS | 24 | 168 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL(SUM("O"."ORDER_TOTAL"),0)>=0 AND
NVL(SUM("O"."ORDER_TOTAL"),0)<=100000)
5 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID"(+))
**使用ANSI聯(lián)結(jié)語法的外聯(lián)結(jié) **
--使用ANSI聯(lián)結(jié)語法的外聯(lián)結(jié)
select count(*)
from (select c.cust_last_name, nvl(sum(o.order_total), 0) tot_orders
from customers c
left outer join orders o
on (c.customer_id = o.customer_id)
group by c.cust_last_name
having nvl(sum(o.order_total), 0) between 0 and 100000
order by c.cust_last_name);
在ANSI語法中，只需要使用關(guān)鍵字left outer join即可，這表明左側(cè)的表是你需要的，即使沒有滿足聯(lián)結(jié)條件的數(shù)據(jù)行也要將所有行包含在結(jié)果集中的表。如果你想即使在customers表中沒有對(duì)應(yīng)的匹配，也要返回orders表中的所有數(shù)據(jù)行，可以用right outer join。
Ｏracle外聯(lián)結(jié)語法的另一個(gè)局限性在于它不支持全外聯(lián)結(jié)。全外聯(lián)結(jié)從左到右以及從右到左對(duì)兩個(gè)表進(jìn)行聯(lián)結(jié)。兩個(gè)聯(lián)結(jié)方向所得到的結(jié)果只輸出一次以避免重復(fù)。
使用ANSI聯(lián)結(jié)語法的全外聯(lián)結(jié)
select * from emp;
create table e1 as select * from emp where empno in (1,4);
create table e2 as select * from emp where empno in (2,6);
select * from e1;
select * from e2;
select e1.ename,e1.deptno,e1.job,
e2.ename,e2.deptno,e2.job
from e1 full outer join
e2 on (e1.empno=e2.empno);
兩張表中的數(shù)據(jù)行即使在相對(duì)的表中沒有匹配項(xiàng)也都出現(xiàn)在輸出結(jié)果中。這就是全外聯(lián)結(jié)所能做的事情，在部分?jǐn)?shù)據(jù)集需要進(jìn)行聯(lián)結(jié)的時(shí)候是很有用的。
全外聯(lián)結(jié)功能的oracle等價(jià)語法
select e1.ename, e1.deptno, e1.job, e2.ename, e2.deptno, e2.job
from e1, e2
where e1.empno(+) = e2.empno
union all
select e1.ename, e1.deptno, e1.job, e2.ename, e2.deptno, e2.job
from e1, e2
where e1.empno = e2.empno(+)
and e2.rowid is null;
