Oracle 基数估计

嗨，

请看下面的两个SQL语句，一个我使用RANK，另一个我使用ROW_NUMBER。
请注意，在我们使用秩的情况下，基数估计是正确的，而在ROW_NUMBER的情况下，基数估计将更改为1。
我做错什么了吗？

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 <>. Cost: 4  Bytes: 7,200  Cardinality: 72    
  2 WINDOW SORT PUSHED RANK  Cost: 4  Bytes: 3,960  Cardinality: 72   
   1 TABLE ACCESS FULL TABLE <>.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  <>. Cost: 4  Bytes: 100  Cardinality: 1    
  2 WINDOW SORT PUSHED RANK  Cost: 4  Bytes: 3,960  Cardinality: 72   
   1 TABLE ACCESS FULL TABLE  <>.nme_renamed_1 Cost: 3  Bytes: 3,960  Cardinality: 72  
复制

问候!!

Row_number() 为每一行分配一个新值。因此，您可以保证只有一行的值为1。

Rank() 为领带分配相同的值。所以你可以有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   1
复制

您使用row_number() 的查询只能返回一行。所以这 (1) 的基数估计是正确的。

当使用rank() 时，您可以返回1-72行之间的任何位置 (假设第2步的估计是正确的)。优化器在这里遇到最坏的情况:

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 |                        
-------------------------------------------------  
复制