usage of a calculated index

From: Norbert Winkler <norbert.winkler1_at_gmx.de>
Date: Tue, 25 Nov 2008 16:45:47 +0100
Message-ID: <1mnue8mwa0h53.1vq0ouwa2pm74.dlg@40tude.net>

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

Received on Tue Nov 25 2008 - 09:45:47 CST