The wonder of increasing rows

  • From: "l.flatz@xxxxxxxxxx" <l.flatz@xxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 28 Oct 2019 11:49:56 +0100 (CET)

Hi ,
not the first time I come accross this, but now I have to follow up on it:
--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes 
| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       
|   845 (100)|          |
|   1 |  SORT AGGREGATE                      |                 |     1 |     7 
|            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ANY_TABLE       | 84827 |   
579K|   845   (1)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID             |                 | 68418 |       
|    76   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                 | ANY_INDEX       | 68418 |       
|    76   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("X"."ANY_COLUMN1"='J')
   4 - access("X"."ANY_COLUMN2"=89155)
As you can see, the cardinality estimate increases when the table is visited. I 
have no good explaination for it.
84827 is BTW correct. 
It even gets better, when i hint a FTS:
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost 
(%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |       |       | 15457 
(100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |              |     1 |     7 |         
   |          |        |      |            |
|   2 |   PX COORDINATOR               |              |       |       |         
   |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000     |     1 |     7 |         
   |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |              |     1 |     7 |         
   |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |              |  2446 | 17122 | 15457   
(1)| 00:00:03 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| ANY_TABLE    |  2446 | 17122 | 15457   
(1)| 00:00:03 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z AND ("X"."ANY_COLUMN2"=89155 AND 
"X"."ANY_COLUMN1"='J'))
       filter(("X"."ANY_COLUMN2"=89155 AND "X"."ANY_COLUMN1"='J')) 
As far as I know :Z>=:Z AND :Z<=:Z does not filter anthing: 
http://kerryosborne.oracle-guy.com/2011/01/30/storagezz-and-z
Can anybody shed somelight on it? Version is 19.0., neither Index nor table are 
comressed
Regards 
Lothar

Other related posts: