Home » SQL & PL/SQL » SQL & PL/SQL » Good SQL statement
Good SQL statement [message #215531] Mon, 22 January 2007 13:50 Go to next message
waris@satyam
Messages: 1
Registered: June 2006
Junior Member
Hello Experts,
[b]I have this below SQL statement with it's explain plan[/b]

SELECT 
       c.run_id, a.company, a.paygroup, a.pay_end_dt, a.off_cycle, a.page_num,
       a.line_num, a.paycheck_option, a.sepchk
  FROM ps_pay_check a, ps_pay_calendar c
 WHERE a.emplid = '111111111'
   AND a.company = 'ABC'
   AND a.paycheck_status = 'C'
   AND a.company = c.company
   AND a.paygroup = c.paygroup
   AND a.pay_end_dt = c.pay_end_dt
   AND (    c.pay_confirm_run = 'N'
        AND (c.pay_precalc_run = 'Y' OR c.pay_calc_run = 'Y' OR c.run_id = '701'
            )
       )

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=68)
   1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=68)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAY_CHECK' (Cost=2
          Card=2 Bytes=84)

   3    2       INDEX (RANGE SCAN) OF 'PS1PAY_CHECK' (NON-UNIQUE) (Cos
          t=3 Card=12)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAY_CALENDAR' (Cost
          =1 Card=1 Bytes=26)

   5    4       INDEX (UNIQUE SCAN) OF 'PS_PAY_CALENDAR' (UNIQUE)




Statistics
----------------------------------------------------------
        896  recursive calls
          0  db block gets
        288  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        317  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


[b]Same SQL statement with RULE hint and its explain plan:[/b]


SELECT /*+ RULE */
       c.run_id, a.company, a.paygroup, a.pay_end_dt, a.off_cycle, a.page_num,
       a.line_num, a.paycheck_option, a.sepchk
  FROM ps_pay_check a, ps_pay_calendar c
 WHERE a.emplid = '111111111'
   AND a.company = 'ABC'
   AND a.paycheck_status = 'C'
   AND a.company = c.company
   AND a.paygroup = c.paygroup
   AND a.pay_end_dt = c.pay_end_dt
   AND (    c.pay_confirm_run = 'N'
        AND (c.pay_precalc_run = 'Y' OR c.pay_calc_run = 'Y' OR c.run_id = '701'
            )
       )



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAY_CHECK'
   3    2       INDEX (RANGE SCAN) OF 'PSFPAY_CHECK' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAY_CALENDAR'
   5    4       INDEX (UNIQUE SCAN) OF 'PS_PAY_CALENDAR' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        472  bytes sent via SQL*Net to client
        317  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Could you please let me know which one of these two SQL's is the best to use. One with RULE hint or without RULE hint.

Thanks in anticipation.

Thanks
Waris













[Updated on: Mon, 22 January 2007 13:55] by Moderator

Report message to a moderator

Re: Good SQL statement [message #215536 is a reply to message #215531] Mon, 22 January 2007 14:56 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
The second one has much less recursive calls and consistent gets. I'd go with that one.
Previous Topic: how -ve scale is used in number datatype??
Next Topic: Reg: Latest values....
Goto Forum:
  


Current Time: Mon Dec 05 09:11:37 CST 2016

Total time taken to generate the page: 0.26293 seconds