IOT

[lee]

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.

[Shestakov]

Use HINT
select
/*+ index (BANK_IDENTIFIER_IOT pty_nam_index_name) */
...
from ...
where ...
;

[lee]

Thanks, but I have the following question:
The users are always going to put order by bi.pty_nam asc in all their queries. So, is it possible to have the pty_nam column sorted in the definition of the IOT or is there any other way?


Please help. Thanks.

[Shestakov]

No, Sort option on particular field in
CREATE TABLE (or ALTER TABLE) command no exists.

[lee]

Is there any other way to do this then?

Thanks.

[lee]

Thanks, but I have the following question:
The users are always going to put order by bi.pty_nam asc in all their queries. So, is it possible to have the pty_nam column sorted in the definition of the IOT or is there any other way?


Please help. Thanks.

[Shestakov]

You can plare record in some order only if you recreate table, for example:

commit;
set transaction use rollback segment VERY_LARGE_SEGS;

create table temp_table
as
select * from some_table
order by pty_nam; -- (or other fields)

rename table some_table to old_some_table;

rename table temp_table to some_table;

drop table old_some_table;

...
next step -> recreate all INDEXES, TRIGGERS, CONSTRAINTS ;

[lee]

Is there any other way using IOT? Please explain.

Thanks.

[Shestakov]

On my expirience no.

Table must has phisical order of rows for it.

[molonede]

It sounds like you are confused on why the optimizer choose to use the index. The optimizer finds the quickest way to get you the data. The order by column is used in ordering the data, but the optimizer uses the index to give you the results the quickest, not for the order.