Re: usage of a calculated index

Norbert Winkler schreef:
> Hello,
>
> I've created the following table and view:
> _____
> CREATE TABLE X_EF (
> ID_EF INTEGER NOT NULL,
> A_VALUE VARCHAR2(1) NOT NULL,
> A_NUMBER NUMBER(15,0) NOT NULL
> )
> /
> CREATE INDEX IX_X_EF_A_YEAR
> ON X_EF (
> TRUNC(A_NUMBER/100000000,0)
> )
> /
> CREATE INDEX IX_X_EF_A_VALUE
> ON X_EF (
> A_VALUE
> )
> /
> create view XV_EF as
> SELECT d.*, trunc(A_NUMBER/100000000,0) A_YEAR
> FROM X_EF
> /_____
>
> I've tested this SELECT's
> ____
>
> SELECT A_YEAR, A_VALUE, Count(*) Anz
> FROM XV_EF
> WHERE A_YEAR = 2008
> Group By A_YEAR, A_VALUE;
> --> 3 A_VALUEs
>
> SELECT A_YEAR, A_VALUE, Count(*) Anz
> FROM XV_EF
> WHERE A_VALUE = '1'
> Group By A_YEAR, A_VALUE;
> --> 7 Years
>
> ____
> the "execution plan" shows for "WHERE A_VALUE" the usage of
> IX_X_EF_A_VALUE;
>
> but for "WHERE A_YEAR" I see "full table scan".
>
> I think I've read that using the same expression in SELECT likE in CREATE
> INDEX causes the usage of the index.
>
> Thanks for any advice!
>
>

If it's Oracle 10.x: what is your optimizer goal? if I perform your query with
SELECT /*+ ALL_ROWS */ A_YEAR, A_VALUE, .... the query uses its function based index. With 'CHOOSE' it does not (at least not until you calculate your statistics!)

Shakespeare Received on Tue Nov 25 2008 - 11:27:06 CST