Forum Stats

  • 3,837,690 Users
  • 2,262,286 Discussions
  • 7,900,363 Comments

Discussions

Indexes become unusable

Ken18
Ken18 Member Posts: 139
edited May 2, 2018 6:31AM in SQL & PL/SQL

Hi All ,

Could you help me with this scenario where we see indexes marked as unusable post our application migration to a higher version.

We have recently migrated our application to a higher version , which comprise of sql scripts ran against the DB  ....   which leave few indexes unusable and has to rebuilt manually.

Environment :

DB -  11.2.0.4

Tables - Non partitioned     

[ select owner, table_name, partitioning_type, subpartitioning_type from all_part_tables ;    ..,  SELECT * FROM dba_part_tables;  ., SELECT * FROM dba_part_index;  select partition_name from user_tab_partitions where table_name = 'xxxxxxxxxxxxxxx';]

To be precise , what the sql's consist and does is ...

  • adds new columns to fewer tables
  • adding indees if not exist
  • add new constraint to table if it does not exist
  • add new sequence if it does not exist
  • create unique indexes
  • creating foreign keys for all of the tables

Glance of Syntax's of the scripts used :

==============================

ALTER TABLE xxxxxxxxxxx

CREATE INDEX xxxxxxxxxxxx

    ON xxxxxxxxxxxxxxx(

       xxxxxxxxxxxxxx,

        xxxxxxxxxxx,

        xxxxxxxxxxxx

    )TABLESPACE xxxxxxxxxxxxxxx;

ALTER TABLE xxxxxxxxxxxxx

        ADD (CONSTRAINT xxxxxxxxxxxxxxxxxxxxx PRIMARY KEY (id)) ;   

       

  CREATE UNIQUE INDEX xxxxxxxxxxxxxxxxxxxx

    ON xxxxxxxxx(

        xxxxxxxxxxxxx,

        xxxxxxxxxxxxxx

    )TABLESPACE xxxxxxxxxxxxxx;

ALTER TABLE xxxxxxxxxxxxxxxx

ADD CONSTRAINT xxxxxxxxxxxxxxxxxxx FOREIGN KEY (xxxxxx)

REFERENCES xxxxx (id)

ON DELETE SET NULL;

ALTER TABLE xxxxxxxxxxxxxxx

    ADD CONSTRAINT xxxxxxxxxxxxxxxxxx FOREIGN KEY (xxxxxxxxxxxxx)

    REFERENCES xxxxxxxxxxxx(id)

    ON DELETE CASCADE ;

Thanks

Tagged:
AndrewSayer

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 2, 2018 3:10AM

    Indexes general are unusable because they were never built in the first place or because you’ve done some segment move without updating them.

    When you build the indexes, were they unusable? How much time passed before they became unusable? What DDL was processed at that point? Alter table.. move?

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited May 2, 2018 3:10AM

    If you know which indexes got invalid, you can then determine to which table this index belongs.

    Search in your scripts for commands referencing this index or this table.

    Perhaps you are doing a 'alter table move' somewhere or a direct path load?

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 2, 2018 3:45AM
    Hans Steijntjes wrote:If you know which indexes got invalid, you can then determine to which table this index belongs.Search in your scripts for commands referencing this index or this table.Perhaps you are doing a 'alter table move' somewhere or a direct path load?

    Direct path load maintains indexes, sql ldr direct path load makes indexes unusable before it kicks off.

  • Ken18
    Ken18 Member Posts: 139
    edited May 2, 2018 4:27AM

    To be precise,  here is the script syntax for the table in question ...

    The syntax of table in question whose indexes ( ind1_xxxxxxxxxxxx, ind2_xxxxxxxxxxx , ind3_xxxxxxxxxxx , ind5_xxxxxxxxxxx ) is as below along with indexes ,

    CREATE TABLE tabname

      (id              number(20,0)  NOT NULL,

       version int DEFAULT 0 NOT NULL,

       bid         number(20,0)             NOT NULL,

       xxxxxxxxxxxxxxxxxxx

       xxxxxxxxxxxxxxxxxxxxxxxxxx

    ....

    ....

      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

       attributes           CLOB           NULL

      )

      LOB (attributes) STORE AS lobaccountattr (TABLESPACE xxxxxxxxxxxxxxx;

       TABLESPACE xxxxxxxxxxxxxxx;

    --

    --

        CREATE UNIQUE INDEX ind1_xxxxxxxxxxxx

        ON tab (

            id

        )TABLESPACE xxxxxxxxxxxxxxx;

    --

    --

        CREATE UNIQUE INDEX ind2_xxxxxxxxxxxx

        ON pcwbankaccount (

            cid

        )TABLESPACE xxxxxxxxxxxx;

            

    --   

    -- 

        CREATE UNIQUE INDEX ind3_xxxxxxxxxxxx

        ON pcwbankaccount (

        cid,

            acntnum,

            rt

        )TABLESPACE xxxxxxxxxxxx;

    --

    --

        CREATE INDEX ind5_xxxxxxxxxxxx;

        ON xxxxxxxxxxxx;(

           wtypeid,

            cart,

            cid

        )TABLESPACE xxxxxxxxxxxx;

               

    ALTER TABLE xxxxxxxxxxxx

            ADD (CONSTRAINT tabname_id_pk PRIMARY KEY (id)) ;

  • Ken18
    Ken18 Member Posts: 139
    edited May 2, 2018 4:28AM

    Hi Andrew,

    Alter table only to add constraint ... please see my detailed syntax w.r.t the same.

    Thank you.

  • Ken18
    Ken18 Member Posts: 139
    edited May 2, 2018 4:31AM

    Thanks Hans  , Please find the details in my reply to the original question.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited May 2, 2018 4:39AM

    What the others are trying to tell you is that nothing in the redacted scripts you posted should have caused the indexes to become unusable, so the problem most likely lies elsewhere.

    AndrewSayer
  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy
    edited May 2, 2018 6:31AM

    Index become unusable when something like alter table shrink space or alter table move compress happens. Has anything like that happened.

    Or indexes may become unusable when they are explicitly altered to be set unusable, which may be done in order to have some updates go faster. After that, indexes may be rebuilt. Not necessarily one by one, but they may be rebuilt using a script.

This discussion has been closed.