Hi Tom,
I have a problem with DENSE_RANK function. Let's see an example:
CREATE TABLE test_rank (val number);
INSERT INTO test_rank VALUES(1);
INSERT INTO test_rank VALUES(2);
INSERT INTO test_rank VALUES(3);
INSERT INTO test_rank VALUES(4);
INSERT INTO test_rank VALUES(1);
INSERT INTO test_rank VALUES(2);
INSERT INTO test_rank VALUES(3);
INSERT INTO test_rank VALUES(4);
COMMIT;
Now when I run the following query
SELECT *
FROM (SELECT DENSE_RANK() OVER (ORDER BY 1) AS x$
, DENSE_RANK() OVER (ORDER BY val) AS y$
FROM test_rank
)
WHERE x$ BETWEEN 1 AND 2 AND y$ BETWEEN 3 AND 4I receive no results.
However when I run a query:
SELECT *
FROM (SELECT DENSE_RANK() OVER (ORDER BY 1)+0 AS x$
, DENSE_RANK() OVER (ORDER BY val)+0 AS y$
FROM test_rank
)
WHERE x$ BETWEEN 1 AND 2 AND y$ BETWEEN 3 AND 4I see exactly what I expect:
X$ Y$
-------------------------------------- --------------------------------------
1 3
1 3
1 4
1 4 Is it some kind of Oracle bug?
Regards,
Wojciech
Yup...that looks like a bug to me. If you look at the execution plan - see in the first instance, we did a "PUSHED RANK". That's an optimization we use in pagination style queries to try avoid having to sort an entire result set. It looks to me like its being inappropriately applied in this case.
Please log this with Support including your test case.
SQL>
SQL> set autotrace on explain
SQL> SELECT *
2 FROM (SELECT DENSE_RANK() OVER (ORDER BY 1) AS x$
3 , DENSE_RANK() OVER (ORDER BY val) AS y$
4 FROM test_rank
5 )
6 WHERE x$ BETWEEN 1 AND 2 AND y$ BETWEEN 3 AND 4;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2411384131
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 208 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 8 | 208 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 8 | 104 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST_RANK | 8 | 104 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X$">=1 AND "X$"<=2 AND "Y$">=3 AND "Y$"<=4)
2 - filter(DENSE_RANK() OVER ( ORDER BY NULL )<=2)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
SQL>
SQL> SELECT *
2 FROM (SELECT DENSE_RANK() OVER (ORDER BY 1)+0 AS x$
3 , DENSE_RANK() OVER (ORDER BY val)+0 AS y$
4 FROM test_rank
5 )
6 WHERE x$ BETWEEN 1 AND 2 AND y$ BETWEEN 3 AND 4;
X$ Y$
---------- ----------
1 3
1 3
1 4
1 4
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2201055587
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 208 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 8 | 208 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 8 | 104 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_RANK | 8 | 104 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X$">=1 AND "X$"<=2 AND "Y$">=3 AND "Y$"<=4)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
SQL>
SQL> set autotrace off
SQL>