Re: 10g explain plan detemination without actually doing change in the query...
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 22 Sep 2009 05:50:17 -0700 (PDT)
Message-ID: <4ccc6082-ef71-40ee-a594-d6941eee7811_at_p15g2000vbl.googlegroups.com>
On Sep 22, 6: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.
>
> 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?
>
> Regards
Date: Tue, 22 Sep 2009 05:50:17 -0700 (PDT)
Message-ID: <4ccc6082-ef71-40ee-a594-d6941eee7811_at_p15g2000vbl.googlegroups.com>
On Sep 22, 6: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.
>
> 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?
>
> Regards
Look into specifying a NO_INDEX hint for the SQL statement: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50411
Note that if the query uses bind variables the execution plan displayed by autotrace and explain plan may differ from the actual execution plan.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Sep 22 2009 - 07:50:17 CDT