CTE With Insert In Oracle

Question

i am running a query in oracle with CTE. When i execute the query it works fine in select statement but when i use insert statement it takes ample of time to execute.Any help here is the code

INSERT INTO port_weeklydailypricesTest (co_code,start_dtm,end_dtm)
   SELECT * FROM
        (
            WITH CTE(co_code, start_dtm, end_dtm) AS
            (
                SELECT co_code                                                     ,
                CAST(NEXT_DAY(MIN(dlyprice_date),'FRIDAY')-6  AS DATE)   start_dtm ,
                CAST(NEXT_DAY(MIN(dlyprice_date),'FRIDAY') AS DATE)      end_dtm
                FROM   feed_dlyprice
                GROUP BY co_code
                UNION ALL
                SELECT  co_code     ,
                CAST(TO_CHAR(end_dtm + INTERVAL '1' DAY,'DD-MON-YYYY') AS DATE),
                CAST(TO_CHAR(end_dtm + INTERVAL '7' DAY,'DD-MON-YYYY') AS DATE)
                FROM    CTE
                WHERE   CAST(end_dtm AS DATE) <= TO_CHAR(TO_DATE(SYSDATE+1,'DD-MON-YYYY'))
             )
            SELECT co_code,start_dtm,end_dtm 
            FROM CTE
        );

Answer 1

If, as you say, the performance of the SELECT on its own is satisfactory the problem must lie with the INSERT part of the statement.

There are a number of things which might cause an insert to run slow:

• The most likely is the presence of a trigger on the target table which executes something very expensive.
• Another possibility is that the insert is waiting on a locked resource (say some other process has an exclusive table level lock on the target table, or some other shared resource such as a code control table).
• it could be a storage allocation issue, chaining or row migration, too many indexes or lots of derived columns.
• perhaps it is down to hardware - underpowered network, dodgy interconnects, a bad disk.

This is by no means exhaustive. The items at the top are application issues which you should be able to investigate and resolve. The further down the list you go the more likely it is that you will need the assistance on an on-site DBA.