Re: Large table and buffer cache concerns

On Apr 11, 8:03 am, deva..._at_gmail.com wrote:
> > Shouldn't you be better concerned by the table design?
> > Table j contains a cartesian product! Does that sound right?
> > Sybrand Bakker
> > Senior Oracle DBA
>
> I would not consider table j to be a cartesian product, it is an
> associative table.
>
> For example, let's assume we want to track customers (table c) and
> books (table q). I want to track which books a customer has read.
> Enter table j.
>
> For each customer c, I "can" have as many entries in table j as I have
> records in books q.
>
> Hope the example helps to explain the logic behind the table design.

You want to use option 1, but don't reduce the blocksize. You will likely (depending on details) have the rows in table j in contigous rows, so a bigger blocksize will read into the buffer the blocks you will be needing when you get the initial rows without excessive block requests. The two important questions I couldn't quite get from your posts: will you be querying a large proportion of table j at times (3000 customers all coming on at 9AM?), and what kind of _updates_ will you be doing on table j with multiple users looking at the same blocks? It's the latter that will really mess with you. Believe in normalization and the Relational Way! Worry less about what you can have and more about correct design. That blob stuff is just asking to prove Sybrand an optimist.

Beyond that, you can only empirically determine what your code is doing, see the manuals and Jonathan Lewis' site about v$bh monitoring when you have something to test. And digest Kyte's books if you haven't already.

jg

--
@home.com is bogus.
"If you feel the need to investigate, you will be able to waste
endless amounts of time trying to discover and catalogue the reasons
for all 806 calls that 10.2.0.3 offers." - Jonathan Lewis