SQL tuning

From: srivenu <srivenu_at_hotmail.com>
Date: 3 Apr 2003 21:38:24 -0800
Message-ID: <1a68177.0304032138.4b012430@posting.google.com>

The following SQL statement is issued from one of my applications. 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 )

The following is the plan for this statement.

  COUNT(*)

---

         0

1 row selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1348 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=1348 Card=10698 Bytes=106980)

   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
         18  db block gets
      10167  consistent gets
          9  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
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now i change the above statement to this SELECT count ( *)
FROM task, work_queue
WHERE ( task.queue_status='N' OR task.queue_status ='C' ) AND task.task_prompt='N'
AND task.task_open_ind ='Y'
and work_queue.work_queue_id =task.work_queue_id and work_queue.employee_number =:x

  COUNT(*)

---

         0

1 row selected.

Execution Plan

---

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

   2    1     NESTED LOOPS (Cost=27 Card=28911 Bytes=751686)
   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=5 Card=213941 Bytes=2139410)

Statistics

---

          0  recursive calls
          4  db block gets
         46  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

It has improved a lot.
Now my problem is i cant change the application. Hoc can i make the optimizer to use the second execution plan for the first statement ?
Thanks in advance for any help.

regards
Srivenu Received on Thu Apr 03 2003 - 23:38:24 CST