Re: Tuning "INSERT as SELECT"
- From: Prem Khanna J <jprem@xxxxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Feb 2017 14:12:32 +0000
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.
Regards,
Prem
Other related posts:
