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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Prem Khanna J <jprem@xxxxxxxxxxx>
Sent: Thursday, February 2, 2017 9:12:32 AM
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: Tuning "INSERT as SELECT"
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 ?
... your test starts with a truncate then it will have made the indexes
valid again.
Sure Mladen. Thanks again for the valuable piece of advice.please turn on SQL tracing using DBMS_MONITOR or .... Everything else is
pure witchcraft.