Insert using /*+ append */ hint not reduced my time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Insert using /*+ append */ hint not reduced my time

Hybrid View

  1. #1
    Join Date
    Jun 2005
    Posts
    12

    Insert using /*+ append */ hint not reduced my time

    Hi,
    I have given Append hint in the insert query which is loding data from remote DB,eventhough the time taken by the insert query is not reduced.
    The table in which I am inserting is not having more than 2 indexes.
    There select statment with union all is loading data from remote DB which is inserting 29000 records.Explain plan is using nested loops and index scan.
    the select alone is taking seconds. but the insert is taking 2 mitues.

    thanks in advance
    mallma

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Drop all the indexes and then try the insert append.Otherwise i dont think so that direct path insert even works.

    regards
    Hrishy

  3. #3
    Join Date
    Jun 2005
    Posts
    12
    Hi,
    I tried removing the indexes, even its taking the same time.
    any other go?

    thanks.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You can try all sorts of different tweaks, but until you trace the process you don't have a clue as to what is taking the most time. With the +APPEND hint maybe you are taking a query that takes 3 minutes down to 2.5 minutes. Is it really worth it? Trace it.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Last edited by marist89; 07-01-2005 at 12:37 PM.

  6. #6
    Join Date
    Jul 2005
    Location
    INDIA
    Posts
    1

    slow direct inserts

    Try following
    (1) Put table in nologging mode ( it will suppress redo generation for this table).
    (2) make next extent size large if currently it is small.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    insert /*+append*/ IS nologging effectively no redo is generated for the table

    next extent advice is just stupid and wrong

  8. #8
    Join Date
    Jun 2005
    Posts
    12
    Hi,
    Are you talking about the NOLOGGING in the create table command?.
    If so then its is given in the Create script of the table itself.

    if no please advice

    Thanks in advance.
    mallma

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by mallma
    Hi,
    I have given Append hint in the insert query which is loding data from remote DB,eventhough the time taken by the insert query is not reduced.
    The table in which I am inserting is not having more than 2 indexes.
    There select statment with union all is loading data from remote DB which is inserting 29000 records.Explain plan is using nested loops and index scan.
    the select alone is taking seconds. but the insert is taking 2 mitues.

    thanks in advance
    mallma

    I would guess that fetch of 30K records from remote DB.. is taking most of ur time.. you can do a simple test for urself and check it..

    Wass ur Arraysize? ( it does matter for data over n/w )

    @Hrishy,

    Append indeed works if even indexes are there.. but only for table.. for indexes its a usual way to have redo recorded.. And again here your suggestion without even thinking?

    Rgds
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Quote Originally Posted by abhaysk
    I would guess that fetch of 30K records from remote DB.. is taking most of ur time.. you can do a simple test for urself and check it..

    Wass ur Arraysize? ( it does matter for data over n/w )

    @Hrishy,

    Append indeed works if even indexes are there.. but only for table.. for indexes its a usual way to have redo recorded.. And again here your suggestion without even thinking?

    Rgds
    Abhay.
    I always drop or disable indexes on the table where i am doing a dataload and rebuild those indexes in parallel and nologging mode.Thats why i said i think so..wasnt really confident :-)

    regards
    Hrishy

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