Hi,
Please have a look at the two SQL statements below, in one I am using RANK and in another I am using ROW_NUMBER.
Notice that the cardinality estimate is correct in case we use RANK and in case of ROW_NUMBER it changes to 1.
Am I doing something wrong?
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SELECT a.renamed_1_id
, a.renamed_2
, a.name
, a.statUS
FROM (SELECT renamed_1_ID
, renamed_2
, NAME
, STATUS
, RANK ()
OVER (
PARTITION BY NAME
ORDER BY
DECODE (STATUS
, 'ACTIVE', 1
, 'SUSPENDED', 2
, 3)
, last_update_time DESC)
RN
FROM nme_renamed_1) a
WHERE a.RN = 1;
Plan
SELECT STATEMENT ALL_ROWSCost: 4 Bytes: 7,200 Cardinality: 72
3 VIEW <<SchemaName>>. Cost: 4 Bytes: 7,200 Cardinality: 72
2 WINDOW SORT PUSHED RANK Cost: 4 Bytes: 3,960 Cardinality: 72
1 TABLE ACCESS FULL TABLE <<SchemaName>>.nme_renamed_1 Cost: 3 Bytes: 3,960 Cardinality: 72
SELECT a.renamed_1_ID
, a.renamed_2
, a.NAME
, a.STATUS
FROM (SELECT renamed_1_ID
, renamed_2
, NAME
, STATUS
, ROW_NUMBER ()
OVER (
PARTITION BY NAME
ORDER BY
DECODE (STATUS
, 'ACTIVE', 1
, 'SUSPENDED', 2
, 3)
, last_update_time DESC)
RN
FROM nme_renamed_1) a
WHERE a.RN = 1;
Plan
SELECT STATEMENT ALL_ROWSCost: 4 Bytes: 100 Cardinality: 1
3 VIEW <<SchemaName>>. Cost: 4 Bytes: 100 Cardinality: 1
2 WINDOW SORT PUSHED RANK Cost: 4 Bytes: 3,960 Cardinality: 72
1 TABLE ACCESS FULL TABLE <<SchemaName>>.nme_renamed_1 Cost: 3 Bytes: 3,960 Cardinality: 72 Regards!!
Row_number() assigns a new value for each row. Thus you're guaranteed to only have one row with the value 1.
Rank() assigns ties the same value. So you could have any number of rows with rank = 1:
create table t as
select 1 x from dual connect by level <= 5;
exec dbms_stats.gather_table_stats(user, 't');
select row_number() over (order by x) rn ,
rank() over (order by x) rk
from t;
RN RK
1 1
2 1
3 1
4 1
5 1Your query with row_number() can only return one row. So the cardinality estimate for this (1) is correct.
When using rank() you could have anywhere between 1-72 rows returned (assuming the estimate at step 2 is correct). The optimizer is going with the worst case scenario here:
set serveroutput off
with rns as (
select row_number() over (order by x) rn ,
rank() over (order by x) rk
from t
)
select * from rns
where rk = 1;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +ROWS'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
with rns as ( select row_number() over (order by x) rn ,
rank() over (order by x) rk from t ) select * from rns where rk
= 1
Plan hash value: 2764225767
-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | VIEW | | 5 |
| 2 | WINDOW SORT PUSHED RANK| | 5 |
| 3 | TABLE ACCESS FULL | T | 5 |
-------------------------------------------------
with rns as (
select row_number() over (order by x) rn ,
rank() over (order by x) rk
from t
)
select * from rns
where rn = 1;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +ROWS'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
with rns as ( select row_number() over (order by x) rn ,
rank() over (order by x) rk from t ) select * from rns where rn
= 1
Plan hash value: 2764225767
-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | VIEW | | 1 |
| 2 | WINDOW SORT PUSHED RANK| | 5 |
| 3 | TABLE ACCESS FULL | T | 5 |
-------------------------------------------------