How Oracle Enterprise stage reformats SQL with /*+rowid(p)*/
Posted: Sun Oct 14, 2007 7:25 pm
Hi,
One of my SQL Query to select records from Oracle table causing an issues to retrieve the data from table. Job aborted after running about 10 hrs saying that "Snap shot too old".
We verified the Sql at the database side - query is reformatted at the database side by adding /*+rowid(p)*/ as hint and it is adding row id clause in the where condition, DBA's are saying that by adding the rowid hint is causing the job to abort after running long time.
SQL query - is just a join between 2 table joining on where Key/Indexed columns are, but still it is causing an issue.
I am just trying to understand why the query is reformatted to add hint and where condition? my guess is as we are running in Oracle Enterprise stage - it is splitting the query to run in multiple way (px), but how it is calculating the proper number of rowid's in where condition?
I am not agreeing with my DBA as they are pointing that it is DataStage's fault by adding the hint and rowid's. (As if i change the query to join on other non indexed column it is working fine).
If any one has any information please share how this Oracle EE Stage works and it is common in PX area to add this extra hint.
Any information is appreciated.
Thanks
One of my SQL Query to select records from Oracle table causing an issues to retrieve the data from table. Job aborted after running about 10 hrs saying that "Snap shot too old".
We verified the Sql at the database side - query is reformatted at the database side by adding /*+rowid(p)*/ as hint and it is adding row id clause in the where condition, DBA's are saying that by adding the rowid hint is causing the job to abort after running long time.
SQL query - is just a join between 2 table joining on where Key/Indexed columns are, but still it is causing an issue.
I am just trying to understand why the query is reformatted to add hint and where condition? my guess is as we are running in Oracle Enterprise stage - it is splitting the query to run in multiple way (px), but how it is calculating the proper number of rowid's in where condition?
I am not agreeing with my DBA as they are pointing that it is DataStage's fault by adding the hint and rowid's. (As if i change the query to join on other non indexed column it is working fine).
If any one has any information please share how this Oracle EE Stage works and it is common in PX area to add this extra hint.
Any information is appreciated.
Thanks