roy wrote:Hi,
Do you have the $DS_ENABLE_RESERVED_CHAR_CONVERT set to true in your job properties?
Where/How i can found this flag?
In your job properties in the parameters tab there is a command button name add Environment Variable.
The variable itself is put as other parameters with the name I gave.
Roy R.
Time is money but when you don't have money time is all you can afford.
As your where clause includes the column CYCLE_MONTH, oracle should use partition pruning to limit the partitions accessed by the query.
How many partitions are there and how many are you putting in the parameter when running the job? I assume the table is range partitioned?
Run an explain plan on the query and check that partition pruning is happening and also whether the index is being used. Does a value of 'RT' bring back only a small percentage of the total rows - if not then you don't want the index to be used.
Is there a parallel option set on this table in Oracle. If so Oracle will run more than one query to get the results from each affected partition and then combine the answers into one result set. Be careful if this is the case as setting the Datastage partition table option will cause datastage to issue more than one select statement. You don't want Datastage producing lots of queries and then oracle trying to parallelise these as well.
As a previous post said get your DBA to monitor what happens when you run the query (in sqlplus and in Datastage).
thompsonp wrote:As your where clause includes the column CYCLE_MONTH, oracle should use partition pruning to limit the partitions accessed by the query.
How many partitions are there and how many are you putting in the parameter when running the job? I assume the table is range partitioned?
Run an explain plan on the query and check that partition pruning is happening and also whether the index is being used. Does a value of 'RT' bring back only a small percentage of the total rows - if not then you don't want the index to be used.
Is there a parallel option set on this table in Oracle. If so Oracle will run more than one query to get the results from each affected partition and then combine the answers into one result set. Be careful if this is the case as setting the Datastage partition table option will cause datastage to issue more than one select statement. You don't want Datastage producing lots of queries and then oracle trying to parallelise these as well.
As a previous post said get your DBA to monitor what happens when you run the query (in sqlplus and in Datastage).
