Views, predicates and partition-elimination

From: Paul Parker <parkerpg_at_bellatlantic.net>
Date: Sat, 21 Apr 2001 03:23:38 GMT
Message-ID: <3ae0f797.342705717@news.bellatlantic.net>

Does anyone have any ideas?

I have a table CLAIM_DEFECT, range partitioned on a date column PAY_DEALER_DATE spanning 7 years resulting in 28 partitions. I have a view TEST_V on top of this table representing only 4 of the 7 years
(PAY_DEALER_DATE between '01-jan-1997' and '31-dec-2000').

As expected, the following SQL achieves the partition-elimination:
(the 13 - 28 represents the start and stop partitition from the plan
table)

select * from test_v;

 SELECT STATEMENT Cost = 33518
  2.1 PARTITION RANGE ITERATOR 13-28
    3.1 TABLE ACCESS FULL CLAIM_DEFECT 13-28 The following SQL also behaves as expected:

select * from test_v
where
pay_dealer_date between '01-jan-1998' and '31-dec-1998';

 SELECT STATEMENT Cost = 8125
  2.1 PARTITION RANGE ITERATOR 17-20
    3.1 TABLE ACCESS FULL CLAIM_DEFECT 17-20 In other words, the predicate applied to the SQL statement is used to achieve the partition-elimination, superceding that of the predicate in the view.

However, if I add another predicate to the query, the order of the predicates become significant.

STMT1

---

select
*
from
test_v
where
pay_dealer_date between '01-jan-1998' and '31-dec-1998' and
model_code = '1234';

 SELECT STATEMENT Cost = 7322
  2.1 PARTITION RANGE ITERATOR 13-28
    3.1 TABLE ACCESS BY LOCAL INDEX ROWID CLAIM_DEFECT 13-28

      4.1 BITMAP CONVERSION TO ROWIDS
        5.1 BITMAP INDEX SINGLE VALUE CDTX007 13-28

STMT2

---

select
*
from
test_v
where
model_code = '1234'
and
pay_dealer_date between '01-jan-1998' and '31-dec-1998';

  2.1 PARTITION RANGE ITERATOR 17-20
    3.1 TABLE ACCESS BY LOCAL INDEX ROWID CLAIM_DEFECT 17-20

      4.1 BITMAP CONVERSION TO ROWIDS
        5.1 BITMAP INDEX SINGLE VALUE CDTX007 17-20

As the sql which I am executing actually comes from a 3rd party tool, I cannot order the predicates. Is there any other way which I can achieve the proper partition-elimination when using a view? Is there any way I can see the sql which the optimizer actually uses when referencing a view? 10053 doesn't show the sql with the base table, only the reference to the view.

Thanx in advance
Paul Received on Fri Apr 20 2001 - 22:23:38 CDT