DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: IOT

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    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.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Use HINT
    select
    /*+ index (BANK_IDENTIFIER_IOT pty_nam_index_name) */
    ...
    from ...
    where ...
    ;

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    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.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    No, Sort option on particular field in
    CREATE TABLE (or ALTER TABLE) command no exists.

  5. #5
    Join Date
    Feb 2000
    Posts
    142
    Is there any other way to do this then?

    Thanks.

  6. #6
    Join Date
    Feb 2000
    Posts
    142
    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.

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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 ;



  8. #8
    Join Date
    Feb 2000
    Posts
    142
    Is there any other way using IOT? Please explain.

    Thanks.


  9. #9
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    On my expirience no.

    Table must has phisical order of rows for it.

  10. #10
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    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.
    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width