Oracle connector stage is not doing index scan in update

[pradeep9081]

Hi,

I have a table in target with 7 columns where 2 of them are primary keys and has primary key index on those columns. Those two columns are namely 'Item_type varchar2(5), Item_no varchar2(15)'.

I'm doing an 'Update then insert' in oracle connector stage. The job is running for ever. Because its doing full table scan on the table and not using the indexes defined. I tried only putting 'Update' rather than 'Update then insert' but still the same problem.

When I checked with the DBA, Index is being used when we give literals than bind variables. Even index is being used from TOAD with few bind variables .

DBA tried with different options by changing the database parameters like optimizer_mode, cursor_sharing and other optimizer parameters. But, still the issue is not resolved.

Target table has 2 million records, I'm trying to update only 1000 records.

Below is the query:
UPDATE LA.ITEM_T SET ITEM_STATE=:ITEM_STATE,ITEM_NAME_EN=:ITEM_NAME_EN,PA_NO_ACTIVE=:PA_NO_ACTIVE,UPD_DTIME=:UPD_DTIME,DELETE_DTIME=:DELETE_DTIME WHERE ITEM_TYPE=:ITEM_TYPE AND ITEM_NO=:ITEM_NO.

INSERT INTO
LA.ITEM_T(ITEM_TYPE,ITEM_NO,ITEM_STATE,ITEM_NAME_EN,PA_NO_ACTIVE ,UPD_DTIME,DELETE_DTIME) VALUES(:ITEM_TYPE,:ITEM_NO,:ITEM_STATE,:ITEM_NAME_EN,:PA_NO_ACTIVE,:UPD_DTIME,:DELETE_DTIME)

Is there any setting from Database side that needs to change in order to use the indexes or any settings from datastage needs to be changed ?

Please suggest.

Thanks

[pradeep9081]

Any reply on this is really appreciated.

[ahmedwaseem2000]

is the table indexed on ITEM_TYPE AND ITEM_NO ?

What type of index is defined?

have you tried other options like merge query which will be executed completely on DB.

something like this

Code: Select all

MERGE INTO LA.ITEM_T  LAT USING DUAL ON ( ITEM_TYPE=:ITEM_TYPE AND ITEM_NO=:ITEM_NO )

WHEN MATCHED THEN 

UPDATE SET ITEM_STATE=:ITEM_STATE,ITEM_NAME_EN=:ITEM_NAME_EN,PA_NO_ACTIVE=:PA_NO_ACTIVE,UPD_DTIME=:UPD_DTIME,DELETE_DTIME=:DELETE_DTIME

WHEN NOT MATCHED THEN

INSERT INTO 
(ITEM_TYPE,ITEM_NO,ITEM_STATE,ITEM_NAME_EN,PA_NO_ACTIVE ,UPD_DTIME,DELETE_DTIME) VALUES(:ITEM_TYPE,:ITEM_NO,:ITEM_STATE,:ITEM_NAME_EN,:PA_NO_ACTIVE,:UPD_DTIME,:DELETE_DTIME)