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