SIMPLE B-TREE ACCESS in Java

Painting Code 3 of 9 in Java SIMPLE B-TREE ACCESS

select /*+ index(t1) */ n2 from t1 where n1 between 6 and 9 order by n1 ; Execution Plan (9.2.0.6 and 10.1.0.4) ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=230 Card=2051 Bytes=12306) 1 0 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=230 Card=2051 Bytes=12306) As usual, we have to use a little care to calculate the selectivity of the range-based predicate on n1. In this case it is (9 6) / (24 0) + 2/25 = 3/24 + 2/25 = 0.205. So if we forget about the table component in the baseline formula, we get Cost = 2 + 0.205 * 1111 = 2 + 227.755 = 230 Our assumption seems to be correct.

Just to finish off, let s add one final refinement to our test case, to show that the general case of a single table access by B-tree index has three selectivities. Run the following query (script btree_cost_02.sql in the online code suite again) through autotrace: select /*+ index(t1) */ small_vc from t1 where and and and ; n1 ind_pad n2 small_vc between 1 and 3 = rpad('x',40) = 2 = lpad(100,10)

Execution Plan (9.2.0.6 and 10.1.0.4) ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=264 Card=1 Bytes=58) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=264 Card=1 Bytes=58) 2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=184 Card=1633)

This query starts like the example in the section More on Range-based Tests, but adds one more predicate: small_vc = lpad(100,10). Note particularly the cost has not changed, but the cardinality has dropped from 82 to 1 (Card=1 in the first and second lines). The optimizer has calculated that the query will return just one row. The mechanical steps at execution time are as follows: Check all the index leaf blocks for the range n1 between 1 and 3 (effective index selectivity). Check all the table rows where the index entry passes all three index predicates (effective table selectivity). Return the rows where the fourth predicate passes. So we have Effective index selectivity (n1) = 0.1633333 Effective table selectivity (n1, ind_pad, n2) = 0.163333 * 1 * 0.05 Final table selectivity (n1, ind_pad, n2, small_vc) = 0.163333 * 1 * 0.05 * 0.0001 And we get these cardinalities (which are always some variant of selectivity * input row count): Index access line = round(0.163333 * user_tables.num_rows (10,000)) = 1,633 Rowid source (not part of plan) = round(0.0081667 * user_tables.num_rows (10,000)) = 82 Table return = round(0.0000081667 * user_tables.num_rows (10,000)) = 0! At first glance, this seems to be another place where my theory about how 9i and 10g use the round() function to produce the final cardinality seems to break. In this case, though, it isn t really surprising, and you will find several other occasions where an answer that technically looks as if it should be zero is changed to one. It s an interesting point, of course, that 9i and 10g report 1,633 as the cardinality on the index line of this plan. In most cases, the reported cardinality is the output cardinality, not the input cardinality in other words, the number of rows that will be passed on by this line of the plan, rather than the number of rows that will be examined by this line of the plan. Ideally, of course, we would like to see both 1,633 and 82 reported against the index line in the plan, as both numbers are relevant unfortunately, there is only space for one value in the execution plan. Looking again at the section More on Range-based Tests, 8i appeared to be following the output count rule when it reported 82 against the index line where 9i and 10g reported 1,633. In this example, 8i went one step further and reported a cardinality of just one against the index line. There will always be little inconsistencies like this that cause endless confusion, especially as you migrate through different versions of Oracle.