1 2 Previous Next 15 Replies Latest reply: Mar 7, 2014 4:42 AM by Mohamed Houri RSS

    ORA-01502 when skip_unusable_indexes=TRUE

    JackK

      Hi,

      I have some tables (call them "T-tables") with several million rows of data. Once a week I need to insert new data to that tables. The tables have some primary/unique indexes.

      This is the case (once a week):

      1. I am doing an import of a plan-text file (about 500MB) into my main tables.

      2. I need to make a copy of the imported data from main tables to T-tables.

       

      At the moment T-tables contains 100 of imported files. At the beginning (there were no more than 5 files in T-tables) the operation of coping took 2-3 minutes to complete. Now it takes 2 hours. I thing it's because index maintanance during inserting data to T-tables.

      So I made a try and make all T-table's indexes unusable before start of copy operation and execute:

      ALTER SESSION SET skip_unusable_indexes = TRUE;

      to prevent ORA-01502 error. However, during copy I got ORA-01502 error anyway. Why is this happened???

       

      Thank in advance,

      Jacek

        • 1. Re: ORA-01502 when skip_unusable_indexes=TRUE
          Hemant K Chitale

          From the documentation on "SKIP_UNUSABLE_INDEXES" :

           

          If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

           

           

          Hemant K Chitale

          • 2. Re: ORA-01502 when skip_unusable_indexes=TRUE
            Martin Preiss

            a unique index can not be skipped during inserts. For bitmap and non-unique B*Tree indexes the strategy of setting the indexes unusable and rebuild them after the load works fine - but not for unique indexes. You could define the PK-Constraints using non-unique indexes, but that should of course be tested thoroughly.

             

            If you now ask why a unique index can not be skipped then I have to answer: I don't know. I think it should be possible to defer the evaluation of uniqueness until after the load - but perhaps Oracle thinks that this could bring a lot of unnecessary work if there were indeed duplicates (making the index useless).

             

            As far as I know there is a document "Error: ORA-01502 occurs only on unusable unique index with SKIP_UNUSABLE_INDEXES=TRUE? [ID 272565.1]" - but I have currently no MOS access to check this.

             

            Message was edited by: Martin Preiss

            • 3. Re: ORA-01502 when skip_unusable_indexes=TRUE
              JackK

              Thanks, Hemant and Martin.

              • 4. Re: ORA-01502 when skip_unusable_indexes=TRUE
                Mohamed Houri

                As Martin and Hermant have already pointed it out, skipping unique indexes is not possible even when the skip_unusable_indexes parameter is set to TRUE.

                 

                You can instead do this

                 

                (1) disable unique constraint (or primay key)

                (2) set skip_unusable_indexes = true ( for other non unique indexes)

                (3) load your data

                (4) enable your unique (primary constraint) using the exceptions table as shown in the following blog article

                     On constraint validation : use of Exception table | Mohamed Houri’s Oracle Notes

                 

                (5) eventuelly delete duplicate rows (if any) using the corresponding rowid in that exceptions table

                 

                By the way, when loading data using SQLLoader with the option direct = true, SqlLoader, behind the scene disable the unique index without disabling the corresponding constraint and allow duplicate rows to be inserted in the table to see how the sqlloader process manage to do that

                 

                http://hourim.wordpress.com/2011/04/09/sql-loader-direct-path-and-duplicate-key/

                 

                 

                SQL> select index_name, status from user_indexes where table_name = 'T1';

                 

                 

                INDEX_NAME                     STATUS

                ------------------------------ --------

                T1_PK                          UNUSABLE

                 

                SQL> select constraint_name, status from user_constraints where table_name = 'T1';

                 

                CONSTRAINT_NAME                STATUS

                ------------------------------ --------

                T1_PK                          ENABLED

                 

                So for sure there is an option that allow inserting data only by disabling the unique index. For that i have to generate a trace file during the sqlloader load in a direct path load mode

                 

                Best regards

                Mohamed Houri

                • 5. Re: ORA-01502 when skip_unusable_indexes=TRUE
                  Jonathan Lewis

                  Using this strategy, it might be a good idea to create non-unique indexes to enforce the unique constraints.

                  This means the index could always be rebuilt, and then used to check the constraint as it was re-enabled.

                   

                  If I recall correctly, Oracle adds the hint /*+ skip_unq_unusable_idx */  to the SQL - but uses a hidden call to set a flag that makes this hint valid before using it.

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: ORA-01502 when skip_unusable_indexes=TRUE
                    Randolf Geist

                    Mohamed Houri wrote:

                     

                     

                    By the way, when loading data using SQLLoader with the option direct = true, SqlLoader, behind the scene disable the unique index without disabling the corresponding constraint and allow duplicate rows to be inserted in the table to see how the sqlloader process manage to do that

                     

                    http://hourim.wordpress.com/2011/04/09/sql-loader-direct-path-and-duplicate-key/

                     

                     

                    SQL> select index_name, status from user_indexes where table_name = 'T1';

                     

                     

                    INDEX_NAME                     STATUS

                    ------------------------------ --------

                    T1_PK                          UNUSABLE

                     

                    SQL> select constraint_name, status from user_constraints where table_name = 'T1';

                     

                    CONSTRAINT_NAME                STATUS

                    ------------------------------ --------

                    T1_PK                          ENABLED

                     

                    Mohamed,

                     

                    the SQL*Loader behaviour can be reproduced manually using SQL only, see here:

                     

                    Oracle related stuff: Primary key / unique constraints enforced using a non-unique index - 11.1.0.6 and 11.1.0.7

                     

                    This isn't prevented in any version I've tested so far (I haven't tested 12c yet, but it certainly applies to 11.2), and therefore is a potential trap that one can fall in when using non-unique indexes with primary / unique keys, since you can end up with duplicates in a table although the constraint is always enabled and the unusable, non-unique index can be rebuilt without any errors.

                     

                    Randolf

                    • 7. Re: ORA-01502 when skip_unusable_indexes=TRUE
                      Martin Preiss

                      Randolf,

                       

                      seems to have changed in 12.1. Using your example from http://oracle-randolf.blogspot.de/2008/11/primary-key-unique-constraints-enforced.html I get:

                      -- 12.1.0.1

                      drop table append_test;

                       

                       

                       

                      create table append_test as

                      select

                      trunc(sqrt(rownum-1)) as skewed_data,

                      rownum-1 as id,

                      lpad(rownum-1,10) id_char,

                      rpad('x',50, 'x') as filler

                      from

                      all_objects

                      where 1 = 2;

                       

                       


                      -- create non-unique index

                      create index append_test_pk on append_test(id);

                       

                       


                      -- add primary key constraint

                      alter table append_test add constraint pk_append_test primary key (id);

                       

                       


                      -- make the index unusable

                      alter index append_test_pk unusable;

                       

                       


                      -- now perform a direct-path insert

                      insert /*+ append */ into append_test

                      select

                      trunc(sqrt(rownum-1)) as skewed_data,

                      1 as id,

                      --rownum-1 as id,

                      lpad(rownum-1,10) id_char,

                      rpad('x',50, 'x') as filler

                      from

                      all_objects

                      where rownum <= 100;

                                                *

                      FEHLER in Zeile 1:

                      ORA-26026: Unique-Index TEST.APPEND_TEST_PK anfänglich in unbrauchbarem Zustand

                      -- i.e. ORA-26026: unique index ... initially in unusable state

                      The error message is a little bit strange, since the index is of course still nonunique:

                      select index_name

                           , uniqueness

                           , status

                        from user_indexes

                      where table_name = upper('append_test');

                       

                      INDEX_NAME                     UNIQUENES STATUS

                      ------------------------------ --------- --------

                      APPEND_TEST_PK                 NONUNIQUE UNUSABLE

                      But at least there are no duplicates inserted without further notice.

                       

                      Martin

                      • 8. Re: ORA-01502 when skip_unusable_indexes=TRUE
                        Jonathan Lewis

                        Randolf,

                         

                        It seems to be fixed in 11.2.0.4

                         

                        I copied your test code and got your results in 11.1.0.7, but the insert append failed in 11.2.0.4 with error ORA-26026: : unique index TEST_USER.APPEND_TEST_PK initially in unusable state ) - not quite the correct error since the index isn't unique, but certainly an appropriate response.  12.1.0.1 is fixed the same way.

                         

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: ORA-01502 when skip_unusable_indexes=TRUE
                          Randolf Geist

                          Jonathan, Martin,

                           

                          thanks for testing it - it's not fixed in 11.2.0.2 / 11.2.0.3 (I've just tested it again), so it seems to be something that was introduced with 12.1 resp. the backport of the fix to 11.2.0.4.

                           

                          Good to know that finally it was addressed - almost five years later than the original blog post (thinking about the release date of 11.2.0.4 / 12.1).

                           

                          Randolf

                          • 10. Re: ORA-01502 when skip_unusable_indexes=TRUE
                            Mohamed Houri

                            Randolf,

                             

                            Unless I have missed something, it is fixed in 11.2.0.3

                             

                            SQL> select * from v$version;

                             

                            BANNER

                            --------------------------------------------------------------------------------

                            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                            PL/SQL Release 11.2.0.3.0 - Production

                            CORE    11.2.0.3.0      Production

                            TNS for Linux: Version 11.2.0.3.0 - Production

                            NLSRTL Version 11.2.0.3.0 - Production

                             

                             

                            SQL>  insert /*+ append */ into append_test

                              2      select

                              3      trunc(sqrt(rownum-1)) as skewed_data,

                              4      1 as id,

                              5      --rownum-1 as id,

                              6      lpad(rownum-1,10) id_char,

                              7      rpad('x',50, 'x') as filler

                              8      from

                              9      all_objects

                            10     where rownum <= 100;

                            insert /*+ append */ into append_test

                            *

                            ERROR at line 1:

                            ORA-26028: index xxxx.APPEND_TEST_PK initially in unusable state

                             

                            PS : the error message is not showing unique index

                             

                            Jonathan,

                             

                            I have already read about the hint (SKIP_UNQ_UNUSABLE_IDX ) in ask tom site but as far as I was googling it with the wrong syntax I failed to get to that site

                             

                            https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:616795500346622064

                             

                            Anyway, yes it needs some internal setting to work properly because when I hinted the insert using SKIP_UNQ_UNUSABLE_IDX it is still producing an error

                             

                            SQL>    insert /*+ skip_unq_unusable_idx */ into t1 select rownum from dual connect by level <= 3;

                               insert /*+ skip_unq_unusable_idx */ into t1 select rownum from dual connect by level <= 3

                            *

                            ERROR at line 1:

                            ORA-01502: index 'xxxx.SYS_C0092000' or partition of such index is in unusable state

                             

                            Mohamed Houri

                            Best Regards

                             

                             

                             

                            Ce message a été modifié par : Mohamed Houri

                            • 11. Re: ORA-01502 when skip_unusable_indexes=TRUE
                              Randolf Geist

                              Hi Mohamed,

                               

                              that's interesting, are you sure you followed the script?

                               

                              Here's a cut & paste from a plain vanilla 11.2.0.3 installation (no patches applied):

                               

                              SQL> select * from v$version;

                               

                              BANNER

                              --------------------------------------------------------------------------------

                              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                              PL/SQL Release 11.2.0.3.0 - Production

                              CORE    11.2.0.3.0      Production

                              TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

                              NLSRTL Version 11.2.0.3.0 - Production

                               

                              5 rows selected.

                               

                              SQL>

                              SQL> drop table append_test purge;

                              drop table append_test purge

                                         *

                              ERROR at line 1:

                              ORA-00942: table or view does not exist

                               

                               

                              SQL>

                              SQL> -- blank sample table

                              SQL> create table append_test as

                                2  select

                                3  trunc(sqrt(rownum-1)) as skewed_data,

                                4  rownum-1 as id,

                                5  lpad(rownum-1,10) id_char,

                                6  rpad('x',50, 'x') as filler

                                7  from

                                8  all_objects

                                9  where 1 = 2;

                               

                              Table created.

                               

                              SQL>

                              SQL> -- create non-unique index

                              SQL> create index append_test_pk on append_test(id);

                               

                              Index created.

                               

                              SQL>

                              SQL> -- add primary key constraint

                              SQL> alter table append_test add constraint pk_append_test primary key (id);

                               

                              Table altered.

                               

                              SQL>

                              SQL> -- same applies to unique constraint

                              SQL> -- alter table append_test add constraint uq_append_test unique (id);

                              SQL>

                              SQL> -- make the index unusable

                              SQL> alter index append_test_pk unusable;

                               

                              Index altered.

                               

                              SQL>

                              SQL> -- now perform a direct-path insert

                              SQL> insert /*+ append */ into append_test

                                2  select

                                3  trunc(sqrt(rownum-1)) as skewed_data,

                                4  1 as id,

                                5  --rownum-1 as id,

                                6  lpad(rownum-1,10) id_char,

                                7  rpad('x',50, 'x') as filler

                                8  from

                                9  all_objects

                              10  where rownum <= 100;

                               

                              100 rows created.

                               

                              SQL>

                              SQL> -- this generates an error

                              SQL> -- and therefore shows that this

                              SQL> -- was a direct-path insert

                              SQL> select * from append_test;

                              select * from append_test

                                            *

                              ERROR at line 1:

                              ORA-12838: cannot read/modify an object after modifying it in parallel

                               

                               

                              SQL>

                              SQL> -- now we have non-unique data

                              SQL> -- in the table

                              SQL> -- although the primary key

                              SQL> -- constraint is enabled and

                              SQL> -- validated

                              SQL> commit;

                               

                              Commit complete.

                               

                              SQL>

                              SQL> -- try the same using conventional insert

                              SQL> insert /*+ noappend */ into append_test

                                2  select

                                3  trunc(sqrt(rownum-1)) as skewed_data,

                                4  1 as id,

                                5  --rownum-1 as id,

                                6  lpad(rownum-1,10) id_char,

                                7  rpad('x',50, 'x') as filler

                                8  from

                                9  all_objects

                              10  where rownum <= 100;

                              insert /*+ noappend */ into append_test

                              *

                              ERROR at line 1:

                              ORA-01502: index 'CBO_TEST.APPEND_TEST_PK' or partition of such index is in unusable state

                               

                               

                              SQL>

                              SQL> -- rebuild the index

                              SQL> alter index append_test_pk rebuild;

                               

                              Index altered.

                               

                              SQL>

                              SQL> -- attempt to re-validate the constraint

                              SQL> alter table append_test modify constraint pk_append_test novalidate;

                               

                              Table altered.

                               

                              SQL>

                              SQL> -- fails due to duplicates

                              SQL> alter table append_test modify constraint pk_append_test validate;

                              alter table append_test modify constraint pk_append_test validate

                              *

                              ERROR at line 1:

                              ORA-02437: cannot validate (CBO_TEST.PK_APPEND_TEST) - primary key violated

                               

                               

                              SQL>

                              SQL> spool off

                              SQL>

                              SQL>


                              So as you can see - the direct path inserts succeeds, the index rebuild, too, but the re-validation of the constraint fails, as posted in the original note.

                               

                              In 11.2.0.4 / 12.1 the direct-path insert fails as shown above.

                               

                              It would be interesting to know why you get the error message in 11.2.0.3, that I don't get.

                               

                              Randolf

                              • 12. Re: ORA-01502 when skip_unusable_indexes=TRUE
                                Mohamed Houri

                                Randolf

                                 

                                Here my spool file. Please let me know where I have missed someting

                                 

                                SQL> select * from v$version;

                                 

                                BANNER

                                --------------------------------------------------------------------------------

                                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                                PL/SQL Release 11.2.0.3.0 - Production

                                CORE 11.2.0.3.0 Production

                                TNS for Linux: Version 11.2.0.3.0 - Production

                                NLSRTL Version 11.2.0.3.0 - Production

                                 

                                 

                                SQL> drop table append_test purge;

                                 

                                Table dropped.

                                 

                                SQL>  create table append_test as

                                  2      select

                                  3      trunc(sqrt(rownum-1)) as skewed_data,

                                  4      rownum-1 as id,

                                  5      lpad(rownum-1,10) id_char,

                                  6      rpad('x',50, 'x') as filler

                                  7      from

                                  8      all_objects

                                  9      where 1 = 2;

                                 

                                Table created.

                                 

                                SQL>

                                SQL> create index append_test_pk on append_test(id);

                                 

                                Index created.

                                 

                                SQL>

                                SQL> alter table append_test add constraint pk_append_test primary key (id);

                                 

                                Table altered.

                                 

                                SQL>

                                SQL> alter index append_test_pk unusable;

                                 

                                Index altered.

                                 

                                SQL>

                                SQL>  insert /*+ append */ into append_test

                                  2      select

                                  3      trunc(sqrt(rownum-1)) as skewed_data,

                                  4      1 as id,

                                  5      --rownum-1 as id,

                                  6      lpad(rownum-1,10) id_char,

                                  7      rpad('x',50, 'x') as filler

                                  8      from

                                  9      all_objects

                                10   where rownum <= 100;

                                insert /*+ append */ into append_test

                                *

                                ERROR at line 1:

                                ORA-26028: index xxxx.APPEND_TEST_PK initially in unusable state


                                Best regards

                                Mohamed Houri

                                • 13. Re: ORA-01502 when skip_unusable_indexes=TRUE
                                  Randolf Geist

                                  So - is this a plain vanilla 11.2.0.3 installation, or do you have patches / PSUs or similar installed?

                                   

                                  My guess is that there is a patch available for 11.2.0.3 that includes the fix - as I said, mine is a plain vanilla 11.2.0.3 without any patches installed.

                                   

                                  Randolf

                                  • 14. Re: ORA-01502 when skip_unusable_indexes=TRUE
                                    Randolf Geist

                                    Mohamed Houri wrote:

                                     

                                    Randolf

                                     

                                    Here my spool file. Please let me know where I have missed someting

                                     

                                    Mohamed,

                                     

                                    I think the explanation for our different results is very simple: You've obviously run the test case with "SKIP_UNUSABLE_INDEXES" set to FALSE - hence you get the (different) error message. The point of the test case was however to run with the default value of SKIP_UNUSABLE_INDEXES set to TRUE, although it's not explicitly set in the test case and not mentioned in the note - clearly an omission on my side.

                                     

                                    Randolf

                                    1 2 Previous Next