Re: which is fast and good output

From: oracle dba <aoracledba_at_gmail.com>
Date: 14 Dec 2006 20:59:30 -0800
Message-ID: <1166158770.361549.263960@79g2000cws.googlegroups.com>

Mark D Powell wrote:
> On Dec 14, 8:17 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:
> > oracle dba schreef:
> >
> >
> >
> > > Hello All,
> >
> > > I am showing 2 explain plans, please tell me which is good and why.
> >
> > > SQL> @?\rdbms\admin\utlxplp.sql
> >
> > > PLAN_TABLE_OUTPUT
> > > -------------------------------------------------------------------------
> >
> > > ------------------------------------------------------------------------
> > > | Id | Operation | Name | Rows | Bytes | Cost
> > > |
> > > ------------------------------------------------------------------------
> > > | 0 | SELECT STATEMENT | | 1 | 235 | 6
> > > |
> > > |* 1 | VIEW | | 1 | 235 | 6
> > > |
> > > |* 2 | WINDOW SORT PUSHED RANK| | 1 | 40 | 6
> > > |
> > > |* 3 | TABLE ACCESS FULL | N | 1 | 40 | 4
> > > |
> > > ------------------------------------------------------------------------
> >
> > > SQL> @?\rdbms\admin\utlxplp.sql
> >
> > > PLAN_TABLE_OUTPUT
> > > ----------------------------------------------------------------------
> >
> > > --------------------------------------------------------------------
> > > | Id | Operation | Name | Rows | Bytes | Cost |
> > > --------------------------------------------------------------------
> > > | 0 | SELECT STATEMENT | | 1 | 40 | 4 |
> > > |* 1 | TABLE ACCESS FULL | N | 1 | 40 | 4 |
> > > |* 2 | TABLE ACCESS FULL | N | 1 | 15 | 4 |
> > > |* 3 | TABLE ACCESS FULL | N | 1 | 15 | 4 |
> > > --------------------------------------------------------------------The mauve one.
> >
> > --
> > Regards,
> > Frank van Bortel
> >
> > Top-posting is one way to shut me up...- Hide quoted text -- Show quoted text -

>
>

> Generally speaking you cannot declare one plan better than another just
> from looking at the two plans in isolation. First the two plans need
> to be of two versions of the same SQL statement taken on the same db
> with the same statistics or the comparison is not even valid.

>

> Run time, size of tables in rows and bytes, what columns are indexed,
> column statitics, and even database parameter setting may need to
> considered in relation to the plan output depending on the
> circumstances.

>

> The query text is probably the first item on the list of things needed
> to judge plans against. Does the plan look reasonable for what the SQL
> is asking to be done?

>
> HTH -- Mark D Powell --

There are the queries:

here cost is 6

SELECT a,b,n,whateverElse
FROM
(SELECT t.*,DENSE_RANK() OVER (PARTITION BY a ORDER BY a,b,n) rn FROM tblTest t WHERE a=12 AND NVL(b,3)=3 AND NVL(n,7)=7) WHERE rn=1;

here cost is 4

select a,b,n,whateverElse from tblTest
where (a=12 and b is null and not exists (select 1 from n where a=12 and b=3 and (n is null or n=7))) or(a=12 and b=3 and n is null and
not exists(select 1 from n where a=12 and b=3 and n=7)) or
(a=12 and b=3 and n=7) Received on Thu Dec 14 2006 - 22:59:30 CST