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

CHAPTER 4 SIMPLE B-TREE ACCESS
Painting Code 39 Extended In Java
Using Barcode creator for Java Control to generate, create Code 3 of 9 image in Java applications.
www.OnBarcode.com
Code-39 Scanner In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
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.
Make GS1 128 In Java
Using Barcode drawer for Java Control to generate, create EAN 128 image in Java applications.
www.OnBarcode.com
Make Code 39 In Java
Using Barcode creation for Java Control to generate, create Code39 image in Java applications.
www.OnBarcode.com
The Three Selectivities
Making QR Code JIS X 0510 In Java
Using Barcode encoder for Java Control to generate, create QR Code JIS X 0510 image in Java applications.
www.OnBarcode.com
Printing Data Matrix ECC200 In Java
Using Barcode generation for Java Control to generate, create DataMatrix image in Java applications.
www.OnBarcode.com
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)
Barcode Maker In Java
Using Barcode drawer for Java Control to generate, create Barcode image in Java applications.
www.OnBarcode.com
Print UCC - 14 In Java
Using Barcode maker for Java Control to generate, create GTIN - 14 image in Java applications.
www.OnBarcode.com
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)
Code 3/9 Encoder In C#
Using Barcode creator for VS .NET Control to generate, create Code 39 image in .NET framework applications.
www.OnBarcode.com
Painting Code-39 In .NET
Using Barcode generator for Reporting Service Control to generate, create Code 3/9 image in Reporting Service applications.
www.OnBarcode.com
CHAPTER 4 SIMPLE B-TREE ACCESS
Encode Barcode In None
Using Barcode generation for Software Control to generate, create Barcode image in Software applications.
www.OnBarcode.com
Code 128 Code Set A Maker In None
Using Barcode generator for Online Control to generate, create Code 128 Code Set C image in Online applications.
www.OnBarcode.com
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.
USS Code 39 Scanner In None
www.OnBarcode.com
Drawing GS1 - 12 In None
Using Barcode creation for Word Control to generate, create GTIN - 12 image in Microsoft Word applications.
www.OnBarcode.com
GS1 128 Drawer In None
Using Barcode encoder for Office Word Control to generate, create GS1 128 image in Word applications.
www.OnBarcode.com
Scanning Code39 In C#.NET
Using Barcode reader for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
Decode Barcode In Java
Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Draw Code 3/9 In None
Using Barcode creator for Font Control to generate, create Code 39 image in Font applications.
www.OnBarcode.com
EAN-13 Supplement 5 Generation In Java
Using Barcode generator for Eclipse BIRT Control to generate, create EAN13 image in BIRT reports applications.
www.OnBarcode.com
UPCA Creator In Visual C#
Using Barcode generation for .NET framework Control to generate, create UPC Code image in .NET framework applications.
www.OnBarcode.com