Re: which is fast and good output

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 -- Received on Thu Dec 14 2006 - 08:09:30 CST