Oracle Scratchpad


Just a few notes about contributing. Please check the details about tags and formatting at the end. WordPress does some odd things with comment text.

Delayed comments

The blog is protected by Akismet, which tries to identify spam. As a consequence it is possible that a comment you post will not appear because it has been marked as ‘possible spam’. This is particularly likely to happen if you include a few URLs (such as Metalink, other blogs etc.) in the comment. If you post a comment that doesn’t appear within a few minutes send me an email and I will “despam” it as soon as I can.

Code Demonstrations

It’s becoming quite common to see code samples being used to demonstrate a point. The commonest presentation seems to be to run from an SQL*Plus session with set echo on so that the SQL and the response to the SQL alternate.

I actually prefer to see the SQL as a continuous script – its easier to view and I like to read through the script and predict the results before seeing them; so my approach is simply to paste the code then run the script with echo off and paste the results. Do take a look at the note below about spaces disappearing and how to deal with that problem.


If you reply to someone else’s comment it’s a good idea to start your comment with their name in case another unrelated comment arrives between the original and your reply; remember though, that there is a “reply” link under each comment that allows comments to become threaded. It is also quite helpful to quote a line or two from the original if you are just commenting on a specific detail.

Losing text:

It’s been really difficult writing the following three sections on text appearance because the visual editor keeps mangling my carefully constructed examples as I switch between edit modes. If you see odd empty spaces in the instructions please drop me an email to let me know it’s gone wrong again.

Various problems appear because many of the normal html tags are removed by the comment editor. Most significantly, the <pre></pre> tags do not work although the <code></code> tags do persist – but they still lose whitespace.

WordPress recently introduced “sourcecode” tags – which have to be used in square brackets [] – which seem to preserve the spaces in comments, and make it easy for readers to copy the text into their clipboards, and this is the ideal mechanism for including code and text output in comments.

“Less than” in code:

If you start writing SQL in comments be careful about < symbols. Anything from one of these to the next > symbol will disappear when you publish. Due the popularity of using a connect by query against dual to create data, lots of people have seen the interesting bits of their code vanish. You need to replace the symbol with the text &lt; (including the semi-colon).

Spaces in Edits:

When you create a comment (in the theme I have used), all white-space is eliminated, and there doesn’t seem to be an easy way to stop this happening. However I have found that if I prepare the comment in a simple text-editor and do a ‘global find and replace’ to change spaces to the text &nbsp; (including the semi-colon) then I can get whitespace where I need it. In some cases I’ve alternated real spaces with the “no-break space” code by doing a global replace of pairs of spaces because otherwise the lines have got longer than WordPress likes.

Of course it’s a nuisance to do, and even more fiddly if you have tab marks in your text, but if you want to make a point about code or output it’s much more likely to be read by other people if you’ve managed to get the text alignment working. (But see the note about sourcecode tag above.)

Emphasis in Comments:

There are three possibilities that I’ve found to work so far:

  • em gives you italic script, as does the single letter i
  • strong gives you bold, as does the single letter b
  • h1, h2, h3, h4, h5 give you options for headings – h4 is a good size

In these cases you have to use the html tag syntax, for example: <em> to start the highlight, and </em> to end it.


There doesn’t seem to be a preview option available (with this theme, at least), so if you do have an accident when entering a comment, remember that I can edit or delete your comment. So if you have to “finish” off a comment that you published by accident, you can either type the whole thing in again – and I’ll delete the earlier copy; or you can create an ending section – and I’ll cut and paste the two bits together for you..


  1. Hi!

    Did you saw:
    which lead to:

    Is that approved according your knowledge?

    Damir Vadas

    Comment by damirvadasDamir Vadas — July 12, 2011 @ 8:33 pm BST Jul 12,2011 | Reply

  2. Following is the query on TPC-H schema.

    explain plan for select
    o_orderkey= l_orderkey.

    The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as “outer table cost + cardinality of outer table * inner table cost ” fails here. I am not able to understand this.

    Table Stats::
    #Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00
    Column (#1): L_ORDERKEY(NUMBER)
    AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000
    Index Stats::
    Index: LINEITEM_PRTK Col#: 2
    LVLS: 2 #LB: 12931 #DK: 197757 LB/K: 1.00 DB/K: 29.00 CLUF: 5812082.00
    Index: LINEITEM_SI Col#: 14
    LVLS: 2 #LB: 29439 #DK: 4 LB/K: 7359.00 DB/K: 109769.00 CLUF: 439077.00
    Index: LINEITEM_SM Col#: 15
    LVLS: 2 #LB: 18648 #DK: 7 LB/K: 2664.00 DB/K: 112004.00 CLUF: 784030.00
    Index: PK_LINEITEM Col#: 1 4
    LVLS: 2 #LB: 15440 #DK: 5879916 LB/K: 1.00 DB/K: 1.00 CLUF: 120630.00
    Table Stats::
    Table: ORDERS Alias: ORDERS
    #Rows: 1500000 #Blks: 24244 AvgRowLen: 110.00
    Column (#1): O_ORDERKEY(NUMBER)
    AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 163 Max: 5998114
    Index Stats::
    Index: PK_ORDERS Col#: 1
    LVLS: 2 #LB: 3307 #DK: 1500000 LB/K: 1.00 DB/K: 1.00 CLUF: 24045.00

    Now joining: ORDERS[ORDERS]#0
    NL Join
    Outer table: Card: 6001215.00 Cost: 2994.16 Resp: 2994.16 Degree: 1 Bytes: 6
    Inner table: ORDERS Alias: ORDERS
    Access Path: TableScan
    NL Join: Cost: 27976374778.51 Resp: 27976374778.51 Degree: 0
    Cost_io: 27873478291.00 Cost_cpu: 2386397111117456
    Resp_io: 27873478291.00 Resp_cpu: 2386397111117456
    Access Path: index (index (FFS))
    Index: PK_ORDERS
    resc_io: 633.55 resc_cpu: 203550602
    ix_sel: 0.0000e+00 ix_sel_with_filters: 1
    Inner table: ORDERS Alias: ORDERS
    Access Path: index (FFS)
    NL Join: Cost: 3854751895.27 Resp: 3854751895.27 Degree: 0
    Cost_io: 3802081121.00 Cost_cpu: 1221551742006481
    Resp_io: 3802081121.00 Resp_cpu: 1221551742006481
    Access Path: index (UniqueScan)
    Index: PK_ORDERS
    resc_io: 1.00 resc_cpu: 15293
    ix_sel: 6.6667e-07 ix_sel_with_filters: 6.6667e-07
    NL Join (ordered): Cost: 10714.35 Resp: 10714.35 Degree: 1
    Cost_io: 6722.00 Cost_cpu: 92591405803
    Resp_io: 6722.00 Resp_cpu: 92591405803
    Access Path: index (AllEqUnique)
    Index: PK_ORDERS
    resc_io: 1.00 resc_cpu: 15293
    ix_sel: 6.6667e-07 ix_sel_with_filters: 6.6667e-07
    NL Join (ordered): Cost: 10714.35 Resp: 10714.35 Degree: 1
    Cost_io: 6722.00 Cost_cpu: 92591405803
    Resp_io: 6722.00 Resp_cpu: 92591405803
    Best NL cost: 10714.35
    resc: 10714.35 resc_io: 6722.00 resc_cpu: 92591405803
    resp: 10714.35 resp_io: 6722.00 resp_cpu: 92591405803

    Kindly expalin how the cost has been calculated. This does not follow the traditional nested loop cost formula as mentioned in the book.


    Rekha Singhal

    Comment by Rekha Singhal — March 27, 2012 @ 8:46 am BST Mar 27,2012 | Reply

    • Hi

      I’ve also observed such behavior some time ago.

      Comment by Timur Akhmadeev — March 27, 2012 @ 9:40 am BST Mar 27,2012 | Reply

    • Rekha,

      This isn’t a forum, so the very least you could do is find a posting which seems to be relevant to your question rather than posting it at random. I will be deleting your question in a couple of days; in the meantime Timur seems to have pointed you to some interesting related information.

      You might note that you haven’t included the Oracle version number in your question – and there have been a number of patches, and upgrades even, since the book came out. I believe the book does point out, by the way that the cost of the index-access for a nested loop is reduced by one if the index is unique – and that’s probably part of the change you see. There’s also a sanity check (not mentioned in the book) that limits the total cost of all the accesses into the index – something along the lines of the cost not exceeding the cost of accessing the entire index once, possibly through an index full scan.

      Comment by Jonathan Lewis — March 31, 2012 @ 7:28 am BST Mar 31,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 5,218 other followers