Re: Tuning "INSERT as SELECT"

From: Prem Khanna J <>
Date: Thu, 2 Feb 2017 14:12:32 +0000
Message-ID: <>

Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.

>>Did you really select and fetch 80 million rows ? or was it the first few, or a count(*) of an inline view, or what ?

You are on spot . we did a "select count(*) from inline view" :-( Also tested with "+all_rows" hint in sql*developer thinking that might help , but now I feel like that's also not the right way of testing as sql*developer stops with first 50~100 rows. Will let the exact SQL run all the way in sql*plus and see long it takes.Just curious - is there any other way of doing it (don't want to see all the 80m recs in sql*plus) or how would you guys do it ?

>> ... your test starts with a truncate then it will have made the indexes valid again.

Exaclty. we made indexes "unusable" , truncated the table before I ran my tests. Did not imagine that truncate would my indexes valid. Just checked and it was so. Shocked !! Wonder why truncate does so. Thanks a million Jonathan for letting me know that.

>> please turn on SQL tracing using DBMS_MONITOR or .... Everything else is pure witchcraft.
Sure Mladen. Thanks again for the valuable piece of advice.

Will continue with my tests and keep you guys posted.


Received on Thu Feb 02 2017 - 15:12:32 CET

Original text of this message