N I DUNG C A BU I H CỘ Ủ Ổ Ọ
Phân tích các hàm c b n trong Oracle và nh ng ví d đ n gi n gi i thích, gi i thi uơ ả ữ ụ ơ ả ả ớ ệ
v hàm trong Oracleề
- Các hàm t p h p t đ ng: ậ ợ ự ộ Rollup, cube
- Hàm x p h ng:ế ạ rank, dense_rank, row_number
- Hàm Lag, lead
- Hàm First, last
- V n hành Oracle th c hi n b ng cách th 1: Vi t câu truy v n SQL ch t l ngậ ự ệ ằ ứ ế ấ ấ ượ
cao
- V n hành Oracle th c hi n b ng cách th 2: S d ng Index thi t l p ch ng trìnhậ ự ệ ằ ứ ử ụ ế ậ ươ
có hi u qu caoệ ả
I/ Phân tích các hàm t p h p t đ ng: Rollup và cubeậ ợ ự ộ
S d ng ví d d i đây đ tính t ng các nguyên li u b ng phát bi u SQL thôngử ụ ụ ướ ể ổ ệ ằ ể
th ng hi n th cho m i vùng(Area) ngay l p t c: ườ ể ị ỗ ậ ứ
select area_code,
sum(local_fare) local_fare
from t
group by area_code
union all
select ‘TOTAL' area_code,
sum(local_fare) local_fare
from t;
1/ Cũng v i k t qu đó nh ng s d ng v i câu l nh rollup:ớ ế ả ư ử ụ ớ ệ
select nvl(area_Code, ' total ' ) area_code,
sum(local_fare) local_fare
from t
group by rollup(nvl(area_Code, ' total '));
S d ng hàm Rollup, phát bi u Sql đ n gi n h n, có h th ng h n, tài nguyên tiêuử ụ ể ơ ả ơ ệ ố ơ
th là r t th p so v i s d ng câu truy v n thông th ng.ụ ấ ấ ớ ử ụ ấ ườ
2/ S d ng ví d tính t ng v t li u v i hàm cube:ử ụ ụ ổ ậ ệ ớ
select area_code,
bill_month,
sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,
bill_month nulls last;
S d ng hàm s grouping phân bi t các tr ng t p h p. ử ụ ố ệ ườ ậ ợ n u nh t ng hi n t iế ư ổ ệ ạ
là căn c vào c t đó đ tính toán thì grouping s tr v 1 ng c l i là 0ứ ộ ể ẽ ả ề ượ ạ
select decode(grouping(area_code),1,'all area', to_char(area_code)) area_code,
decode(grouping(bil _month),1,'all month', bill_month) bill_month,
sum(local_fare) local_fare
from t
group by cube(area_code,bill_month)
order by area_code,bill_month nulls last;
II/ Phân tích hàm: Rank, dense_rank,row_Number function
1/ Hàm Rank
select area_code,
sum(local_fare) local_fare,
rank() over (order by sum(local_fare) desc) fare_rank
from t
group by area_code;
Hàm s x p h ng cho c t:ẽ ế ạ ộ local_fare giá tr x p h ng đ c đ a vào c tị ế ạ ượ ư ộ
fare_rank
Giá tr l n nh t s có h ng bé nh t, d li u trùng thì đ ng h ng, h ng tí p theo là vị ớ ấ ẽ ạ ấ ữ ệ ồ ạ ạ ế ị
trí c a nó trong b ng x p h ng(Không l y v trí k ti p c a h ng trùng tr c nó). ủ ả ế ạ ấ ị ế ế ủ ạ ướ
Area_code Local_fare Fare_rank
A1 100 1
A2 100 1
A3 101 3
2/ Hàm dense_rank
select area_code,
sum(local_fare) local_fare,
dense_rank() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;
Hàm s x p h ng cho c t:ẽ ế ạ ộ local_fare giá tr x p h ng đ c đ a vào c t ị ế ạ ượ ư ộ fare_rank
Giá tr l n nh t s có h ng bé nh t, d li u trùng thì đ ng h ng, h ng tí p theo làị ớ ấ ẽ ạ ấ ữ ệ ồ ạ ạ ế
h ng k ti p c a h ng trùng tr c đóạ ế ế ủ ạ ướ
Area_code Local_fare Fare_rank
A1 100 1
A2 100 1
A3 101 2
3/ Hàm row_number
select area_code,
sum(local_fare) local_fare,
row_number() over (order by sum(local_fare) desc ) fare_rank
from t
group by area_code;
Hàm s x p h ng cho c t:ẽ ế ạ ộ local_fare giá tr x p h ng đ c đ a vào c t ị ế ạ ượ ư ộ fare_rank
Giá tr l n nh t s có h ng bé nh t, d li u trùng không đ ng h ng, h ng = ch s c aị ớ ấ ẽ ạ ấ ữ ệ ồ ạ ạ ỉ ố ủ
hàng d li uữ ệ
Area_code Local_fare Fare_rank
A1 100 1
A2 100 2
A3 101 3
II/ Phân tích hàm: lag, lead
Hàm Lag và Lead phán đoán n tháng tr c, sau nó hi n th trong câu truy v n.ướ ể ị ấ
select area_code,
bill_month,
local_fare cur_local_fare,
lag(local_fare,2,0) over (partition by area_code order by bill_month)
pre_local_fare,
lag(local_fare,1,0) over (partition by area_code order by bill_month )
last_local_fare,
lead(local_fare,1,0) over (partition by area_code order by bill_month )
next_local_fare,
lead(local_fare,2,0) over (partition by area_code order by bill_month )
post_local_fare
from ( select area_code,
bill_month,
sum(local_fare) local_fare
from t
group by area_code,bill_month );
lag(local_fare,2,0): Tr c đó 2 thángướ
lag(local_fare,1,0): Tr c đó 1 thángướ
lead(local_fare,1,0): Sau đó 1 tháng
lead(local_fare,2,0): Sau đó 2 tháng
III/ Phân tích hàm s : Sum, Avg, Min, Maxố
select area_code,bill_month, local_fare,
sum(local_fare) over ( partition by area_code
order by to_number(bill_month)
range between 1 preceding and 1 following ) "3month_sum",
avg(local_fare) over ( partition by area_code
order by to_number(bill_month)
range between 1 preceding and 1 following ) "3month_avg",
max(local_fare) over ( partition by area_code
order by to_number(bill_month)
range between 1 preceding and 1 following ) "3month_max",
min(local_fare) over ( partition by area_code
order by to_number(bill_month)
range between 1 preceding and 1 following ) "3month_min"
from ( select area_code,
bill_month,
sum(local_fare) local_fare
from t
group by area_code, bill_month );
Ví d phía trên, tìm ra giá tr l n nh t, nh nh t, trung bình c a 3 tháng tính t thángụ ị ớ ấ ỏ ấ ủ ừ
hi n t i.ệ ạ
IV/ Phân tích hàm first, last
L y ra giá tr đ u và giá tr cu iấ ị ầ ị ố
select bill_month,
area_code,
sum(local_fare) local_fare,
first_value(area_code) over (order by sum(local_fare) desc rows unbounded
preceding) firstval,
first_value(area_code) over (order by sum(local_fare) asc rows unbounded
preceding) lastval
from t
group by bill_month,
area_code
order by bill_month;
V/ V n hành Oracle th c hi n b ng cách th 1: Vi t câu truy v n SQL ch tậ ự ệ ằ ứ ế ấ ấ
l ng caoượ
- S hi u qu l n c a câu truy v n SQL d a vào trình t vi t câu SQL. Oracleự ệ ả ớ ủ ấ ự ự ế
s phân tích câu truy v n theo m t quy t c tên. ẽ ấ ộ ắ
- M nh đ FROM s đ c hi u theo v trí t ph i qua tráiệ ề ẽ ượ ể ị ừ ả
- V trí k t n i trong m nh đ WHERE: Oracle ch n ra v trí t d i lên trênị ế ố ệ ề ọ ị ừ ướ
phân tích m nh đ WHERE. Đi u ki n đ l c ra d li u ph i đ c vi t cu i m nhệ ế ề ệ ể ọ ữ ệ ả ượ ế ố ệ
đ WHEREề
- Tránh s d ng trong m nh đ SELECT d u ‘*’: Oracle s b t đ u vào bênử ụ ệ ề ấ ẽ ắ ầ
trong tìm tên t t c các hàng trong su t quá trình tìm ki m, phân tích d li u. Quá trìnhấ ả ố ế ữ ệ
này s làm t n r t nhi u th i gian.ẽ ố ấ ề ờ
- C g ng đánh giá h n ch các giá tr NULL trong m nh đ WHERE, N uố ắ ạ ế ị ệ ề ế
không Index s ng ng s d ng, và không th Scan. Khi đánh giá m t tr ng d li u làẽ ư ử ụ ể ộ ườ ữ ệ
0 b ng câu truy v n: select id from t where num is null . Khi đó ta đ m c đ nh ằ ấ ể ặ ị
select id from t where num =0
- X lý câu truy v n SQL: Oracle luôn phân tích phát bi u SQL chuy n toàn bử ấ ể ể ộ
thành ký t hoa đ a vào x lý ự ư ử
- C g ng tránh dùng các toán t ‘/’ trong m nh đ WHERE, vì nó là lý do màố ắ ử ệ ề
Index không s d ng đ c và Scan toàn b table làm chu ng trình ch y ch m.ử ụ ượ ộ ơ ạ ậ
Thay vì select id from t where num/2=100 chuy n thành ể
select id from t where num=100*2.
- Không ghi các phép toán s h c, công th c bên trái c a toán t ‘=’ n u khôngố ọ ứ ủ ử ế
h th ng không th s d ng đ c Index.ệ ố ể ử ụ ượ
VI/ V n hành Oracle th c hi n b ng cách th 2: S d ng Index thi t l pậ ự ệ ằ ứ ử ụ ế ậ
ch ng trình có hi u qu caoươ ệ ả
- Thay th IN v i EXISTS, thay th NOT IN v i NOT EXISTSế ớ ế ớ
- Phát bi u d i đây có hi u qu caoể ướ ệ ả
SELECT * FROM EMP (base table)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’ FROM DEPT WHERE
DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
- Thay th OR v i UNION(S d ng Index đ c s p x p)ế ớ ử ụ ượ ắ ế
SELECT * FROM EMP (base table) WHERE EMPNO > 0 AND DEPTNO
IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
- Tránh s d ng các phép toán làm t n tài nguyên và th i gian: DISTINCT , ORDERử ụ ố ờ
BY
- Tránh thay đ i ki u s p x p c a Index: ổ ể ắ ế ủ
oGi s EMPNO là ki u s p x p Index là ki u Numberả ử ể ắ ế ể
oKhi ta s d ng câu truy v n: ử ụ ấ SELECT … FROM EMP WHERE EMPNO =
‘123'
oT i th i đi m runtime s tr v cú pháp:ạ ờ ể ẽ ả ề
SELECT … FROM EMP WHERE to_number(EMPNO) = 123
-
- Không ph i t t c các Index đ u mang l i k t qu nh mong mu n. SQL đánh giáả ấ ả ề ạ ế ả ư ố
thông tin theo cách đ n d li u trong m t hình th c, khi s lu ng quy mô c a dế ữ ệ ộ ứ ố ợ ủ ữ
li u đ c l p l i trong hình th c đó. SQL không đ m nh đ l y thông tin. N uệ ượ ặ ạ ứ ủ ạ ể ấ ế
có các ph n: Sex, male, female g n tách bi t m t n a, vi c xây d ng Index l yầ ầ ệ ộ ử ệ ự ấ
thông tin đó hi u qu là không th làm vi c v i Sex.ệ ả ể ệ ớ
- Tránh s d ng IS NULL trên Index, IS NOT NULL ử ụ
- Example:
SELECT ¡ FROM A WHERE code IS NOT NULL;
Hi u qu cao: ệ ả SELECT ¡ FROM A WHERE CODE > =0;
- Oracle s d ng Index và lý do hi n nhiên d n đ n s kém hi u qu c a s d ngử ụ ể ẫ ế ự ệ ả ủ ử ụ
sai nó . Chúng ta c n s d ng t t c các k năng h p lý tránh xa Index, không lênầ ử ụ ấ ả ỹ ợ
s d ng.ử ụ
- Ki m tra: Ta có các ph n: a, b, c, d, trong a, b cái đ t tr thành Index trên ể ầ ặ ở c_a(a,b,c)
. Cài đ t Index inx_a, b_b(b). Trong tr ng h p c b n, where a = ? and b=? and cặ ườ ợ ơ ả
= ? chúng ta s s d ng Index inx_a where b =? S s d ng Index inx_b , tuy nhiênẽ ử ụ ẽ ử ụ
where a =? and b =? and c =? Cái index broup by b đ s d ng hay không?ể ử ụ
- Oracle s m s d ng inx_b, xuyên su t qúa trình phân tích và x lý s d ngẽ ở ử ụ ố ử ử ụ
Index s phá hu và m t th i gian r t nhi u.ẽ ỷ ấ ờ ấ ề