Search code examples
oracle-databasesql-insertoracle9i

Oracle insert in index table:Time to load 500 thousand rows is more than inserting 16 million rows

At first I tried normal insert into target table from temporary table.

INSERT /*+ APPEND */ INTO RDW10DM.INV_ITEM_LW_DM
SELECT
    *
FROM
    RDW10PRD.TMP_MDS_RECLS_INV_ITEM_LW_DM
;
COMMIT;

It tooks only 17 min to load.Total count in temp table TMP_MDS_RECLS_INV_ITEM_LW_DM is 16491650. Plan for Execution:

--------------------------------------------------------------------------------------
| Id  | Operation            |  Name                         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |                               |    16M|  1290M|  4927 |
|   1 |  LOAD AS SELECT      |                               |       |       |       |
|   2 |   TABLE ACCESS FULL  | TMP_MDS_RECLS_INV_ITEM_LW_DM  |    16M|  1290M|  4927 |
--------------------------------------------------------------------------------------
Note: cpu costing is off

Then I tried to load loc wise:

INSERT /*+ APPEND */ INTO RDW10DM.INV_ITEM_LW_DM
SELECT
        *
FROM
     RDW10PRD.TMP_MDS_RECLS_INV_ITEM_LW_DM
     where LOC_KEY=222
;
COMMIT;

Then it tooks around 28 min to load. Total count in temp table with filter is 493465

Plan for execution:

--------------------------------------------------------------------------------------
| Id  | Operation            |  Name                         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |                               |   492K|    38M|  4927 |
|   1 |  LOAD AS SELECT      |                               |       |       |       |
|*  2 |   TABLE ACCESS FULL  | TMP_MDS_RECLS_INV_ITEM_LW_DM  |   492K|    38M|  4927 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TMP_MDS_RECLS_INV_ITEM_LW_DM"."LOC_KEY"=222)

Note: cpu costing is off

Index in Target table: enter image description here

Does anyone has any idea why this is happening?

Solution

  • Thank everyone for your valuable thoughts.

    I found the actual problem later. Since I have doing frequent truncate and load in target table RDW10DM.INV_ITEM_LW_DM so index pages might have fragmented. So, ran query after rebuilding indexes and got expected results.