Re: Tuning "INSERT as SELECT"
Date: Thu, 2 Feb 2017 15:28:43 +0000
Message-ID: <DF4PR84MB020445DA024CBE602818E80ACC4C0_at_DF4PR84MB0204.NAMPRD84.PROD.OUTLOOK.COM>
Prem, "UNUSABLE" basically means one of the following 1- the index row entries no longer point to the correct location of the base table rows, the index row entries point to non-existent rows, or there are table rows with no entry in the index when there should be an entry. If you TRUNCATE the table non of these conditions is true and on insert to the table the indexes will be maintained so the indexes are USABLE, that is, the index entries are valid.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Prem Khanna J <jprem_at_outlook.com> Sent: Thursday, February 2, 2017 9:12:32 AM To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: Tuning "INSERT as SELECT"
Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.
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.
Regards,
Prem
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 02 2017 - 16:28:43 CET