Re: 10g explain plan detemination without actually doing change in the query...

From: ddf <oratune_at_msn.com>
Date: Tue, 22 Sep 2009 06:05:19 -0700 (PDT)
Message-ID: <925ab51e-8471-433b-89e3-d6a0107a381f_at_o35g2000vbi.googlegroups.com>

On Sep 22, 5: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

Which index is used depends upon which of the two indexes is the more selective; you may not be using the col3 index with that query:

SQL> create table t(

  2          col1 number,
  3          col2 varchar2(40),
  4          col3 varchar2(10)

5 );

Table created.

Elapsed: 00:00:00.09
SQL>
SQL> create index t_col1_idx
2 on t(col1);

Index created.

Elapsed: 00:00:00.01
SQL>
SQL> create index t_col3_idx
2 on t(col3);

Index created.

Elapsed: 00:00:00.01
SQL>
SQL> begin

  2          for i in 1..100000 loop
  3                  if mod(i, 40) = 0 then
  4                          insert into t
  5                          values (i, 'Testing '||i, 'FALSE');
  6                  elsif mod(i, 78) = 0 then
  7                          insert into t
  8                          values (i, 'Testing '||i, 'MAYBE');
  9                  elsif mod(i, 333) = 0 then
 10                          insert into t
 11                          values (i, 'Testing '||i, 'ALWAYS');
 12                  else
 13                          insert into t
 14                          values(i, 'Testing '||i, 'WHY NOT');
 15                  end if;
 16
 17          end loop;
 18
 19          commit;

20
21 end;
22 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.17

SQL>
SQL> set autotrace on
SQL>
SQL> select col1, col2

  2 from t
  3 where col1 = 888
  4 and col3 = 'WHY NOT';

COL1 COL2

---------- ----------------------------------------
       888 Testing 888

Elapsed: 00:00:00.04

Execution Plan

Plan hash value: 1536306675

| Id  | Operation                   | Name       | Rows  | Bytes |
Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     8 |   336 |
2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |     8 |   336 |
2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_COL1_IDX |     8 |       |

1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter("COL3"='WHY NOT')
2 - access("COL1"=888)

Note

dynamic sampling used for this statement

Statistics

         72  recursive calls
         16  db block gets
         79  consistent gets
          0  physical reads
        972  redo size
        474  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off

SQL>
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:02.68
SQL>
SQL> begin

  2          for i in 1..100000 loop
  3                  if mod(i, 40) = 0 then
  4                          insert into t
  5                          values (mod(i, 40), 'Testing '||i,
'FALSE');
  6                  elsif mod(i, 78) = 0 then
  7                          insert into t
  8                          values (mod(i, 78), 'Testing '||i,
'MAYBE');
  9                  elsif mod(i, 333) = 0 then
 10                          insert into t
 11                          values (mod(i, 333), 'Testing '||i,
'ALWAYS');
 12                  else
 13                          insert into t
 14                          values(i, 'Testing '||i, 'WHY NOT');
 15                  end if;
 16
 17          end loop;
 18
 19          commit;

20
21 end;
22 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.53

SQL>
SQL> set autotrace on
SQL>
SQL> select col1, col2

  2 from t
  3 where col1 = 888
  4 and col3 = 'WHY NOT';

COL1 COL2

---------- ----------------------------------------
       888 Testing 888

Elapsed: 00:00:00.01