เรียนรู้ Trick การ Query ด้วย SQL

posted on 14 Oct 2008 20:33 by nhephex in Knowledge

แปลเค้ามาอีกทีนะครับ สำหรับคนเล่น Oracle ครับ ลองดู

ว่ากันว่า เขียน SQL ให้ Optimize กิน resource น้อย ยากกว่าเขียนให้ทำงานถูกต้องตามที่เราต้องการมากมายนัก -_-“

1. ตัวอักขระที่ใช้ใน Select statement ต้องเปี๊ยะ (ตัวใหญ่ตัวเล็กมีผล ถ้าจะเอามาใช้ซ้ำได้ ต้องเหมือนกัน)

Example: สอง query นี่ ไม่เหมือนกัน!

SELECT STUDENT_NUMBER,NAMEFROM STUDENTWHERE STUDEN_NUMBER = ‘0220’

Select Student_Number,NameFrom StudentWhere Student_Number = ‘0220’

2. ใช้ Bind Variable

อย่าใช้

SELECT FIRST_NAME, LAST_NAME

FROM Client

WHERE CLIENT_NUM = 1200

แต่ให้ใช้

SELECT FIRST_NAME, LAST_NAME

FROM Client

WHERE CLIENT_NUM = :Client_Num

การเขียนแบบล่าง ไม่ต้อง สร้าง cursor ใหม่ ถ้า bind variable เปลี่ยน

เพราะ ชื่อของ variable ไม่เปลี่ยน จากการ execution จากก่อนหน้าไปอันล่าสุด

3. ใช้ rowid ถ้าทำได้ เพราะมันจะไม่เปลี่ยนจนกระทั่งเจอ delete statement ดังนั้น ใช้มันเพื่อให้เร็ว

cursor accounts_cur is

select acct_no,

currency,

branch

Rowid acct_rowid,

…

From account

where . . . .

…

for acct_rec in accounts_cur loop

…

update account set …

…

where rowid = acct_rec.acct_rowid;

…

end loop;

4. อย่าใช้ having เพราะมันจะต้อง fetch ดูจนครบก่อนถึงทำ

อย่าใช้

select *

from account

where cust_Active_flag = ‘y’

having group = ‘001’แต่ใช้นี่แทน

select *

from account

where cust_Active_flag = ‘y’

and group = ‘001’ 5. ใช้ union all แทน union เพราะ union all จะเอาหมด (dup ก็เอา) และไม่ sort ซึ่งไม่เปลืองพลัง ยกเว้น ถ้าต้องการไม่ให้มี dup คงต้องใช้ union ธรรมดา

6. ใช้ not exists แทน not in สำหรับกรณี column ที่เป็น indexed เพราะ not in จะทำให้เกิด internal sort/merge

อย่าใช้

select * from Student

where STUDENT_NUM not in

(select STUDENT_NUM from CLASS)

ให้ใช้นี่แทน

select * from STUDENT C

where not exists

(select 1 from CLASS A where A.STUDENT_NUM = C.STUDENT_NUM)

7. ใช้ minus แทน not in สำหรับ กรณี column ที่ ไม่เป็น index เพราะ not in จะทำให้เกิด internal sort/merge

อย่าใช้

select * from system_user

where su_user_id not in

(select ac_user from account)

ให้ใช้นี่แทน

select * from system_user

where su_user_id in

(select su_user_id from system_user

minus

select ac_user from account)

8. ใช้ join แทน exists สำหรับ unique scan index และ table ที่เล็กๆ

อย่าใช้

select acct_ID, currency, branch

from account

where exists (select 1 from branch where code = branch and def_curr = '001')

ให้ใช้ join 2 table ดีกว่า

select acct_ID,currency, branch

from account A, branch B

where b.code = A.branch

and A.def_curr = '001'

9. ใช้ Hints อันนี้ไม่ค่อยเข้าใจ ดูตัวเต็มแล้วกัน -_-“

w Hints are special instructions to Optimizer. You can change the Optimization goal for an individual statement by using Hint. Some commonly used Hints are: CHOOSE, RULE, FULL(table_name), INDEX(table_name index_name), USE_NL, USE_HASH(table_name), PARALLEL(table_name parallelism) etc.

SELECT /*+RULE*/ NAME,

ACCT_ALLOCATION_PERCENTAGE

FROM ACCOUNTS WHERE ACCOUNT_ID = 1200

w The above SQL statement will be processed using the RULE based optimizer.

SELECT /*+ INDEX(A, ACCT_ID_IND) */ NAME,

ACCT_ALLOCATION_PERCENTAGE

FROM ACCOUNTS A

WHERE ACCOUNT_ID = :ACCT_ID AND CLIENT_ID= :CLIENT_ID

w In the above SQL statement, an Index Hint has been used to force the use of a particular index.

10. การสร้าง Index เพื่อทำให้เร็วขึ้น

- Index ช่วยให้เร็วจริง แต่ก็ทำให้เสียเหมือนกัน เพราะตอน insert / update / delete มันก็ต้องทำด้วย (ทำให้ ช้าลง)

- การจะใช้ index ให้ดีนั้น ขึ้นอยู่กับการเลือก column ด้วย

- ปกติ การเลือกจะใช้เวลานาน ถ้าค่าใน column เป็นค่าที่แตกต่างกันเยอะๆ

- การที่มี column ที่เป็น index มากกว่า 1 column เรียกว่า concatenated index

- concatenated index MORE selective กว่า single

- ตำแหน่งของ column มีความสำคัญสำหรับ concatenated index ถ้าจะใช้ ให้ใช้คอลัมน์ที่เป็นตัวนำ (Leading Column)

11. ใช้ like ดีกว่าใช้ calculation ในกรณี field ที่เป็น index เช่น

Select * from

Account

Where substr(ac_acct_no,1,1) = ‘1’

Instead use -

Select * from

Account

Where ac_acct_no like ‘1%’

12. หลีกเลี่ยงการใช้ NOT ใน column ที่เป็น index เช่น

w For example the following select statement will never use the index on STUDENT_NUM column

Select * from

student

Where STUDENT_NUM not like ‘9%’

w เพราะ Remember, indexes are built on what is in a table, but not what is NOT in a table.

13. ใช้ UNION แทน OR เพราะการใช้ OR กับ column ที่เป็น index จะทำให้เกิดการ scan ทั้ง table แทนที่จะไปใช้ index

14. ตำแหน่งของการวาง Join ใน Where Cause มีผล! ควรวางไว้นำหน้า

SELECT . . . .

FROM EMP E

WHERE

SAL > 50000

AND JOB = ‘CLERK’

AND 25 < (SELECT COUNT(*)

FROM EMP WHERE MGR = E.EMPNO);

ควรแก้เป็น

SELECT . . . .

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO )

AND SAL > 50000 AND JOB = ‘CLERK’;

15. ORACLE parser always processes table names from right to left, so the table name you specify last (driving table) is actually the first table processed. มันจะ scan table ที่อยู่ท้ายสุดก่อน (ดังนั้นท้ายสุดควรเป็น table ที่ record น้อยสุด)

Table TABA has 16,384 rows.

Table TABB has 1 row.

SELECT COUNT(*) FROM TABA, TABB 0.96 seconds elapsed SELECT COUNT(*) FROM TABB, TABA 26.09 seconds elapsed

16. กรณี Join 3 Table ให้เลือก Table ที่ Link มากสุดไว้หลังสุด

เช่น ควรใช้

SELECT . . .

FROM LOCATION L, CATEGORY C, EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

มากกว่า ที่จะใช้

SELECT . . .

FROM EMP E,

LOCATION L, CATEGORY C

WHERE E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

17. การใช้ DECODE ช่วยในการป้องกันไม่ให้ scan ไปที่ row เดิม หรือ join table เดิม

SELECT COUNT(*), SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE ‘SMITH%’;

SELECT COUNT(*), SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE ‘SMITH%’;

จะแทนด้วย

SELECT COUNT(DECODE(DEPT_NO,0020, ‘X’, NULL))

D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,‘X’,NULL))

D0030_COUNT,

SUM(DECODE(DEPT_NO,0020, SAL, NULL)) D0020_SAL, SUM(DECODE(DEPT_NO, 0030, SAL, NULL))

D0030_SAL

FROM EMP

WHERE ENAME LIKE ‘SMITH%’;

18. ยุบรวมวิธีการ select table ให้เหลือน้อยๆ

เช่น

SELECT TAB_NAME

FROM TABLES

WHERE

TAB_NAME =

(SELECT TAB_NAME

FROM TAB_COLUMNS

WHERE VERSION = 604)

AND

DB_VER = (SELECT DB_VER

FROM TAB_COLUMNS

WHERE VERSION = 604)

ควรแก้เป็น

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME,DB_VER)=

(SELECT TAB_NAME, DB_VER

FROM TAB_COLUMNS

WHERE VERSION = 604)

19. ใช้ EXISTS แทน DISTINCT

เช่น

SELECT DISTINCT DEPT_NO, DEPT_NAME

FROM DEPT D, EMP E

WHERE D.DEPT_NO = E.DEPT_NO

ควรแทนด้วย

SELECT DEPT_NO, DEPT_NAME

FROM DEPT D

WHERE EXISTS (SELECT ‘X’

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

Exists ดีกว่าตรงที่ พอเจอแล้ว 1 ครั้ง ก็จะรับรู้ว่าเจอเลย ทำให้ terminate ได้

20. คำแนะนำอื่นๆ

ไม่ควร	ควร
Select * from Account Where substr(ac_acct_no,1,1) = ‘9’	Select * from Account Where ac_acct_no like ‘9%’
Select * From fin_trxn Where ft_trxn_ref_no != 0	Select * From fin_trxn Where ft_trxn_ref_no > 0
Select * From account Where ac_type \|\| ac_branch = ‘sav001’	Select * From account Where ac_type = ‘sav’ B14 And ac_branch = ‘sav001’
Select * From CLIENT where to_char(CUTT_OFF_TIME,’yyyymmdd’) = to_char(sysdate,’yyyymmdd’)	Select * From CLIENT Where CUT_OFF_DATE >= trunc(sysdate) and CUT_OFF_TIME < trunc(sysdate) + 1
Select * From acct_trxn Where to_char(at_value_date,’yyyymmdd’) > to_char(sysdate,’yyyymmdd’)	Select * From acct_trxn Where at_value_date >= trunc(sysdate) + 1
Select * From acct_trxn Where to_char(at_value_date,’yyyymmdd’) < to_char(sysdate,’yyyymmdd’)	Select * From acct_trxn Where at_value_date < trunc(sysdate)
Select * From acct_trxn Where to_char(at_value_date,’yyyymmdd’) >= to_char(sysdate,’yyyymmdd’)	Select * From acct_trxn Where at_value_date >= trunc(sysdate)
Select * From acct_trxn Where to_char(at_value_date,’yyyymmdd’) <= to_char(sysdate,’yyyymmdd’)	Select * From acct_trxn Where at_value_date < trunc(sysdate) + 1
Select count( *) From BROKER	Select count(PRIMARY_KEY or a non null INDEX column or 1) From Broker

21. ไม่ควรใช้ SELECT * เพราะต้อง Convert ทุกๆ Column ออกมา กินพลังอย่างแรง

22. คำสั่ง AUTOTRACE ของ SQLPLUS จะแสดง Cost ทุกครั้งที่เรา Query อะไรออกมา ทำให้มองเห็นว่าใช้พลังเท่าไหร่

w E.g

SQL> SET AUTOTRACE ON EXPLAIN SQL> SELECT animal_name FROM aquatic_animal ORDER BY animal_name;

ANIMAL_NAME

------------------------------

Batty

Bopper

Flipper

3 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=10 Bytes=170)1 0 SORT (ORDER BY) (Cost=3 Card=10 Bytes=170) 2 1 TABLE ACCESS (FULL) OF ‘AQUATIC_ANIMAL’ (Cost=1 Card=10 Bytes=170)

ถ้าไม่อยากให้แสดงผลออกมาเลย เพียงแต่ให้ดูว่ากินพลังเท่าไหร่ ให้ใช้ EXPLAIN ต่อท้าย

In that case use following :

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

ยกเลิก ใช้

SQL> SET AUTOTRACE OFF

จบแล้วครับ >_< (ไม่เคยรู้มาก่อนเลยหลายๆอัน!)

Tags: database9 Comments