Re: SQL tuning

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 8 Apr 2003 12:43:00 +0100
Message-ID: <b6uci8$6s9$1$8302bc10@news.demon.co.uk>

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.

Imagine that you have one task in the task table with two associated work_queue entries, then the subquery approach will return the answer 1, the join approach will return the answer 2.

With your version of Oracle, and with adequate statistics, and if there is a constraint that ensures that there is a single relevant work_queue entry per task, Oracle should automatically be able to rewrite the subquery as a semi-join to get the effect you want.

It would help if you gave us the definitions of the available indexes, and the allowability of NULLs, and the definition of unique and primary key constraints. At the moment I would guess that you MIGHT get the result you want to see if you create histograms on

    task.queue_status
    task.task_prompt
    task.task_open_ind

as these look as if they might be the sorts of columns with a very strong skew (e.g 99% 'yes' 1% 'no'), and if the histograms are there, then Oracle may adjust its calculations of selectivity so much that it may take a better path.

NB - see the cardinality in your join query show 28,911 rows to be counted - this suggests that there is a stats problem that is making Oracle take the path that it is.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"srivenu" <srivenu_at_hotmail.com> wrote in message
news:1a68177.0304032138.4b012430_at_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 Tue Apr 08 2003 - 06:43:00 CDT