Px Oracle OCI - User Derfined Query with Subselect
Posted: Wed Mar 09, 2011 11:32 am
Here is an interesting issue I found when implementing a User defined select or a select list containing a sub select in the Oracle OCI Px stage in 8.0.1
Datastage incorrectly added the partition information to the where clause in the subselect, not the main query body, such as:
where (GBI_LA_DW.P_SO_DTL.rowid between 'AAFmmYAAFAAGa44AAA' and 'AAFmmwAAFAAGXzfH//' ) AND ...
The result was a sequential read across all partitions for each node, since the rowid filter was not in the right place.
The work around is intuitive, but anyone know of a patch for this or if this was fixed in later releases?
Datastage incorrectly added the partition information to the where clause in the subselect, not the main query body, such as:
where (GBI_LA_DW.P_SO_DTL.rowid between 'AAFmmYAAFAAGa44AAA' and 'AAFmmwAAFAAGXzfH//' ) AND ...
The result was a sequential read across all partitions for each node, since the rowid filter was not in the right place.
The work around is intuitive, but anyone know of a patch for this or if this was fixed in later releases?