I noticed a strange behavior—a SQL statement, which used to return no rows in the past, started returning rows in an environment where the test data set has not changed. I performed a code comparison and observed a difference in the order of a table in FROM clause. After looking through, I found a bug entry on MOS that demonstrated the same behavior, a simple test case from bug 8823789 reproduced below:
- Build test tables with sample data.
CREATE TABLE one (n NUMBER, d DATE); CREATE TABLE two (n NUMBER, d DATE, v VARCHAR2(1)); CREATE INDEX two_i ON two(n, d); INSERT INTO one (n, d) VALUES (1, ADD_MONTHS(TRUNC(SYSDATE)-1,-12)); INSERT INTO two (n, d, v) VALUES (1, ADD_MONTHS(TRUNC(SYSDATE)-1,-24), 'A'); INSERT INTO two (n, d, v) VALUES (1, NULL, 'B'); INSERT INTO two (n, d, v) VALUES (1, ADD_MONTHS(TRUNC(SYSDATE)-1,-36), 'C'); COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'one',cascade=>TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'two',cascade=>TRUE);
I ran this test case on 9.2.0.8, 10.2.0.4 and 11.1.0.7, and all runs demonstrated the same behavior. The correct expected result is no rows selected. As you see, hints are used in below SQL statements to reproduce the issue.
- Table order: one, two with INDEX RANGE SCAN returns incorrect result (i.e. returns one row)
SELECT /*+ ORDERED INDEX(t)*/
o.*, t.*
FROM one o, two t
WHERE o.n = 1
AND o.n = t.n
AND o.d <= t.d;
N D N D V
---------- --------- ---------- --------- -
1 26-JAN-10 1 B
SELECT * FROM table(dbms_xplan.display_cursor);
SQL_ID dmhzzghapgu4h, child number 0
-------------------------------------
SELECT /*+ ORDERED INDEX(t)*/ o.*, t.* FROM one o, two t WHERE o.n
= 1 AND o.n = t.n AND o.d <= t.d
Plan hash value: 133214459
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TWO | 1 | 10 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 21 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | ONE | 1 | 11 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TWO_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."N"=1)
4 - access("T"."N"=1 AND "O"."D"<="T"."D")
- Table order: two, one with INDEX RANGE SCAN returns correct result (i.e. no rows selected)
SELECT /*+ ORDERED INDEX(t)*/
o.*, t.*
FROM two t, one o
WHERE o.n = 1
AND o.n = t.n
AND o.d <= t.d;
no rows selected
SELECT * FROM table(dbms_xplan.display_cursor);
SQL_ID dkpkj0khg48k8, child number 0
-------------------------------------
SELECT /*+ ORDERED INDEX(t)*/ o.*, t.* FROM two t, one o WHERE o.n
= 1 AND o.n = t.n AND o.d <= t.d
Plan hash value: 2453263735
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 1 | 21 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TWO | 3 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TWO_I | 3 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | ONE | 1 | 11 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."N"="T"."N")
filter("O"."D"<="T"."D")
3 - access("T"."N"=1)
4 - filter("O"."N"=1)
- Table order: one, two with TABLE ACCESS FULL returns correct result (i.e. no rows selected)
SELECT /*+ ORDERED FULL(t)*/
o.*, t.*
FROM one o, two t
WHERE o.n = 1
AND o.n = t.n
AND o.d <= t.d;
no rows selected
SELECT * FROM table(dbms_xplan.display_cursor);
SQL_ID a5gsw95ccj917, child number 0
-------------------------------------
SELECT /*+ ORDERED FULL(t)*/ o.*, t.* FROM one o, two t WHERE
o.n = 1 AND o.n = t.n AND o.d <= t.d
Plan hash value: 1827160935
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 1 | 21 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| ONE | 1 | 11 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TWO | 3 | 30 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."N"="T"."N")
filter("O"."D"<="T"."D")
2 - filter("O"."N"=1)
3 - filter("T"."N"=1)
The database engine returns wrong results when “INDEX RANGE SCAN” with a key involved in less than (<) operator and keys are shown in access field of predicate information without a “IS NOT NULL” predicate. If you prevent the use of index then the database engine will return a correct result—in our case, it is no rows selected. I would recommend you to apply appropriate patch, if you hit this bug.