Spliiitng Sql query while extracting

[dspxlearn]

Hi,

I am designing a job which is pulling data from the oracle database.The sql used for this has 2 UNIONs with 2 oracle views(sometimes more thatn 2). So, usage of 2 Oracle stages in the design will be any adavantageous in terms of performance while extracting?
There are no partitions created for these views in the database level.

[prabu]

Hi,

I am designing a job which is pulling data from the oracle database.The sql used for this has 2 UNIONs with 2 oracle views(sometimes more thatn 2). So, usage of 2 Oracle stages in the design will be any adavantageous in terms of performance while extracting?
There are no partitions created for these views in the database level.

1.UNION ALL can run in parallel as there is no necessity of removing the duplicates unlike a UNION. This is at the db level.

2.The underlying tables of the views should have partitions for the query to run better. again, it depends on the view defintion.

3.you can try to get the 2 view definitions and see if they can be made into a single query. ie., if both these views have the same joins.

regards,
Prabu

[DSguru2B]

You can do it at the database level or do it in seperate stages and collect them. It depends upon how much temp space you have, how much loaded the database will be when your run the query. Will your DBA allow that ??? etc. etc. etc.