Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query on Function-based index not returning correct results in 11g

731105Oct 30 2009 — edited Nov 10 2009
I am trying to run a query in 11g (11.1.0.7.0) similar to one below where I have a function-based index on "TRUNC(DATE_COL)". Since this query returns a sum, I expect it to return one row instead this query returns me five rows with all null values (same as rows returned in the inner query). This has been working well for me in 10g (10.2.0.1).

SELECT SUM(DATE_COUNT) FROM
(
SELECT TRUNC(DATE_COL) TRUNC_DATE_COL, COUNT(*) DATE_COUNT
FROM TEST_FUNCTION_INDEX
GROUP BY TRUNC(DATE_COL)
);

Here is a script to re-create my problem:

DROP TABLE TEST_FUNCTION_INDEX;
CREATE TABLE TEST_FUNCTION_INDEX
(
ID NUMBER NOT NULL,
DATE_COL DATE
);

CREATE INDEX IDX_1 ON TEST_FUNCTION_INDEX
(TRUNC("DATE_COL"));

INSERT INTO TEST_FUNCTION_INDEX VALUES (1, sysdate - 1);
INSERT INTO TEST_FUNCTION_INDEX VALUES (2, sysdate - 2);
INSERT INTO TEST_FUNCTION_INDEX VALUES (3, sysdate - 3);
INSERT INTO TEST_FUNCTION_INDEX VALUES (4, sysdate - 4);
INSERT INTO TEST_FUNCTION_INDEX VALUES (5, sysdate - 5);

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 8 2009
Added on Oct 30 2009
6 comments
2,511 views