Re: Query rewrite

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 15 Jul 2005 20:39:30 +0000 (UTC)
Message-ID: <db96u2$6oa$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:at4gd1ls1ds5k251uj7amru8fo5irpk9ag_at_4ax.com...
> On 15 Jul 2005 11:08:28 -0700, "Ryan S" <rshevchi_at_vt.edu> wrote:
>
>>Yes of course, but that doens't make the query any faster by removing
>>them. Any info on increasing performance?
>
> You state that removing 2 unnecessary sort operations (ie the
> distincts) don't make the query faster? I would disagree with you.
> But apparently you already seem to know all answers, so why ask for
> help here?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Removing two unnecessary sort operations would make the query faster - but the optimizer is aware that a query with a distinct clause is already unique, so doesn't need to introduce its own sort. There are no unnecessary sorts taking place (you can confirm this with a 10032 trace).

Moreover, it happens in the case of the intersect that the presence of the distinct has allowed the optimizer to use a grouping estimate to come up with a slightly better cost estimate and more appropriate cardinality than it gets for the query without the distinct (which you can verify with a 10053 trace).

Example running under 9.2.0.6

create table t1 as
select * from all_objects
where rownum <= 2000;

create table t2 as
select * from all_objects
where rownum <= 2500;

collect statistics

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select distinct owner, object_type from t1

2 intersect
3 select distinct owner, object_type from t2 4 ;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=2 Bytes=56) 1 0 INTERSECTION

   2    1     SORT (UNIQUE) (Cost=15 Card=2 Bytes=28)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=2000 Bytes=28000)
   4    1     SORT (UNIQUE) (Cost=16 Card=2 Bytes=28)
   5    4       TABLE ACCESS (FULL) OF 'T2' (Cost=7 Card=2500 Bytes=35000)



SQL>

SQL>
SQL> select owner, object_type from t1
  2 intersect
  3 select owner, object_type from t2
  4 ;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=2000 Bytes=63000) 1 0 INTERSECTION

   2    1     SORT (UNIQUE) (Cost=18 Card=2000 Bytes=28000)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=2000 Bytes=28000)
   4    1     SORT (UNIQUE) (Cost=20 Card=2500 Bytes=35000)
   5    4       TABLE ACCESS (FULL) OF 'T2' (Cost=7 Card=2500 Bytes=35000)

Regards

Jonathan Lewis

Now waiting on the publishers: Cost Based Oracle - Volume 1

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

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 8th July 2005 Received on Fri Jul 15 2005 - 15:39:30 CDT