RE: Three billion logical reads?
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: "Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 26 Oct 2007 05:22:11 -0700 (PDT)
Thanks Toon, comments in-line below.
"Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of David Aldridge
Sent: vrijdag 26 oktober 2007 13:45
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
