SQL tuning in View

[Beantree]

I have a view which utilized 2 inline views as part of the FROM clause.

A simplified example:
Select
<fields>
FROM
table1
,(select <fields> from table2) t2
,(select <fields> from table3) t3
WHERE
t1.account_number = t2.account_number
and t2.account_number = t3.account_number (+)
and t2.record_date = t3.record_date (+)

t2 and t3 are partitioned on Record_date.

When I select from the view including a record_date criteria of a single day, the explain plan is showing a single partition used from t2, but is performing a full scan on t3.

How do I write the view, or other mods (indexes, hints, etc) to get the partition pruning to work in this case?

Thanks

 

[did02]

Hi Beantree,

Is t3.record_date indexed ?
If not, try to create an index. Compute (or estimate) statisitics and then try an explain plan again.

Rgds,
Did02

 

[Beantree]

It has a bitmap index, local to each partition.

 

[Scunningham99]

What is the definition of the index?

Have you tried monitoring the usage of the index? to check the index is being used.

For example. When creating an index you must reference it in the correct order (for which you created it) in your where clause, else the index will not be used

if u need syntax for monitoring usage post again.




Sy UK

 

[Beantree]

In Oracle 8.1.7, using the Choose Optimizer mode, the order of the where clause is not supposed to matter. the tables and indexes have been analyzed.

Regardless of any index, partition pruning should be used by the optimizer. It's half the reason for partitioning being a feature of the database.

Partition pruning is happening in T2, as it should.

Here is the explain plan for a query against the view with a single RECORD_DATE criteria.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=HINT: RULE 4 G 362766
HASH JOIN 4 G 607G 362766
TABLE ACCESS FULL RRREGS 2 K 46 K 19
HASH JOIN 4 G 519G 181674
TABLE ACCESS FULL MV_CUSTOMER 70 K 1 M 875
HASH JOIN OUTER 6 M 577 M 177993
VIEW 75 K 3 M 14536
SORT GROUP BY 75 K 14 M 14536
PARTITION RANGE SINGLE KEY KEY
TABLE ACCESS BY LOCAL INDEX ROWID T_MONEY_BAL_ARC 75 K 14 M 12257.2107136194 KEY KEY
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE I_T_MONEY_BAL_ARC_RECORD_DATE KEY KEY
VIEW 842 K 32 M 163182
SORT GROUP BY 842 K 35 M 163182
PARTITION RANGE ALL 1 4
TABLE ACCESS FULL T_POSITIONS_ARC 842 K 35 M 156610 1 4


Note the PARTITION RANGE SINGLE for t2 and PARTITION RANGE ALL for t3.

 

[Beantree]

I had coded in a Rule hint when I ran this Explain plan. I get the same plan without the hint.

I am wondering if the outer join on t3 afects the partition pruning.

 

[Beantree]

any other ideas?

 

[Beantree]

I have confirmed that removing the outer join then allows for partition pruning on t3.

However, in order for the query to work, I must have an outer join.