Cost for Insert Statement...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Cost for Insert Statement...

  1. #1
    Join Date
    Jan 2002
    Posts
    148
    hi,

    please click this link to see the explain plan for the insert statement.

    http://us.f1.yahoofs.com/users/30a3c...M2j_8AwetM899C

    Is there any possibility to reduce the cost for this.

    - A unique key exists on the table.
    - Table is analyzed with estimate stats for 20 percent.
    - Total Records right now 45M +.


    Thanx in Advance.
    Jr.


  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I don't know if these suggestions will actually reduce the explain plan's cost #, but ..

    Are there other indexes on the table? Key writing will slow things down a bit. I wonder if the cost # goes down if there are fewer number of indexes to build on insert?

    Add more CPU's and increase parallelism .. not sure if that will actually reduce the cost #, but it will insert faster .. i/o permitting.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Jan 2002
    Posts
    148
    Thanx Ken,

    here is a small test run output ...

    http://us.f1.yahoofs.com/users/30a3c...Pek_8ASzDCm4Yz

    - Could Reduce the Cost with NO PX.
    - But Still Cost is about 2000+
    - There is only one index on the Table and thats the Unique Key index.
    - Not sure the affect of PX when trying to insert 1 Record in context with Time.

    Thanx
    Jr.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How to load huge data in a table:

    Increase freelist for the table. Create necessary extents for the table on different datafiles.

    Set the table into nologging mode.

    Turn off ARCHIVE LOG.

    Drop or disable the index before a load.

    After the insert, recreate indexes.

  5. #5
    Join Date
    Jan 2002
    Posts
    148
    Originally posted by tamilselvan
    How to load huge data in a table:

    Increase freelist for the table. Create necessary extents for the table on different datafiles.

    Set the table into nologging mode.

    Turn off ARCHIVE LOG.

    Drop or disable the index before a load.

    After the insert, recreate indexes.
    - The table is already set in NOLOGGING mode
    - Freelists and Initrans are enough set.
    - System is in NOARCHIVE LOG Mode
    - Enough big Preallocated Extents are available
    - And inserts are mandatory dependent on the Unique constraint hence cannot drop and recreate.

    So, are there any corners i should look into.

    Thanx
    Jr.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Then don't concern it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width