Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

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

# RE: Three billion logical reads?

From: Koppelaars, Toon <T.Koppelaars_at_centraal.boekhuis.nl>
Date: Fri, 26 Oct 2007 14:08:52 +0200
Message-ID: <1247DEDC2684644C93827EB6FDF47F9A0320B4E6@SRVEVS1.boekhuis.nl>

David,

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

< outer joins against non-unique indexex >

This means those twelve tables are all child-tables to the 80 million table?

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?)).

< 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).

Are there any predicates against those twelve other tables?

Could you give us the query?

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

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:08:52 CDT

Original text of this message

 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US