JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 13: Sorting and Merge Joins



Intersection Sorting (1st Dec 2009)

Counting in Merge (30th Nov 2009)

Row size in sorts (19th Jan 2008)

Typos (Updated 20th Feb 2006)

Freeing memory (2nd Dec 2005)

Max_area_size (4th Nov 2005)

Rowids (4th Nov 2005)



Back to Book Index

Top of page.


Intersection Sorting: (1st Dec 2009)

Page 397, table of results extracted from 10032 trace. The information copied is in the wrong order. (In passing, the results are highly version and installation dependent, as it depends on the objects created at database creation time – I also found in my system that the first four sets of information were not printed to the trace file, and the last two were in the opposite order.) The corrected table (probably) should have been as follows:


Input records 2500 -- T1

Output records 10


Input records 2000 -- T2

Output records 9


Input records 9 -- The intersection view

Output records 9


Input records 9

Output records 9


Input records 2500

Output records 10


Input records 2000

Output records 9


It’s also possible that the script in the code depot is a version that generates 10 times as many rows in the tables as the script described in the  book. Thanks to Kun Sun for pointing out this issue.

Counting in merge: (30th Nov 2009)

Page 383: In the last complete paragraph on the page, I say that the check of t2 goes through 10,000 rows, then 9,998 rows, then 9,996 rows (decreasing by 2 each step). This is wrong.  The check of t2 will be:  10,000 for the first two rows from t1, then 10,000 again for the second two rows, then 9,998 for the next two rows, and so on decreasing by 2 for each pair of rows from t1 until the last row from t1 is checked against 9,004rows from t2.  If you want to see this, you can run the following query after generating the data:


break on report

compute sum of count(*) on report



      n1, count(*)

from  (







            t2.n2 >= t1.n2 - 1

      and   t1.n1 <= 1000


group by n1

order by n1



Thanks to Henish Patel for highlighting this error. This error was simultaneously reported to me in an email from Kun Sun, who also pointed out that three paragraphs earlier I had said: “apart from the very first and last rows in the t1 selection”, when I should have said “apart from the first two rows in the t1 selection”.


Row size in sorts: (19th Jan 2008)

Page 372: In the last bullet point at the end of the page “Row size” I have the formula for the row size as 12 + sum(avg_col_length) + (n – 1), where n is the number of columns involved. This is wrong. The formula (as derived by Vladimir Begun on his blog is 10 + sum(avg_col_length) + ceil(sum(avg_col_length)/10).  I’m not sure how I managed to get the formula I did, when it’s so easy to show that it’s wrong, but at the very least I did not do enough tests to disprove my hypothesis once I had formed it.

Freeing memory: (2nd Dec 2005)

Page 393: At the very top of the page, there is a comment about memory being freed properly when you are running with workarea_size_policy set to auto. Technically, this happens if you have set the pga_aggregate_target to a non-zero value, which causes the hidden parameter _use_real_free_heap to be set to true.


Max_Area_Size: (4th Nov 2005)

Page 372: I spotted the first error in Practical Oracle 8i a few seconds after the first copy had landed on my front door-step. I spotted the first error in Cost Based Oracle before the printer had even finished printing it – I woke up at three a.m. a few days before the book came out, realizing that I had introduced an error in the last pass of proof-reading the galley pages.


In the bullet point Max Area Size, you will find the comment: “When the two values differ, the calculation of cost seems to be based on the Max area size, rather than the Area size.” This is the wrong way round – for sorting, the optimizer uses the Area size for the calculation of cost, not the Max area size. (This is the opposite way around to the calculations used for hash joins).


This error is repeated on page 373, in the bullet point Initial runs, which says: “You can derive this as Block to Sort * block size / Max Area size.” Again, you should replace Max Area size with Area size.


Typos:  (Updated 2nd Dec 2005)

Page 361, last line: There is a reference to event 1033 – which also appears in the index – this should be event 10033.


Page 358, after the first paragraph there is a little table of space usage, of which the first line reads:

            6 bytes for the length of the entry                    00000008

As you can tell by checking the entry, this should be 4 bytes, not 6 bytes.


Page 387, fourth paragraph, lines 3-4

The earlier query did not lose it’s joins predicate though transitive closure – so …

This almost incomprehensible statement should be:

The earlier query did not lose its join predicate through transitive closure – so …



Rowids:  (4th Nov 2005)

Page 392, the section titled Indexes points out that when you are doing the calculation of memory for building an index that you need to allow for the rowid as an extra column to be sorted. This is correct, but I then quote the lengths of the rowids as they appear in the final index – which is not necessarily the same as the lengths they take up whilst the sort is taking place. For example, the rowid for a simple B-tree index seems to use the full 10 byte extended rowid in the dump to disc if the sort is too large to complete in memory, not the 6 byte restricted rowid that ends up in the index.

Back to Book Index

Top of page.