Re: Partition Elimination Failing to Work

From: rs <info_at_bytelife.com>
Date: Wed, 19 Feb 2003 20:09:19 +0100
Message-ID: <3e53d63a$0$11521$7a0f4ed3@energis-news-env>

hey Gee,
Try this explain plan:

col p1      for A8  head 'Part|Strt-End'
col Q1      for A55 head "Query plan"

select lpad(' ',2*(level-1))||level||'.'||nvl(position,0)||' '||operation||' '||options

       ||' '||object_type||' '||object_name
       ||' '||decode(id,0,'Cost = '||' '||position) Q1

 ,substr(partition_start,1,3)||'-'||substr(partition_stop,1,3) p1 from plan_table
start with id = 0
connect by prior id = parent_id
/
The partition_start and partition_stop should give you a clue. The FULL doesn't mean is scanning all partitions, it should definitely do some partition pruning.

cheers

     Roelof Schierbeek

R.Schierbeek, DBA
e-mail: info_at_nottospmbytelife.com
sql : www.bytelife.com/sql4dba.htm

Gee <george_at_41donny.freeserve.co.uk> schreef in
> OK guys,
> I have a table partition by FISCAL_PERIOD, i.e. 01, 02 ... 16. It is
> analyzed (using analyzed command and not DBMS_STATS) running on 8.1.7,
> Win2000. However, when I issue a SQL statement which I think should
> invoke partition elimination it appears to be doing a FULL table scan.
> For example :
>
> select *
> from general_ledger_line
> where fiscal_period = '01'
>
> Explain plan :
>
> SELECT STATEMENT Hint=CHOOSE 2 M 3672
> TABLE ACCESS FULL GENERAL_LEDGER_LINE 2 M 521 M 3672
> Then when I tried to explicitly tell Oracle to look at 1 partition
> only, i.e.
>
> select *
> from general_ledger_line partition(gll_p01)
>
> I get the same plan and cost!
> The table has a global PK and 4 other LOCAL bitmaps (which are rebuilt
> daily).A Sample of table defintion is listed below.
>
> OPTIMIZER_Index_cost_adj = 10
> OPTIMIZER_INDEX_CACHING = 90
>
> Can anybody offer any advice as to why this is happening?
>
> Regards,
> George

....... Received on Wed Feb 19 2003 - 13:09:19 CST