Re: Serious pb with INDEX

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 27 Jan 2003 16:55:25 -0000
Message-ID: <b13ocr$d44$1$830fa7b3@news.demon.co.uk>

The index_histogram is telling you that if you count keys which just one copy you
will find 264911 of them - i.e. it is describing exactly what you expect.

You don't say which version of Oracle you are using, and the options the optimizer has for unnesting subqueries varies dramatically between releases. As a 'version neutral' way of making your example quicker, you
could try unnesting the subquery by hand

SELECT (...)
FROM

            (
            SELECT MAX(D2.km_denorm) km_denorm
            FROM XXX D2
            WHERE D2.VEHID=KM.VEHID
            )        v,
            XXX KM

WHERE KM.km_denorm = v.km_denorm

One problem that you may be facing relates to your uses of non-date columns to hold date-like values, and then using a single column to hold two fields.

This sort of thing can cause significant problems to the optimizer when working with ranges. In particular the non-random spread of the data can result in Oracle's estimates of row access requirements being way off and therefore precluding the use of indexes.

--
Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


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


Spendius wrote in message ...


>Hi,

>How do you explain this: I have a table XXX

>SQL> desc XXX

> Name                         Null?    Type

> ---------------------------- -------- ------------------

> ...

> KM_DENORM                             NUMBER(18)

>

>that contains 264911 rows. This field above is indexed because *it

>contains 264911 different values* (=> count(1)/count(distinct

km_denorm)


>= 1). Yet when I do an ANALYZE INDEX VALIDATE STRUCTURE, I get:

>

>REPEAT_COUNT KEYS_WITH_REPEAT_COUNT

>------------ ----------------------

>           0                      0

>           1                 264911

>           2                      0

>           3                      0

>           4                      0

>       [...]

>          15                      0

>

>with SELECT * FROM index_histogram. How do you explain this ? My

index


>is NEVER used, there's obviously no way of forcing the optimizer to

use


>it (whatever the INDEX() hint I use). For ex. I'd like to be able to

get


>a very quick answer to (the other index on XXX.VEHID works fine):

>SELECT (...)

>FROM XXX KM

>WHERE KM.km_denorm = (SELECT MAX(D2.km_denorm)

>                      FROM XXX D2

>                      WHERE D2.VEHID=KM.VEHID)

>

>The values look like the following:

>SQL> select min(KM_DENORM), max(km_denorm) from XXX;

>

>          KM_DENORM           KM_DENORM

>------------------- -------------------

>     19720101009089      20021217272582

>

>

>Any idea ?

>Thanks a lot...

>Sp

Received on Mon Jan 27 2003 - 10:55:25 CST

Re: Serious pb with INDEX_HISTOGRAM