11g bug with optimizer_mode=first_rows_N

Back in March, I asked Kerry Osborne some questions: http://kerryosborne.oracle-guy.com/2010/04/funny-developer-tricks-first_rows/#comment-38234

In the 10053 trace output for first_rows(10) (above), we see: —————————————– BEGIN Single Table Cardinality Estimation —————————————– Table: SKEW Alias: A
Card: Original: 10 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00

Is it expected that the estimated cardinality of a table is equal to the optimizer_mode in this case? From what I understand, first_rows is kind of ‘fooling’ the CBO into thinking that a set number of rows may (or may not) be returned, but if first_rows artificially effects the cardinality of a rowsource, it would seem that more query plans would not be optimal. For example, choosing a table with an estimated cardinality of 10 when it really has 10000000 rows to be the inner table of a nested loop join.

This evening, Oracle updated my SR and told me that this is now going through BDE as bug 11858963. I can understand first_rows_N working like a shortcut where you tell Oracle you only want the first N rows from the last operation. But to propagate the assumption about the number of rows to each and every single operation seems a bit strange to me.

PS - As I mentioned in Kerry's blog, I am very much grateful for the work he and his peers have done. My point in bring this to oracle-l is basically just to make folks aware of the bug.

--

Charles Schultz

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 23 2011 - 01:32:40 CDT