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
