Forum Stats

  • 3,782,968 Users
  • 2,254,716 Discussions
  • 7,880,231 Comments

Discussions

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

731105
731105 Member Posts: 2
edited Nov 10, 2009 11:16AM in General Database Discussions
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);
Tagged:

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    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
    Dom Brooks Member Posts: 5,552 Silver Crown
    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> 
    Dom Brooks
  • Enrique Orbegozo
    Enrique Orbegozo Member Posts: 594 Silver Badge
    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
    Centinul Member Posts: 6,871 Bronze Crown
    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.
    Centinul
  • riedelme
    riedelme Member Posts: 3,528
    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
    731105 Member Posts: 2
    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;
This discussion has been closed.