Indexes become unusable

Ken18

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

AndrewSayer

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

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

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

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

Hi Andrew,

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

Thank you.

Ken18

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

Cookiemonster76

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.

BEDE

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.