Re: 10g explain plan detemination without actually doing change in the query...
Date: Tue, 22 Sep 2009 10:45:02 -0700 (PDT)
Message-ID: <214c133d-726b-499c-8af1-88c3e6ac3c40_at_b25g2000prb.googlegroups.com>
On Sep 22, 3:07 am, UXDBA <unixdb..._at_googlemail.com> wrote:
> All,
>
> I would like to know
>
> in Oracle 10.2.0.4:
> if I have below SQL:
>
> select col1,col2 from t where col1=? and col3?
>
> col13 & col1 both are indexed.
>
> This query will have certain execution plan.
In case Charles' and David's answers didn't make it clear, that is wrong.
>
> Now if I make alteration on col3 index . i.e. I drop the index on col3
>
> Then I may have different exectuin plan of the query.
>
> Question :
>
> Is it poosible to find out in 10g to know what execution plan query
> will have after dropping index WITHOUT actually dropping the index?
Since it can change without dropping the index due to data volume and distribution, among other things, no. The two reasons you get and keep runtime plans are to see what changed when things start running bad, and to check that Oracle's way of accessing the data is the same as your idea of how the data should be accessed.
You appear to be assuming Oracle uses a rule-based optimizer, and that is not the case. Please see the OracleŽ Database Performance Tuning Guide chapter about the optimizer.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/sep/21/bn21employee-admits-stealing/?metro&zIndex=169867Received on Tue Sep 22 2009 - 12:45:02 CDT