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 Go to next message
mamalik
Messages: 266
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #384498 is a reply to message #384458] Wed, 04 February 2009 02:05 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Ok. How can we improve this???
Re: Order by Clause causes slow query [message #384502 is a reply to message #384498] Wed, 04 February 2009 02:27 Go to previous message
ThomasG
Messages: 3189
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.
Previous Topic: Create table if it doesn't exist
Next Topic: how to do this query pls? (merged)
Goto Forum:
  


Current Time: Thu Dec 08 20:04:36 CST 2016

Total time taken to generate the page: 0.11513 seconds