Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Three billion logical reads?

RE: Three billion logical reads?

From: David Aldridge <>
Date: Fri, 26 Oct 2007 05:22:11 -0700 (PDT)
Message-ID: <>

Thanks Toon, comments in-line below.       

"Koppelaars, Toon" <> wrote:

        v\:* {behavior:url(#default#VML);}  o\:* {behavior:url(#default#VML);}  w\:* {behavior:url(#default#VML);}  .shape {behavior:url(#default#VML);}                David,

  Your formula assumes only one block access to each of the twelve other tables, in case of there being a matching record.    

  DA: Yes.    

outer joins against non-unique indexex
  This means those twelve tables are all child-tables to the 80 million table?    

  DA: No, just a badly designed/implemented CRM system :)    

  So there can be more than one matching record? Then you need to account for the clustering factor too: and factor that in into your formula (currently assuming only one block access for matching record(s?)).    

  DA: Actually that's true in one case, as two of joins are actually self-joins to the same 80 million row table, once to get potential parents (0 or 1 match) and once to get potential children (0 .. n matches). There may be a number of table block access required for the children but I'm looking more for a best-case scenario so i'm underestimating where there is doubt.    

outer joined to many smaller tables
  This sounds like the twelve tables are ‘lookup’ tables to the 80 million table? (i.e. the 80 million table is the child table, like in a star schema).    

  DA: Yes, mostly thay are, sometimes they are not.    

  There is some contradiction here...    

  Are there any predicates against those twelve other tables?    

  DA: No.    

  Could you give us the query?    

  It's very simple in form, with no predicates and just outer joins. It's an extract from a CRM system as part of an out-of-the-box ETL process. The rojected columns do require table access and can't be read from the indexes, so where a record is founf a table access is required in all cases ...    

  SELECT <column(s) from each table>

  80mill_row_tab a,
  80mill_row_tab b,
  80mill_row_tab c,

  smaller_tab01 d,
  smaller_tab10 x
  a.par_col = and
  a.chi_col = and
  a.col3 = d.col and
  a.col10 = x.col(+)



  From: [] On Behalf Of David Aldridge Sent: vrijdag 26 oktober 2007 13:45
Subject: Three billion logical reads?    

    Part 1: If I have a table of 80 million records and I outer join that to twelve other tables, and the tables are accessed with nested loop outer joins against non-unique indexes having a blevel of 3, and if there is only a 50% (say) chance of there being a matching record in those tables, then is the number of logical reads to perform the join equal to something in the order of:      



    In other words, around 3.3 billion logical reads? Or do I need more coffee this morning?      

    Part 2: What would you say is a reasonable ballpark time for a logical read, on a decently equipped server with SAN storage? Millisecond? Less?                  

    Background: I'm stuck in version 9 with large tabes outer-joined to many smaller tables -- no inut join swapping and hence no (reasonably efficient) hash outer joins.

Received on Fri Oct 26 2007 - 07:22:11 CDT

Original text of this message