SEO

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 |
--------------------------------------------------------------------

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...

On Dec 14, 8:17 pm, Frank van Bortel <frank.van.bor...@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 --

Mark D Powell wrote:
> On Dec 14, 8:17 pm, Frank van Bortel <frank.van.bor...@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)