Hi,

We are facing one problem in a query because it uses partition view.

When we execute the query, this runs for more than 6 hours and gives 'snapshot too old' error.

1) Below is the explain plan:
SELECT STATEMENT Optimizer=CHOOSE (Cost=64555 Card=1142237 Bytes=470601644)
SORT (GROUP BY) (Cost=64555 Card=1142237 Bytes=470601644)
HASH JOIN (Cost=39157 Card=1142237 Bytes=470601644)
TABLE ACCESS (FULL) OF MAP_PRODUCT (Cost=65 Card=27120 Bytes=2467920)
HASH JOIN (Cost=37722 Card=1142237 Bytes=366658077)
TABLE ACCESS (FULL) OF MAP_TIME1 (Cost=6 Card=868 Bytes=59024)
NESTED LOOPS (Cost=37598 Card=4601846 Bytes=1164267038)
HASH JOIN (Cost=129 Card=787 Bytes=151104)
TABLE ACCESS (FULL) OF MAP_ORGANISATION_PS (Cost=124 Card=540 Bytes=89100)
INDEX (FAST FULL SCAN) OF IDX_79289055_IDX0 (UNIQUE) (Cost=4 Card=29652 Bytes=800604)
VIEW OF V_SEC_SALES_INVC_MRG (Cost=37598 Card=5848 Bytes=356728)
UNION-ALL (PARTITION)
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANST_SEC_SALES_INVOICE (Cost=1840 Card=50403 Bytes=2671359)
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANSACTIONT_SS (Cost=65887 Card=1523949 Bytes=96008787)

Few points:
- v_sec_sales_mrg view does 'union all' two identical tables which is mentioned above i.e. TRANST_SEC_SALES_INVOICE and TRANSACTIONT_SS.
- Both the tables are going for FULL tablescan and the result is joined with a nested loop.


2) Now, I tried with giving FULL(V_SEC_SALES_INVC_MRG) hint in the query and it got executed successfully in 25-30 minutes.

Here is the explain plan:
SELECT STATEMENT Optimizer=CHOOSE (Cost=102678 Card=1142237 Bytes=559696130)
SORT (GROUP BY) (Cost=102678 Card=1142237 Bytes=559696130)
HASH JOIN (Cost=72628 Card=1142237 Bytes=559696130)
TABLE ACCESS (FULL) OF MAP_PRODUCT (Cost=65 Card=27120 Bytes=2467920)
HASH JOIN (Cost=70874 Card=1142237 Bytes=455752563)
TABLE ACCESS (FULL) OF MAP_TIME1 (Cost=6 Card=868 Bytes=59024)
HASH JOIN (Cost=70717 Card=4601846 Bytes=1523211026)
HASH JOIN (Cost=129 Card=787 Bytes=151104)
TABLE ACCESS (FULL) OF MAP_ORGANISATION_PS (Cost=124 Card=540 Bytes=89100)
INDEX (FAST FULL SCAN) OF IDX_79289055_IDX0 (UNIQUE) (Cost=4 Card=29652 Bytes=800604)
VIEW OF V_SEC_SALES_INVC_MRG (Cost=67727 Card=118995501 Bytes=16540374639)
UNION-ALL
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANST_SEC_SALES_INVOICE (Cost=1840 Card=3175398 Bytes=168296094)
PARTITION LIST (ALL)
TABLE ACCESS (FULL) OF TRANSACTIONT_SS (Cost=65887 Card=115820103 Bytes=7296666489)

Differences between both the explain plans:
1) Expplan-1 goes for partition view (UNION-ALL (PARTITION) hint is there). So, it filters the rows first and then put a union all. The Cardinality of TRANST_SEC_SALES_INVOICE and TRANSACTIONT_SS are less.
Expplan - 2 doesn't go for partition view. It gets all the rows in both the tables and then put union all. The cardinality is more.

2) Expplan-1 uses nested loop.
Expplan-2 uses hash join.

3) Even though cost is less for expplan-1, it takes 6 hours and then gives snapshot too old error.
Even though cost is high for expplan-2, it retrieves records successfully in 25-30 minutes.

Since we are giving the query through reporting tools, we can't
give the FULL table hint. We should make optimizer to use the second explain plan( i.e not to use the partition view ) without giving any hint.

1) Ours is Oracle version 9.2.0.5.0
2) partition_view_enabled parameter is FALSE.


Please suggest me either
1) how to disable the usage of partition view. I think if I create an extra index in one of the above 2 tables, the structure of tables will not be identical and it will not use partition view. But the index may affect other reports. Is there any other way?
or
2) make optimizer calculate that partition view is expensive than not using partition view so that it will not use partition view.

Thanks in Advance.

Regards,
Sahay