Skip to Content
0
Former Member
Mar 04, 2009 at 05:10 PM

Creating performant pushdown SQL in Oracle

37 Views

I am using DS 3.1 and the ECCD methodology and have the following issue in Oracle. The situation is as follows -

I have two datastores, each pointing to diffferent schemas.

SourceDS - SchemaA

Tables - SourceTab

ExtractDS - SchemaB

Tables - ExtractTab

My extract DF is extremly simple,

SourceTAB -> Query -> ExtractTab.

This DF produces a simple SELECT ... from SourceTAB piece of SQL and not an INSERT /+ APPEND/ INTO ... piece of SQL which will perform much better.

I assume that DI thinks that SchemaB cannot see SchemaA's tables and therefore brings the data into the engine from SourceTab and then pushes it into ExtractTab.

To overcome this SchemaA has granted select privilege on SourceTab to SchemaB.

Then I import SourceTab into ExtractDS. Having both tables in the same DS allows DI to produce the required INSERT /+ APPEND/ INTO ... piece of SQL.

How can I achieve the performant SQL without the tables being in the same DS, but remaining in their orignal datastores ?

Any ideas please ?