Creating performant pushdown SQL in Oracle

[Former Member]

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 ?

[werner_daehn · Accepted solution]

We are switching back and forth on this topic.

In some releases we pushdown automatically in such a scenarion - and get complaints from those users who did not or cannot grant the permissions.

In current releases we are more on the save side and push down only if we are sure - unfortunately we cannot recognize if the tables are select-able or not.

Another point to consider are aliases and datastore configurations. What if the owners of the tables are different according to the repo however by using aliases in the datastore config we make them the same. Ot vice versa.

To cut a long story short, sooner or later we will have to make both groups happy - somehow. For now, don't use two datastores but one instead. (via repo export, etl edit and import this can be changed quickly)

[Former Member]

Will achieve the correct SQL by changing the login id to the source DS and using an alias. Still need to grant select privileges - an extra overhead - easy in development, but much more difficult when dealing with DBAs in production

[werner_daehn]

In DI 12.X no, as there we use the effective user after the aliasing. Only if TNSName and the effective user are the same, then we do a full pushdown (unfortuantely). Feel free to file a bug to raise this problem attention!

[Former Member]

I forgot to state, the two schemas are on the same instance, so I have no need of database links.

Thanks

[Former Member]

if both the schemas are on same instance and you have imported these tables in Different datastores in DI then you have to create database links to utilize full database pushdown

[Former Member]

you can use database links to acheive full push down between tables in different datastore.

But you may run into issues depending on sql statement with dblinks if you have parallel option set for table, and both of your schemas are on same oracle database. You have to choose between PARALLEL and Complete push down

I have seen this issue with using MERGE to do Auto Correct load