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

731105

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);

Centinul

Here are my results on 10.2.0.4 Windows Server 2008 x64:

SQL> CREATE TABLE TEST_FUNCTION_INDEX
  2  (
  3  ID NUMBER NOT NULL,
  4  DATE_COL DATE
  5  );

Table created.

SQL> CREATE INDEX IDX_1 ON TEST_FUNCTION_INDEX
  2  (TRUNC("DATE_COL"));

Index created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (1, sysdate - 1);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (2, sysdate - 2);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (3, sysdate - 3);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (4, sysdate - 4);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (5, sysdate - 5);

1 row created.

SQL> SELECT SUM(DATE_COUNT) FROM
  2  (
  3  SELECT TRUNC(DATE_COL) TRUNC_DATE_COL, COUNT(*) DATE_COUNT
  4  FROM TEST_FUNCTION_INDEX
  5  GROUP BY TRUNC(DATE_COL)
  6  );

SUM(DATE_COUNT)
---------------
              5

Dom Brooks

11.1.0.6:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE TEST_FUNCTION_INDEX
  2  (
  3  ID NUMBER NOT NULL,
  4  DATE_COL DATE
  5  );

Table created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (1, sysdate - 1);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (2, sysdate - 2);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (3, sysdate - 3);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (4, sysdate - 4);

1 row created.

SQL> INSERT INTO TEST_FUNCTION_INDEX VALUES (5, sysdate - 5);

1 row created.

SQL> SELECT SUM(DATE_COUNT) FROM
  2  (
  3  SELECT TRUNC(DATE_COL) TRUNC_DATE_COL, COUNT(*) DATE_COUNT
  4  FROM TEST_FUNCTION_INDEX
  5  GROUP BY TRUNC(DATE_COL)
  6  );

SUM(DATE_COUNT)
---------------
              5

SQL> CREATE INDEX IDX_1 ON TEST_FUNCTION_INDEX
  2  (TRUNC("DATE_COL"));

Index created.

SQL> SELECT SUM(DATE_COUNT) FROM
  2  (
  3  SELECT TRUNC(DATE_COL) TRUNC_DATE_COL, COUNT(*) DATE_COUNT
  4  FROM TEST_FUNCTION_INDEX
  5  GROUP BY TRUNC(DATE_COL)
  6  );

SUM(DATE_COUNT)
---------------
              5

SQL> 

Enrique Orbegozo

11.1.0.7.0 with PSU 1:

$ sqlplus scott/tiger

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Oct 30 12:14:55 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

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

SUM(DATE_COUNT)
---------------






SQL>  drop index idx_1;

Index dropped.

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

SUM(DATE_COUNT)
---------------
              5

Centinul

There are a lot of bugs listed in Metalink with respect to wrong results and function-based indexes.

Here are a few:

Bug 4028186 Wrong results if function based index exists
Bug 4717546 Wrong results / poor plan when function based index exists
Bug 5092688 Wrong results if function based index exists

Based on reviewing them the workarounds range from dropping the index to setting "_disable_function_based_index" to TRUE.

riedelme

Centinul wrote:
There are a lot of bugs listed in Metalink with respect to wrong results and function-based indexes.

Here are a few:

Bug 4028186 Wrong results if function based index exists
Bug 4717546 Wrong results / poor plan when function based index exists
Bug 5092688 Wrong results if function based index exists

Based on reviewing them the workarounds range from dropping the index to setting "_disable_function_based_index" to TRUE.

Facinating. It seems to me that if you use the undocumented intitialization parameter you might just as well drop the FBIs too.

Another hazard of FBIs is the Law of Unintended Consequences. 2 years ago we tried to use one to speed up a query in a PL/SQL package. Worked OK for that purpose, but an unrelated loader on the affected table ran and rand but never finished until the FBI was dropped.

731105

This problem is due to the following bug:

Bug.9017439/8327137 (36) FUNCTION-BASED INDEX CAUSES INCORRECT RESULTS

Patch exists (on my oracle support - former Metalink) for the base bug 8327137 on top of 11.1.0.7 for your platform.

Workaround is:

SQL> alter session set "_replace_virtual_columns"=false;