Re: Re: The wonder of increasing rows

Hi,
i foud it myself. It was a directive at table level that got the estimate correct. Without the directive:

| Id  | Operation                            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT                     |                 |      1 |        |      1 |00:00:00.12 |     235 |    107 |


|   1 |  SORT AGGREGATE                      |                 |      1 |      1 |      1 |00:00:00.12 |     235 |    107 |


|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ANY_TABLE       |      1 |   2446 |  84827 |00:00:00.12 |     235 |    107 |


|   3 |    SORT CLUSTER BY ROWID             |                 |      1 |  68418 |  84900 |00:00:00.09 |     107 |    107 |


|*  4 |     INDEX RANGE SCAN                 | ANY_INDEX       |      1 |  68418 |  84900 |00:00:00.06 |     107 |    107 |
---------------------------------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):

   2 - filter("X"."ANY_COLUMN1"='J')
   4 - access("X"."ANY_COLUMN2"=89155)
There is a dependcy on the filter columns. I am creating extended stats. Too bad. It really gets entertaining. Regards
Lothar
----Ursprüngliche Nachricht----
Von : gogala.mladen_at_gmail.com
Datum : 28/10/2019 - 13:08 (MN)
An : oracle-l_at_freelists.org
Betreff : Re: The wonder of increasing rows Hi Lothar!
If you check DBA_INDEXES view, you will see DISTINCT_KEYS and NUM_ROWS columns. NUM_ROWS is cardinality. Note that if the underlying columns are not defined as NOT NULL, the index cardinality doesn't necessarily match the table cardinality. DBA_TABLES also has NUM_ROWS column. Regards
 On 10/28/19 7:17 AM,
 l.flatz_at_bluewin.ch wrote:    

  Hi Saysan,           

  what kind of Index statistics would be used to calculate cardinaliy?           

  Regards           

  Lothar       

  ----Ursprüngliche Nachricht----   

 Von :
  xt.and.r_at_gmail.com   

 Datum : 28/10/2019 - 12:04 (MN)   

 An :
  l.flatz_at_bluewin.ch   

 Cc :
  oracle-l_at_freelists.org   

 Betreff : Re: The wonder of increasing rows             

     Hi Lothar,                     

      For index access row-sources (rows 3-4 of the first plan) CBO calculates cardinality using index statistics, and for table access it uses table statistics.                             

    --             

     
      
       
         Best regards, 
        
 Sayan Malakshinov 
       
       Oracle performance tuning engineer
       
       
         Oracle ACE Associate 
        
        http://orasql.org
       
      
     
    
   
  
  
 
 

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l