JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 6: Selectivity Issues


Ch.6 Code Depot (2nd Feb 2006)

Addenda

Another 10.2 sanity check (2nd Feb 2006)

sysdate clarification (2nd Feb 2006)

Errata

mod() (26th Aug 2012)

Addenda

Another 10.2 sanity Check

Page 136, first paragraph (this is in a section headed “Correlated Columns”) I say:

One of the very strange things about this problem when it appears in indexes is that the database contains the right information (for some queries)—and the optimizer ignores it.

 

This is referring to the fact that the script dependent.sql creates a data set where two columns have identical values, ranging from 0 – 25; and Oracle calculates a selectivity of 1/(25 * 25) for a query against these columns, when it has an index that shows the distinct_keys at being exactly 25. The execution plan in 9.2 is:

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14 Card=16 Bytes=928)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=14 Card=16 Bytes=928)

   2    1     INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=4 Card=16)

 

However, when we get to 10.2, the execution looks like this:

 

---------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |

---------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    16 |   928 |   294 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    16 |   928 |   294 |

|*  2 |   INDEX RANGE SCAN          | T1_I1 |   400 |       |    47 |

---------------------------------------------------------------------

Note how the cardinality (card = / Rows) value on the index range scan line has changed. In 10.2, the optimizer looks at the distinct_keys value in user_indexes to work out the index cardinality and cost – so that’s a step in the right direction. Unfortunately, it doesn’t take this through to the table, it falls back to the ‘product of columns’.

Thanks for Alen Prodan for bringing this change to my notice.

sysdate clarification

Page 130, second paragraph of section “Surprising Sysdate”: I reference a script called sysdate_01.sql which contains notes about the way that sysdate + 0 (amongst other things) is treated as an unknown bind variable for the purposes of  the optimizer arithmetic. What I fail to make clear in the notes is that this is relevant only for range-based predicates.  Thanks to Boris Dali for picking up this detail.  The script in the code depot for this chapter has been updated to reflect this point.

Back to Book Index

Top of page.


Errata

mod() (26th Aug 2012)

Page 142, first para, second sentence “… hold 100 copies each of the values from 1 to 10.” This refers back to the “create table” SQL on the previous page, which uses the function call mod(rownum,10) which means the sentence should, of course, read “… hold 100 copies each of the values from 0 to 9.”

 


Back to Book Index

Top of page.