8 Replies Latest reply: May 2, 2013 4:07 PM by Martin Preiss RSS

    PK -index

    970021
      I have to load a table with 20 million records into a table which maintains history (32 columns). I have a primary key based on a surrogate key. Because I'm loading so many records, I would like to drop the index to make the load go faster. But then re-creating the index takes a long time. What is the best practice in this case?

      I'm on Oracle 11.2
        • 1. Re: PK -index
          jeneesh
          Aktom:Huge Insert

          See Tom's first followup..
          • 2. Re: PK -index
            Tubby
            967018 wrote:
            I have to load a table with 20 million records into a table which maintains history (32 columns). I have a primary key based on a surrogate key. Because I'm loading so many records, I would like to drop the index to make the load go faster. But then re-creating the index takes a long time. What is the best practice in this case?

            I'm on Oracle 11.2
            http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes002.htm#i1006258

            and

            http://docs.oracle.com/cd/E11882_01/server.112/e17120/tables004.htm

            However only you know your process, concurrency concerns, etc... so you'll have to work that knowledge into the information that you find in the documentation above.

            Cheers,
            • 3. Re: PK -index
              970021
              The problem is that I made the index unusable to speed the load, but I am getting ORA-01502 because its a unique index and primary key what I made unusable.
              • 4. Re: PK -index
                jeneesh
                Have you tried disabling the index partition, loading, and rebuilding that partition-As Tom advices?

                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1415454871121#60074290411043
                • 5. Re: PK -index
                  Martin Preiss
                  it's possible to defer the evaluation of a unique (or PK) constraint - but sadly it's not possible to defer the evaluation of a unique index: you will always get ora-1502. You can use a nonunique index to enforce a unique constraint, but there are some (small) penalties (starting with the increase of 1 LIO for every index access).

                  To speed up the recreation of an index you can use nologging and/or do the operation in parallel (if you have sufficient resources to do parallel operations there).
                  • 6. Re: PK -index
                    Mohamed Houri
                    I have to load a table with 20 million records into a table which maintains history (32 columns). 
                    I have a primary key based on a surrogate key. Because I'm loading so many records, 
                    I would like to drop the index to make the load go faster. But then re-creating the index takes a long time.
                    What is the best practice in this case? 
                    If

                    1. you have no trigger on the table
                    2. you have no integrity constraint on the table
                    3. you are not deleting (after the insert) from the table

                    Then

                    you can consider direct path load i.e
                        insert /*+ append */ into table select col1, col2 from target_table where ...;
                        
                    End if;

                    This will speed up the insert and maintain the indexes very efficiently as far as it will collect data and bulk insert them into the existing indexes

                    Best regards
                    Mohamed Houri
                    www.hourim.wordpress.com
                    • 7. Re: PK -index
                      970021
                      What are the advantages/ disadvantages of the two options below?

                      1. disable the primary key constraint, and enabling it after the load, which will re-create the unique index.
                      2. create a non-unique index on the pk and make it unusable. Disable the constraint. Then after the load rebuild the non unique index and enabling the constraint (primary key).

                      This table is large and contains history. And the load is large as well.
                      • 8. Re: PK -index
                        Martin Preiss
                        if your table is not partitioned then the unique index won't bring you much benefit: it is less efficient than a unique index and the rebuild will be equally expensive. If your table is partitioned and you have a local non-unique index that can be used to support the PK constraint then you can disable the constraint, mark the relevant index partitions as unusable and rebuild the partitions without hitting ora-1502. So you get some administrative benefits (i.e. you could avoid rebuilding on index partitions that are not affected by the insert operation) but a sligthly less efficient access when you use the local non-unique index. This could be a good idea if you do the big loads frequently and the inserts do not affect all the partitions.