Re: UNNecessary indexes ??

There is no snap answer to this question. I can build (without any devious tricks) an example where:

    t1 is defined as (n1, n2, other)
    t1_idx1 is defined as t1(n1)
    t1_idx2 is defined as t2(n1, n2)

Select other from t1 where n1 = 99 and n2 = 99 uses index t1_idx1, and is quick

DROP index t1_idx1

Select other from t1 where n1 = 99 and n2 = 99 does a full tablescan
select /*+ index(t1, t1_idx2) */ ...

    works and is quick.

The effect is down to anomalies in the way Oracle generates the index clustering_factor that it uses to calculate the cost of a range scan.

In this case, I would drop t1_idx1 and use dbms_stats.set_index_stats to adjust
Oracle's calculated clustering_factor to be a better indication of the reality.
But you have to know the data to be able to do this.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June 4th - 6th
____Australia_June 18th - 20th (Perth)
____Australia_June 23rd - 25th (t.b.a)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 15 May 2003 19:51

> Hello All,
> We have a table for (eg) say
> table x with 4 number cols (x1,x2,x3,x4)..
>
> There is a pk constraint on x1 an a unique key constraint on x2,x3
> Now we also have non-unique indexes on
> index x2_idx on col x2
> index x2_x4_idx on col x2,x4
>
> I would presume that index x2_idx alone is not needed as the unique
index
> or index x2_x4_idx will satisfy
> queries with col x2..
>
> My question is can i also eliminate index x2_x4_idx ??
> If i have query with the where clause x2 = 333 and x4 = 444 then
would
> using index x2_x4_idx be good or
> using unique index be a good choice , since CBO does a index range
scan
> while using both the indexes...
> I am trying to eliminate unnecessary indexes created by developers
....
>
> Thanks,
>
> Sathish
>
>
> --
> http://www.fastmail.fm - Choose from over 50 domains or use your own
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: sat0789_at_fastmail.fm
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
services
> --------------------------------------------------------------------
-
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).