RE: Three billion logical reads?

From: David Aldridge <david_at_david-aldridge.com>
Date: Fri, 26 Oct 2007 05:22:11 -0700 (PDT)
Message-ID: <884404.6285.qm@web806.biz.mail.mud.yahoo.com>

Thanks Toon, comments in-line below.       

"Koppelaars, Toon" <T.Koppelaars_at_centraal.boekhuis.nl> 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>
  FROM

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

  smaller_tab01 d,
  ...
  smaller_tab10 x
  where

  a.par_col = b.id(+) and
  a.chi_col = c.id(+) and
  a.col3 = d.col and
  ...
  a.col10 = x.col(+)
   

  Toon           

---

  

  From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Aldridge Sent: vrijdag 26 oktober 2007 13:45
To: oracle-l_at_freelists.org
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:      

    80,000,000*12*(3+.5)      

    ?      

    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.

--
http://www.freelists.org/webpage/oracle-l

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