Hi,
I have created an IOT as follows:
create table bank_identifier_iot(
PTY_ID NUMBER(10) NOT NULL,
PTY_STA_TYP VARCHAR2(10) NOT NULL,
BANK_COD VARCHAR2(2) NOT NULL,
BANK_RTE_ID VARCHAR2(35) NOT NULL,
MAX_DAYS_VAL_DT_IN_PAST NUMBER(5),
MAX_DAYS_VAL_DT_IN_FUT NUMBER(5),
PAYRO_DT_OFS NUMBER(5),
PRTY_COD VARCHAR2(10),
PTY_NAM varchar2(105),
CONSTRAINT pk_bank_ident PRIMARY KEY (pty_id, pty_sta_typ, bank_cod))
ORGANIZATION INDEX

Then, inserted records as:

insert into bank_identifier_iot
select * from bank_identifier_pty2
/
commit

Also, I have created a Primary key on pty_id, pty_sta_typ and bank_cod columns.

Also, I have created an index on pty_nam column.


Now, I am trying to run the following query:


select P.PTY_ID, P.PTY_TYP, P.PTY_STA_TYP, P.PTY_NAM, P.PTY_SHRT_NAM, P.ADR_LNE_2, P.ADR_LNE_3, P.ISO_CTRY_COD, C.CTRY_LNG_NAM, P.ADR_LNE_1, C.CTRY_SHRT_NAM, BC.SYS_NAM, BI.BANK_RTE_ID, BC.BANK_COD, P.CORR_ACCT_NO, P.SETL_ACCT_NO, P.ONLN_BANK_IND, P.COMPTRSD_IND, P.ONLN_ACH_IND, P.ONLN_FED_IND, BC.LCL_IND
from BANK_IDENTIFIER_IOT BI,
PARTY P,
COUNTRY C,
BANK_CODE BC
WHERE ( P.ISO_CTRY_COD = C.ISO_CTRY_COD (+)
AND BI.PTY_ID = P.PTY_ID
AND BI.PTY_STA_TYP = P.PTY_STA_TYP
AND BI.BANK_COD = BC.BANK_COD
AND P.PTY_TYP = 'BANK'
AND P.PTY_STA_TYP = 'PROCESSED'
AND BI.PTY_NAM LIKE 'MAR%')
ORDER BY BI.PTY_NAM ASC



This returns 281 records in 12 seconds.
It used the primary key for index fast full scan. Why? Shouldn't it use the pty_nam index?

Please help.

Is there in the syntax where I can specify it to use the pty_nam column? Please help.

It's urgent.