Re: explain plan: Which index columns are used ?

From: <michael_bialik_at_my-deja.com>
Date: Wed, 09 Feb 2000 21:27:30 GMT
Message-ID: <87sm3p$hc$1@nnrp1.deja.com>

Hi.

 There is some indirect method to know the number of used columns (  matching columns in DB2 terms ) if you are using COST BASED optimizer:

 Num_rows / Value_of_CARDINALITY_field_of_PLAN_table =
  Num_of_distinct_values_of_first_index_field *
  Num_of_distinct_values_of_second_index_field *

  ...
  Num_of_distinct_values_of_N_th_index_field.

  N is the number of matching columns.

 Usually it works, but Oracle CBO often "lies" and puts incorrect  value in CARDINALITY field so you are back at the start line...

 HTH. Michael.

In article <950038307.5632.0.nnrp-09.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Unfortunately not.
> There is a column in the plan_table called
> SEARCH_COLUMNS which sounds so
> promising, but it has never been implemented.
>
> However, given the execution path, you should
> be able to infer from the SQL which columns will
> have been 'usefully' used and which were used
> as a result of an index scan.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Gilles Cordier wrote in message <87p7k7$gm9$1_at_nnrp1.deja.com>...
> >
> >
> >Hi
> >
> >With explain plan, I know which indexes are used in a query.
> >
> >But in case of a concatenated index, is there a way to know which
> >columns are used ?
> >
> >
> >Thanks for your help,
> >
> >Gilles
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 09 2000 - 15:27:30 CST