Home » SQL & PL/SQL » SQL & PL/SQL » Order by Clause causes slow query (oracle 8i)
Order by Clause causes slow query [message #384458] |
Tue, 03 February 2009 23:20  |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
I have following tables.
1)SQL> DESC EMP_ATN_03;
Name Null? Type
------------------------------- -------- ----
TRN_IDE NOT NULL NUMBER
BAS_COD NOT NULL NUMBER
ATN_DTE NOT NULL DATE
UNT_COD NOT NULL NUMBER
DPT_COD NOT NULL NUMBER
SFT_COD NOT NULL NUMBER
ATN_TYP NUMBER
INN_TIM DATE
CR_ID NUMBER
CR_DT DATE
AP_ID NUMBER
AP_DT DATE
SQL> DESC EMP_ATN_04;
Name Null? Type
------------------------------- -------- ----
TRN_IDE NOT NULL NUMBER
BAS_COD NOT NULL NUMBER
EMP_COD NOT NULL NUMBER
DSG_COD NUMBER
EMP_ATN NOT NULL CHAR(3)
EMP_LEV CHAR(10)
TOT_ATN NOT NULL CHAR(3)
CHG_DSG NUMBER
CHG_SFT NUMBER
ORD_COD NUMBER
LIN_NUM NUMBER
SRL_NUM NUMBER
SQL> DESC UNT_00_01;
Name Null? Type
------------------------------- -------- ----
UNT_COD NOT NULL NUMBER(4)
BAS_COD NUMBER(4)
UNT_DES VARCHAR2(200)
ACT_FLG NUMBER
SQL> DESC DPT_00_01;
Name Null? Type
------------------------------- -------- ----
DPT_COD NOT NULL NUMBER
DPT_DES VARCHAR2(250)
OLD_COD NUMBER
CST_CNT NUMBER
ACT_FLG NUMBER
SQL> DATE
Trn_Ide,bas_Cod In Emp_Atn_03 Is Primary Key
Trn_Ide,bas_Cod In Emp_Atn_04 Is Foreign Key References Emp_Atn_03(Trn_Ide,bas_Cod)
(Atn_Typ,atn_Dte,unt_Cod,sft_Cod,dpt_Cod) In Emp_Atn_03 Is Unique Key And One Index In Same Order Is Generated By Oralce
Problem is that then i run follwoing query
SELECT TO_CHAR (atn_dte, 'dd') atn_dte, a4.emp_cod, tot_atn, a4.ord_cod,
lin_num, a3.unt_cod, unt_des,
DECODE (sft_cod, 1, 'A', 2, 'B', 3, 'C', 4, 'G', 5, 'R') sft_des,
DECODE (NVL (chg_dsg, 0), 0, 0, 1) chg_dsg, a3.dpt_cod, emp_nam,
emp_fnm, apt_dte, sft_cod, a4.bas_cod, dpt_des, dsg_des,
DECODE(RTRIM(TOT_ATN),'P',1,'G',sal.val_guz_pay(a4.emp_Cod,a4.bas_Cod,a3.atn_Dte,a4.tot_atn),'P/2',0.50,'ML',1,'CL',1,'AL',1,0) Pay_Dys
FROM emp_fil_01 emp_fil,
sal.emp_atn_03 a3,
sal.emp_atn_04 a4,
sal.unt_00_01 unt_tbl,
sal.dpt_00_01 dpt_tbl,
sal.dsg_00_01 dsg_tbl
WHERE emp_fil.emp_cod = a4.emp_cod
AND emp_fil.bas_cod = a4.bas_cod
AND a3.trn_ide = a4.trn_ide
AND a3.bas_cod = a4.bas_cod
AND unt_tbl.unt_cod = a3.unt_cod
AND dpt_tbl.dpt_cod = a3.dpt_cod
AND dsg_tbl.dsg_cod = a4.dsg_cod
AND atn_dte BETWEEN '01-jan-2009' AND '31-jan-2009'
AND atn_typ = 1
It retrieves data in 16 msecs and returns 119316 records but when i add following order by clause in above query is slow down and take more than 2 minutes.
ORDER BY a3.unt_cod, sft_cod, dpt_cod, a4.lin_num, a4.ord_cod, apt_dte
what may be problem?
Thanks In Advance
Asif.
[Updated on: Tue, 03 February 2009 23:43] Report message to a moderator
|
|
|
Re: Order by Clause causes slow query [message #384472 is a reply to message #384458] |
Wed, 04 February 2009 00:12   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
>16 msecs and returns 119316
Not really, it just looks that way. What it is actually doing is returning the FIRST PAGE of rows (probably 20 or so). The other 119294 haven't even been read from disk yet.
When you use an ORDER BY, it requires EVERY row to be identified before it can begin the sort.
If you want to compare the FULL execution time of two queries, try the method mentioned in this article: http://www.orafaq.com/node/1407
Ross Leishman
|
|
|
|
Re: Order by Clause causes slow query [message #384502 is a reply to message #384498] |
Wed, 04 February 2009 02:27  |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
By doing performance tuning.
Read the sticky in the performance tuning forum first, and then have a look at the execution plan for the query to find out if indexes can be used to speed it up.
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:40:52 CST 2025
|