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@xxxxxxxxx
Datum : 28/10/2019 - 13:08 (MN)
An : oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx wrote:
Hi Saysan,
what kind of Index statistics would be used to calculate cardinaliy?
Regards
Lothar
----Ursprüngliche Nachricht----
Von :
xt.and.r@xxxxxxxxx
Datum : 28/10/2019 - 12:04 (MN)
An :
l.flatz@xxxxxxxxxx
Cc :
oracle-l@xxxxxxxxxxxxx
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
