Re: usage of a calculated index

On Nov 25, 9:45 am, Norbert Winkler <norbert.winkl..._at_gmx.de> wrote:
> 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!
>
> --
> Norbert

There is an Oracle version involved? Is it possible for you to share such information???

David Fitzjarrell Received on Tue Nov 25 2008 - 09:55:33 CST