Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL tuning

Re: SQL tuning

From: srivenu <srivenu_at_hotmail.com>
Date: 10 Apr 2003 01:56:00 -0700
Message-ID: <1a68177.0304100056.7b2152e4@posting.google.com> 





thanks for the reply Jonathan.


--One word of warning - you've made the same mistake that

--Rich Niemiec made in his book in 1999.  Your two queries

--are not logically equivalent unless there is a very specific

--uniqueness condition involved.



Can i use replace the EXISTS with an IN like this



SELECT count(*) 


FROM asap.task 


WHERE ( asap.task.queue_status='N' OR asap.task.queue_status ='C' ) 

AND asap.task.task_prompt='N' 
AND asap.task.task_open_ind ='Y' 
AND asap.task.work_queue_id in 




( SELECT work_queue_id 


FROM asap.work_queue 


WHERE asap.work_queue.employee_number =:x) 



The following is from the Oracle Doc



Use of EXISTS versus IN for Subqueries


In certain circumstances, it is better to use IN rather than EXISTS.
In general, if the selective predicate is in the subquery, then use
IN. If the selective predicate is in the parent query, then use
EXISTS.







Note: 


This discussion is most applicable in an OLTP environment, where the
access paths either to the parent SQL or subquery are via indexed
columns with high selectivity. In a DSS environment, there can be low
selectivity in the parent SQL or subquery, and there might not be any
indexes on the join columns. In a DSS environment, consider using
semi-joins for the EXISTS case.



 



See Also: 



"How the CBO Executes Anti-Joins" 



"HASH_AJ, MERGE_AJ, and NL_AJ" and "HASH_SJ, MERGE_SJ, and NL_SJ" 



Oracle9i Data Warehousing Guide 



Sometimes, Oracle can rewrite a subquery when used with an IN clause
to take advantage of selectivity specified in the subquery. This is
most beneficial when the most selective filter appears in the
subquery, and when there are indexes on the join columns.



Conversely, using EXISTS is beneficial when the most selective filter
is in the parent query. This allows the selective predicates in the
parent query to be applied before filtering the rows against the
exists criteria.





These are the indexes on the TASK table


TASK       FKIDX_TASK_SVCREQPROVPLAN      NONUNIQUE
TASK       FKIDX_TASK_TA_AC_CO_DA         NONUNIQUE
TASK       FKIDX_TA_SE_RE_SI              NONUNIQUE
TASK       FK_TASK_CIRCUIT                NONUNIQUE
TASK       FK_TASK_TASK_TYPE              NONUNIQUE
TASK       IDX_TASK_TASK_NUMBER           NONUNIQUE
TASK       IDX_TASK_TASK_STATUS           NONUNIQUE
TASK       IDX_TASK__REVISED_COMPLETION_D NONUNIQUE
TASK       IDX_TASK__SCHEDULED_COMPLETION NONUNIQUE
TASK       IX_TASK_WORK_QUEUE_TASK_OPEN   NONUNIQUE
TASK       IX_TASK_WORK_QUEUE_TASK_STATU2 NONUNIQUE
TASK       PK_TASK                        UNIQUE

                                                     Col
                                                     umn
                                                     Pos
                                                     iti
Owner      Table name Index Name                      on Column name


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

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

ASAP       TASK       FKIDX_TASK_SVCREQPROVPLAN        1 REQ_PLAN_ID
ASAP       TASK       FKIDX_TASK_TA_AC_CO_DA           1
ACTUAL_COMPLETION_D
                                                         ATE

ASAP       TASK       FKIDX_TA_SE_RE_SI                1
DOCUMENT_NUMBER
ASAP       TASK       FKIDX_TA_SE_RE_SI                2 SERV_ITEM_ID
ASAP       TASK       FK_TASK_CIRCUIT                  1
CIRCUIT_DESIGN_ID
ASAP       TASK       FK_TASK_TASK_TYPE                1 TASK_TYPE
ASAP       TASK       IDX_TASK_TASK_NUMBER             1 TASK_NUMBER
ASAP       TASK       IDX_TASK_TASK_STATUS             1 TASK_STATUS
ASAP       TASK       IDX_TASK_TASK_STATUS             2
SYSTEM_TASK_IND
ASAP       TASK       IDX_TASK__REVISED_COMPLETION_D   1
REVISED_COMPLETION_
                                                         DATE

ASAP       TASK       IDX_TASK__SCHEDULED_COMPLETION   1
SCHEDULED_COMPLETIO
                                                         N_DATE

ASAP       TASK       IX_TASK_WORK_QUEUE_TASK_OPEN     1 WORK_QUEUE_ID
ASAP       TASK       IX_TASK_WORK_QUEUE_TASK_OPEN     2 QUEUE_STATUS
ASAP       TASK       IX_TASK_WORK_QUEUE_TASK_OPEN     3 TASK_PROMPT
ASAP       TASK       IX_TASK_WORK_QUEUE_TASK_OPEN     4 TASK_OPEN_IND
ASAP       TASK       IX_TASK_WORK_QUEUE_TASK_STATU2   1 WORK_QUEUE_ID
ASAP       TASK       IX_TASK_WORK_QUEUE_TASK_STATU2   2 TASK_STATUS
ASAP       TASK       PK_TASK                          1
DOCUMENT_NUMBER
ASAP       TASK       PK_TASK                          2 TASK_NUMBER





19 rows selected.



These are the indexes on WORK_QUEUE table


Table name Index Name                     UNIQUENES


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

WORK_QUEUE FK_WQ_EMPLOYEE                 NONUNIQUE
WORK_QUEUE FK_WQ_WORK_QUEUE               NONUNIQUE
WORK_QUEUE PK_WORK_QUEUE                  UNIQUE


                                           Col
                                           umn
                                           Pos
                                           iti
Owner      Table name Index Name            on Column name


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

ASAP       WORK_QUEUE FK_WQ_EMPLOYEE         1 EMPLOYEE_NUMBER
ASAP       WORK_QUEUE FK_WQ_WORK_QUEUE       1 PARENT_WORK_QUEUE_ID
ASAP       WORK_QUEUE PK_WORK_QUEUE          1 WORK_QUEUE_ID





3 rows selected.




select count(*) from work_queue;



  COUNT(*)





       913



select count(*) from task;



  COUNT(*)





   2608563




select queue_status,count(*) from task group by queue_status;



Q   COUNT(*)



- ----------

B          2
C        419
E          7
G      44439
N     162914
X      19336
     2381452





7 rows selected.



select task_prompt,count(*) from task group by task_prompt



T   COUNT(*)



- ----------


N    2072523


Y     536046



2 rows selected.



select task_open_ind,count(*) from task group by task_open_ind



T   COUNT(*)



- ----------


N    2459644


Y     148925



2 rows selected.



I gather the statistics in a cron job every week.
i do it again now



analyze table task compute statistics;



Table analyzed.



analyze table task estimate statistics for columns task_prompt sample
15 percent;



Table analyzed.



analyze table task estimate statistics for columns task_open_ind
sample 15 percent;



Table analyzed.



analyze table task estimate statistics for columns queue_status sample
15 perc


ent;



Table analyzed.



the plan is



SELECT count ( *)


FROM task


WHERE ( task.queue_status='N' OR task.queue_status ='C' )
AND task.task_prompt='N'


AND task.task_open_ind ='Y'


AND EXISTS



( SELECT 'X'



FROM work_queue


WHERE work_queue.employee_number =:x


AND work_queue.work_queue_id =task.work_queue_id )




  COUNT(*)





         0



1 row selected.




Execution Plan



   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1633 Card=1 Bytes=10
          )

   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'IX_TASK_WORK_QUEUE_TASK_OPE
          N' (NON-UNIQUE) (Cost=1633 Card=4153 Bytes=41530)

   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'WORK_QUEUE' (Cost=2
          Card=1 Bytes=16)

   5    4         INDEX (UNIQUE SCAN) OF 'PK_WORK_QUEUE' (UNIQUE) (Cos
          t=1 Card=1)









Statistics



          0  recursive calls
         20  db block gets
      10864  consistent gets
          0  physical reads
          0  redo size
        200  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







But if i rewrite the query to this, i get the better plan.



SELECT count(*) 


FROM task 


WHERE ( task.queue_status='N' OR task.queue_status ='C' ) 

AND task.task_prompt='N' 
AND task.task_open_ind ='Y' 
AND task.work_queue_id in 




( SELECT work_queue_id 


FROM work_queue 


WHERE work_queue.employee_number =:x) 




  COUNT(*)





         0



1 row selected.




Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)


   2    1     NESTED LOOPS (Cost=17 Card=11224 Bytes=291824)
   3    2       TABLE ACCESS (FULL) OF 'WORK_QUEUE' (Cost=2 Card=5 Byt
          es=80)

   4    2       INLIST ITERATOR
   5    4         INDEX (RANGE SCAN) OF 'IX_TASK_WORK_QUEUE_TASK_OPEN'
           (NON-UNIQUE) (Cost=3 Card=83059 Bytes=830590)









Statistics



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






Your help is very much appreciated.


thanks in advance


srivenu
Received on Thu Apr 10 2003 - 03:56:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US