Re: Large table and buffer cache concerns

<devalos_at_gmail.com> wrote in message
news:1176250100.425723.111570_at_y5g2000hsa.googlegroups.com...
> Hello all,
>
> I've an application developer with a rather unique request, for a
> High Volume OLTP system. I've yet to work with tables of this
> magnitude before so I'd love some second opinions.
>
> Essentially, a 400 million record table consisting of 43 byte records.
> 7 int and 2 date columns. Which accounts for approximately half the
> overall database storage. This is an associative table along these
> lines:
>

I've come into this a bit late, so may have missed some of the replies to date.

Your 'associative table' is a little suspect, in that you expect it to hold the Cartesian Join of the two base tables. Typically this would not be the case - my local library has about 10,000 members and about 150,000 books, but I know that I certainly haven't (and never will) get through all 150,000 books in my lifetime. In a scenario like this, the "associative table" is likely to hold only a tiny fraction of the Cartesian Join.

As far as handling the data fast enough is concerned, your comments (and Joel's) about co-location are highly appropriate. You stated requirements sound like a perfect match for an Index Organized Table with a primary key - picking up on your analogy - equivalent to (customer, video). All 1000 rows for a single customer would then fit into a single leaf block, and the random read problem ought to be minimised.

Some of the discussion in this thread suggests that your code may, however, expect to update all 1,000 rows simultaneously because of the nature of the application - or possibly the implementation. This is potentially a more serious performance threat than the basic co-location issue because of the overhead in redo generation.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html