1

Topic: opt_estimate On an index for lack of the table

There is a request of type
select / * + OPT_ESTIMATE (INDEX_SKIP_SCAN DD IDX01 ROWS=1234) */
count (vn) cnt from Table1 DD where vn in (:p1);
Cardinality for certain needs it produces bad, therefore it is required to correct it on 1234.
But. The field vn is in an index, therefore the table is not read, but only an index.
It is actually necessary to set cardinality of an index.
The line of the plan looks approximately so:
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2193 (100) | |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX SKIP SCAN | IDX01 | 21 | 126 | 2193 (1) | 0:00:27 AM |
----------------------------------------------------------------------------------------------
And, accordingly,  in that case does not work.
But as soon as a field vn in count it is replaceable in the field what in an index is not present the plan shows access to the table
And  works:
select / * + OPT_ESTIMATE (INDEX_SKIP_SCAN DD IDX01 ROWS=1234) */
count (ts) cnt from Table1 DD where vn in (:p1);
---------------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX UNIQUE SCAN | I_PK | 21 |
|* 3 | INDEX SKIP SCAN | IDX01 | 1234 |
---------------------------------------------------------------
I_PK Is a table, it is simple it IOT.
Question - how to force  to work on a bare index, without the table, as shown in 1 plan?

2

Re: opt_estimate On an index for lack of the table

avdu;
Because in the first case undertakes  under the table. Compare with

select / * +
OPT_ESTIMATE (INDEX_SKIP_SCAN DD IDX01 ROWS=123>>> 4 <<<)
OPT_ESTIMATE (TABLE DD ROWS=123>>> 6 <<<)
*/
count (vn) cnt from Table1 DD where vn in (:p1);

3

Re: opt_estimate On an index for lack of the table

avdu;
And generally why not to use NO_INDEX_SS if want from it to get rid?

4

Re: opt_estimate On an index for lack of the table

xtender;
Precisely, like worked. All sorted out, and the table did not guess in the absence of that in the plan.
And it that also was required, however. In the plan that it is not present, but in life is.
Thanks, the sir

5

Re: opt_estimate On an index for lack of the table

xtender;
From an index I do not want, simply I want to change cardinality on it.

6

Re: opt_estimate On an index for lack of the table

avdu;
I meant not from an index, namely from ISS.
, it would be better to use, something another...
For example, still it is possible to make:

COLUMN_STATS (DD, VN, scale, length=3 distinct=5 nulls=0 min=2 max=10)

parameters to twist under the necessary

7

Re: opt_estimate On an index for lack of the table

xtender;
It is possible, simply wanted to check up .