The wonder of increasing rows
Date: Mon, 28 Oct 2019 11:49:56 +0100 (CET)
Message-ID: <763494113.18333.1572259796235_at_bluewin.ch>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 28 2019 - 11:49:56 CET