Index Cost Calculation ,when blevel of Index is 1.

Last week ,i was working on performance issue of small table whose index index blevel was 1. I found cost calculation differs ,just decided to formulate few scenairo of cost calculation.

Table Stats:: Table: REMEDY_HIST_WITHOUT_HISTOGRAM
ROWS: 2382
BLOCKS: 13
AVGROWLEN: 18.00

INDEX STATS:: Index: INDX_REMEDY Col#: 1
BLEVEL : 1
LEAF BLOCK : 5
DISTINCT KEYS : 98
AVG NUM OF LEAF BLKS PER KEY : 1.00
AVG NUM OF DATA BLKS PER KEY : 1.00
CLUSTERING FACTOR : 163.00
***************************************

COST CALCULATION :
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
***************************************

TEST1:
Select * from test.REMEDY_HIST_WITHOUT_HISTOGRAM where seqno > 75

Access Path: index (RangeScan)
Index: INDX_REMEDY
resc_io: 43.00 resc_cpu: 545662
ix_sel: 0.2449 ix_sel_with_filters: 0.2449
Cost: 43.05 Resp: 43.05 Degree: 1

COST= 1 + ceil(5*0.2449) + ceil(163 * 0.2449)
= 1 + 2 + 40
= 43
Cost Calculation was perfect as per formula. Matches with the Cost Generated from 10053 trace.


TEST2:
Select * from test.REMEDY_HIST_WITHOUT_HISTOGRAM where seqno in(98,99)

Access Path: index (RangeScan)
Index: INDX_REMEDY
resc_io: 6.00 resc_cpu: 77062
ix_sel: 0.020408 ix_sel_with_filters: 0.020408
Cost: 6.01 Resp: 6.01 Degree: 1

COST = 1 + ceil( 5 * 0.020408) + ceil(163 * 0.020408)
= 1 + 1 + 4
= 6
Cost Calculation was perfect as per formula.

TEST3:
Select * from REMEDY_HIST_WITHOUT_HISTOGRAM where seqno=10

Index: INDX_REMEDY
resc_io: 3.00 resc_cpu: 32464
ix_sel: 0.010204 ix_sel_with_filters: 0.010204
Cost: 3.00 Resp: 3.00 Degree: 1

According to formula
COST = 1 + ceil(5 * 0.010204) + ceil(163 * 0.010204)
= 1 + 1 + 2
= 4

As we can see that according to formula Cost of Index scan is 4, where as 10053 and as well explain plan show Cost is 3.

How did this differ ?
Indexes where the blevel is set to 1 (so the index goes straight from the root block to the leaf blocks). The optimizer effectively ignores the blevel if every column in the index appears in an equality predicate.
This is an interesting case, as a root block split (which could happen due to one row in the table has been updated) would then push the cost of the index up by two—which could change the access path. This is just one of
many clues that small tables can be more important than large tables when you want to solve optimizer problems.