Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Unhappy Unanswered: INSERT INTO Select * from..

    This Insert statement is taking too much time (it didn't even finish in 1 hours), however the SELECT query fetches entire set of all rows (not FIRST_ROWS) in approx 3 mins:

    INSERT INTO GT_A_TOTALS
    SELECT DISTINCT vat.*
    FROM v_a_totals vat,
    (SELECT DISTINCT orig, p_date
    FROM t_m_advice) ad
    WHERE vat.orig = ad.orig
    AND ad.p_date BETWEEN '01-jun-2006' and '30-jun-2006';

    Any idea why?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Could be for a variety of reasons, i.e. there is heavy loading on your redo logs which means the inserts themselves take a long time, or maybe there are a lot of indexes on gt_a_totals table etc.

    You need to trace the statement to get a better idea of where it is slow.

    Alan

  3. #3
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52
    There is no index on this gt_a_totals table. However, I think there is a heavy loading on the redo logs. This query fetches around 18,500 rows.

    Anyways, thanks for your suggestions Alan.

    Cheers,
    Gautam

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You need to try and remove the 'DISTINCT's, see if this works:
    PHP Code:
    INSERT INTO GT_A_TOTALS 
    SELECT vat
    .*
      
    FROM v_a_totals vat
     WHERE EXIST 
    (
         
    SELECT 1
           FROM t_m_advice ad
          WHERE vat
    .orig ad.orig
            
    AND p_date BETWEEN '01-jun-2006' AND '30-jun-2006')
     ; 

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    He just said the query runs for 3 minutes along.

    I doubt you have a problem with your redo logs (18,500 rows is just way too small).

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I don't like that "distinct *" thing.
    You might consider truncating p_date trunc(p_date)
    this will ensure you get every row for the month in question.

    how long does this take to run?
    PHP Code:
    create table test_a as
    SELECT DISTINCT vat.*
    FROM v_a_totals vat,
    (
    SELECT DISTINCT origp_date
    FROM t_m_advice
    ad
    WHERE vat
    .orig ad.orig
    AND ad.p_date BETWEEN '01-jun-2006' and '30-jun-2006'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    There must be a problem when inserting as the select is only taking 3 minutes. When I earlier said heavy redo loading what I meant was other database activity (by other sessions) was causing redo log writing to be a bottleneck. What size are your redo logs (and buffer) and how often do they switch? Also check if there are locking issues or possibly a disk problem (check your OS logs).

    Alan

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by LKBrwn_DBA

    You need to try and remove the 'DISTINCT's, see if this works:
    PHP Code:
    INSERT INTO GT_A_TOTALS 
    SELECT vat
    .*
      
    FROM v_a_totals vat
     WHERE EXIST 
    (
         
    SELECT 1
           FROM t_m_advice ad
          WHERE vat
    .orig ad.orig
            
    AND p_date BETWEEN '01-jun-2006' AND '30-jun-2006')
     ; 


    Did you try insert /*APPEND*/ hint.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by PMASchmed
    Did you try insert /*APPEND*/ hint.
    table would have to be nologging right?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Actually, the Oracle docs says this:

    The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

    In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
    If it were me, I'd create a script to:

    1) ALTER TABLE GT_A_TOTALS NOLOGGING;

    2) INSERT INTO GT_A_TOTALS
    SELECT vat.*
    FROM v_a_totals vat
    WHERE EXIST (
    SELECT 1
    FROM t_m_advice ad
    WHERE vat.orig = ad.orig
    AND p_date BETWEEN '01-jun-2006' AND '30-jun-2006');

    3) ALTER TABLE GT_A_TOTALS LOGGING;

    Of course, if you are not running the Enterprise Edition, I'd also try the /*+ APPEND */ hint.
    JoeB
    save disk space, use smaller fonts

  11. #11
    Join Date
    May 2006
    Posts
    132
    Quote Originally Posted by The_Duck
    table would have to be nologging right?
    That's correct.

    joebednarz, it looks like you omitted the /*+ APPEND */ hint from your INSERT statement ??
    Last edited by ebrian; 09-27-06 at 15:59.

  12. #12
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    He also missed a step, which is

    4) Backup the table.


  13. #13
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    JMartinez, you are absolutely correct... if you put the table in "NOLOGGING" mode, you have no way to recover it if something goes wrong.

    So... my revised steps would put

    1) Backup the table.

    ... everything else.
    JoeB
    save disk space, use smaller fonts

  14. #14
    Join Date
    May 2006
    Posts
    132
    JMartinez meant to backup the table AFTER the INSERT. Backing the table up BEFORE the insert won't help with recovering the new data loaded. And as mentioned above, the insert needs to include the /*+ APPEND */ hint.

  15. #15
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I can see that, however, since the additional data already exists in another place (SELECT * FROM v_a_totals), in order to recover (i.e., start over) you would need to restore the receiving table (GT_A_TOTALS) to its original state and restart the INSERT.

    Of course, only in my opinion (which by definition is only important to me...)
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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