準(zhǔn)備測(cè)試數(shù)據(jù)
drop table job_history;
create table job_history(
employee_id number not null,
start_date date,
end_date date,
ename varchar2(30),
job varchar2(30),
job_id number
);
delete from job_history;
insert into job_history values(107,to_date('2014-07-07','yyyy-mm-dd'),to_date('2015-07-07','yyyy-mm-dd'),'polly','sales',1);
insert into job_history values(107,to_date('2015-07-07','yyyy-mm-dd'),to_date('2018-07-07','yyyy-mm-dd'),'polly','finance',2);
insert into job_history values(104,to_date('2014-06-07','yyyy-mm-dd'),to_date('2015-06-07','yyyy-mm-dd'),'hanmeimei','secrectory',3);
insert into job_history values(104,to_date('2015-06-07','yyyy-mm-dd'),to_date('2017-06-07','yyyy-mm-dd'),'hanmeimei','finance',2);
insert into job_history values(105,to_date('1985-06-07','yyyy-mm-dd'),to_date('1987-02-07','yyyy-mm-dd'),'litao','engineer',4);
insert into job_history values(105,to_date('1987-06-07','yyyy-mm-dd'),to_date('2000-02-07','yyyy-mm-dd'),'litao','operations person',5);
insert into job_history values(4,to_date('2003-06-07','yyyy-mm-dd'),to_date('2006-04-07','yyyy-mm-dd'),'lilei','manager',6);
insert into job_history values(11,to_date('2006-08-07','yyyy-mm-dd'),to_date('2009-04-07','yyyy-mm-dd'),'lilei','ceo',7);
insert into job_history values(5,to_date('1999-01-07','yyyy-mm-dd'),to_date('1999-08-07','yyyy-mm-dd'),'uncle wang','cto',8);
insert into job_history values(12,to_date('2009-05-07','yyyy-mm-dd'),to_date('2010-08-07','yyyy-mm-dd'),'uncle wang','sales',9);
insert into job_history values(6,to_date('2001-07-07','yyyy-mm-dd'),to_date('2005-06-07','yyyy-mm-dd'),'jim','police',10);
insert into job_history values(13,to_date('2005-07-07','yyyy-mm-dd'),to_date('2009-10-07','yyyy-mm-dd'),'jim','sales',1);
insert into job_history values(7,to_date('2004-09-07','yyyy-mm-dd'),to_date('2008-06-07','yyyy-mm-dd'),'green','waiter',11);
insert into job_history values(14,to_date('2008-09-07','yyyy-mm-dd'),to_date('2015-06-07','yyyy-mm-dd'),'green','waiter',11);
insert into job_history values(1, to_date('2016-02-03', 'yyyy-mm-dd'), to_date('2016-09-03', 'yyyy-mm-dd'), 'lilei','reporter',12);
insert into job_history values(105, to_date('2016-02-03', 'yyyy-mm-dd'), to_date('2016-09-03', 'yyyy-mm-dd'), 'litao', 'reporter',12);
insert into job_history values (104, to_date('2016-02-03', 'yyyy-mm-dd'), to_date('2016-09-03', 'yyyy-mm-dd'),'hanmeimei', 'reporter',12);
insert into job_history values (104, to_date('2016-11-03', 'yyyy-mm-dd'), to_date('2017-10-03', 'yyyy-mm-dd'), 'hanmeimei', 'model',13);
列出干過(guò)多種崗位的員工
--列出干過(guò)多種崗位的員工
--desc job_history;
--向表中添加主鍵約束
--alter table job_history add constraint SYS_C0010295 primary key(employee_id);
--查找表中主鍵名稱得job_history表中的主鍵名為SYS_C0010295
--SELECT * from user_cons_columns;
--alter table job_history drop constraint SYS_C0010295;
--alter table job_history modify (employee_id null);
select employee_id,count(*) job_ct
from job_history
group by employee_id
having count(*) > 1;
上面的列子是錯(cuò)的坎缭，為了了解提問(wèn)者的真正業(yè)務(wù)别瞭，應(yīng)該問(wèn)清楚業(yè)務(wù)的方式面面脊僚，而不是用一個(gè)歷史記錄表統(tǒng)計(jì)拣宰。
- 在查詢中將員工將當(dāng)前的工作崗位列入計(jì)數(shù)還是只才考慮當(dāng)前工作崗位以外的崗位轩端？
- 能滿足需求的數(shù)據(jù)放在哪兒放典，即在一張表還是多張表數(shù)據(jù)中？
- 數(shù)據(jù)模型是什么的基茵，如果有的話奋构，我能否到到一份數(shù)據(jù)字典或?qū)嶓w關(guān)系圖(entity relationship diagram ERD)的副本？
- 結(jié)果集有預(yù)期的結(jié)果尺寸嗎拱层？
- 數(shù)據(jù)是何存儲(chǔ)的弥臼？
- 這個(gè)查詢執(zhí)行的頻率如何？
其實(shí)上面是用工作歷史表來(lái)查詢的根灯，并不準(zhǔn)確径缅。
重寫(xiě)的工作位查詢
select employee_id, count(*) job_ct
from (select e.employee_id, e.job_id
from employees e
union all
select j.employee_id, j.job_id from job_history j)
group by employee_id
having count(*) > 1;
數(shù)據(jù)的問(wèn)題
獲得完成任務(wù)的業(yè)務(wù)，接下來(lái)考慮的是如何來(lái)發(fā)現(xiàn)數(shù)據(jù)的存儲(chǔ)方式呢烙肺？首先試著試著像優(yōu)化器一樣思考問(wèn)題纳猪。優(yōu)化器需要統(tǒng)計(jì)信息和實(shí)例參數(shù)值來(lái)計(jì)算計(jì)劃。
- 為了得到所需的全部數(shù)據(jù)都需要那些表茬高？
- 其中有表是分區(qū)的嗎?如果有兆旬，分區(qū)是如何定義的呢？
- 每張表都有那些列怎栽？
- 每張表的索引都有那些丽猬？
- 每張表及其中的列和索引的統(tǒng)計(jì)信息都是什么宿饱？
- 某些列上有直方圖信息嗎？
統(tǒng)計(jì)信息為優(yōu)化器描繪出了各種訪問(wèn)和聯(lián)結(jié)數(shù)據(jù)的方法是如何實(shí)現(xiàn)的藍(lán)圖脚祟。
得到統(tǒng)計(jì)信息后谬以，你可以使用這些信息來(lái)提問(wèn)，以及回答關(guān)于你期望優(yōu)化器對(duì)你的SQL做什么的問(wèn)題由桌。如索引統(tǒng)計(jì)信息叫聚族因子为黎，這個(gè)統(tǒng)計(jì)信息幫助優(yōu)化器計(jì)算將會(huì)訪問(wèn)多少個(gè)數(shù)據(jù)塊⌒心基本上聚族因子與表中的數(shù)據(jù)塊數(shù)目越接近铭乾，使用這個(gè)索引時(shí)所需要訪問(wèn)數(shù)據(jù)塊數(shù)目的估計(jì)值就越少，聚族因子與表中的數(shù)據(jù)行數(shù)目越接近娃循，所需要訪問(wèn)的數(shù)據(jù)塊數(shù)目的估計(jì)值就越大炕檩。訪問(wèn)的數(shù)據(jù)塊數(shù)目越少，使用這個(gè)索引的成本就越小捌斧，優(yōu)化器也就越可能為執(zhí)行計(jì)劃選擇這個(gè)索引笛质。
--索引聚族因子
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 = 'SALES'
order by t.TABLE_NAME, i.index_name;
除了統(tǒng)計(jì)信息以外，可以實(shí)際執(zhí)行一次查詢來(lái)得到數(shù)據(jù)的相關(guān)情況以及在這個(gè)表中需要訪問(wèn)和返回多少行數(shù)據(jù)捞蚂。不管語(yǔ)句有多復(fù)雜妇押，你可以做優(yōu)化器會(huì)做的事情并將語(yǔ)句分解為單表訪問(wèn)。對(duì)于所包括的每張表姓迅，只需執(zhí)行一個(gè)或多個(gè)查詢來(lái)計(jì)算并查看使用SQL語(yǔ)句所要使用的篩選條件會(huì)返回多少行數(shù)據(jù)敲霍。
建立邏輯表達(dá)式
通常對(duì)于同一個(gè)謂語(yǔ)邏輯可能多種表述方法。學(xué)習(xí)一些好的布爾邏輯技術(shù)队贱，從而可以不用只依賴于某一種方法來(lái)表達(dá)條件邏輯色冀。布爾邏輯表達(dá)式總是可以做出最高效的執(zhí)行計(jì)劃運(yùn)算選擇，一定要對(duì)其他替代方法進(jìn)行徹低的測(cè)試柱嫌，但最好還是要知道如何構(gòu)造不同的替代語(yǔ)句锋恬，這樣可以不必局限于某一種方法。
--表達(dá)條件邏輯的不同方法
variable empno number;
variable getall number;
exec :empno := 2;
exec :getall :=1;
select /* opt1 */
empno, ename
from emp
where empno = case
when :getall <> 1 then
:empno
else
empno
end;
--使用綁定變量創(chuàng)建where子句
variable getall number;
variable empno number;
exec :empno := 1;
exec :getall := 0;
select /* opt2 */ empno,ename from emp
where empno = nvl(:empno,empno);
select /* opt3 */ empno,ename from emp
where (:empno is null) or (:empno = empno);
在上面的例子中编丘，布爾邏輯不能為你提供最好的執(zhí)行計(jì)劃与学。因此知道幾種謂語(yǔ)構(gòu)造的可替代方式以獲得可能的最佳執(zhí)行計(jì)劃是很好的。
--使用綁定變量創(chuàng)建where子句
variable getall number;
variable empno number;
exec :empno := 1;
exec :getall := 0;
--使用用union all處理?xiàng)l件邏輯
select /* opt5 */ empno,ename from emp
where :empno is null
union all
select empno,ename from emp
where :empno = empno;
與串聯(lián)執(zhí)行計(jì)劃類(lèi)似嘉抓，在上面的例子中索守，一個(gè)由兩部分單獨(dú)的子計(jì)劃合并到一起來(lái)得到結(jié)果的執(zhí)行計(jì)劃。如果變量為空值抑片，就將進(jìn)行全掃描卵佛，并且會(huì)返回所有行。當(dāng)綁定變量不為空時(shí)，將會(huì)進(jìn)行索引唯一掃描并僅返回所需要那一行截汪。
通常優(yōu)化器使用and條件時(shí)能夠更好的選擇執(zhí)行計(jì)劃疾牲，這是因?yàn)閛r條件的使用意味著基于表達(dá)式的求解方式可能會(huì)使用兩種不同的運(yùn)算。而在and條件中衙解，更可能只有一個(gè)選擇阳柔，或者至少只會(huì)考慮本質(zhì)上不是相對(duì)立的選擇。
如果在一個(gè)很大的代碼體內(nèi)部寫(xiě)SQL代碼蚓峦，如果存儲(chǔ)過(guò)程舌剂，盡量在編碼語(yǔ)言中使用條件結(jié)構(gòu)而不是將邏輯放在SQL中。sql語(yǔ)句越簡(jiǎn)單暑椰，在語(yǔ)句中需要直接進(jìn)行處理的條件越少霍转，優(yōu)化器確定最優(yōu)執(zhí)行計(jì)劃時(shí)的復(fù)雜性也就是越小。
根據(jù)業(yè)務(wù)干茉，寫(xiě)出功能上正確并且性能最優(yōu)的sql谴忧，我們要對(duì)自己的代碼多了解業(yè)務(wù)的背景很泊，涉及的表角虫，字段，索引委造，加深對(duì)數(shù)據(jù)的理解戳鹅，有助于幫助我們寫(xiě)出高質(zhì)量高性能的SQL。問(wèn)好問(wèn)題的能力是一個(gè)非常具有智慧的習(xí)慣昏兆，必須長(zhǎng)時(shí)間才能養(yǎng)成枫虏。
