| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: A beginner's question on Oracle 8i: Table/Index Partitioning
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<EC4D9.81260$g9.228811_at_newsfeeds.bigpond.com>...
> > The plan shows:
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 * SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
> > 1 0 SORT (AGGREGATE)
> > 2 1 TABLE ACCESS (FULL) OF 'T_TEST' (Cost=1 Card=103 Bytes=6
> > 18)
> >
> > It appears that there is no partition pruning being performed.
>
> This *appears* to be correct again as again no partition range operation is
> listed in the execution plan.
>
> However I have seen "funnies" with this where partition pruning is obviously
> occurring due to the statistics being generated (ie. low number of
> consistent/physical reads) but the execution plan doesn't seem to show this.
> You need to trace the operation and see exactly what the optimizer is doing.
I figured out the causes. It appears that by using "SET AUTOTRACE" feature to auto generate the execution plan, the information provided by SQL*Plus is incomplete.
I issue proper "EXPLAIN PLAN FOR" command and run the utlxpls.sql provided by Oracle8i, and the execution plan shows that the partition is indeed being eliminated.
SQL> explain plan for
2 select /*+ index( a ) */
3 a.*
4 from t_test a
5 where a.f1 = 2001;
Explained.
real: 50
SQL> @c:\orawin8i\rdbms\admin\utlxpls
Plan Table
| Operation | Name | Rows | Bytes| Cost |Pstart| Pstop |
| SELECT STATEMENT | | 1 | 6 | 2 | | | | TABLE ACCESS BY GLOBAL IN|T_TEST | 1 | 6 | 2 | 3 | 3 | | INDEX RANGE SCAN |T_TEST_I1 | 1 | | 1 | 3 | 3 | --------------------------------------------------------------------------------
SQL> explain plan for
2 select sum(a.f2)
3 from t_test a
4 where a.f1 between 2500 and 2600;
Explained.
real: 0
SQL> @c:\orawin8i\rdbms\admin\utlxpls
Plan Table
| Operation | Name | Rows | Bytes| Cost |Pstart| Pstop |
| SELECT STATEMENT | | 1 | 6 | 1 | | | | SORT AGGREGATE | | 1 | 6 | | | | | TABLE ACCESS FULL |T_TEST | 103 | 618 | 1 | 3 | 3 | --------------------------------------------------------------------------------
Now I am wondering if there is a way to re-configure Oracle server so that the execution plan format used by utlxpls will be used when I turned on "SET AUTOTRACE" in SQL*Plus. Or am I doomed to suffer endless pain with no *hope*? Received on Tue Nov 26 2002 - 00:07:44 CST
 |
 |