Re: Designing database tables for performance?

On Feb 19, 5:03 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Timasmith wrote:
> > On Feb 18, 8:00 pm, "Mike Preece" <mich..._at_preece.net> wrote:
>
> >>On Feb 9, 5:04 am, "Timasmith" <timasm..._at_hotmail.com> wrote:
>
> >>Are you interested in logical or physical performance?
>
> Oh my! Can anyone imagine anything more embarassing than Mike's
> question? Should we all point and laugh now?

Welllll.... I think they are laughing, but not at who you think. See http://www.diku.dk/undervisning/2003f/729/papers/millsap.pdf

>
>
>
> > When tables get that big the data is rarely in memory and so while
> > perhaps some of the indexes will be, I have to say physical
> > performance assuming reads from disk. Seems like I got some good
> > ideas from the posts, in a few weeks I will mock it up and see how it
> > flies with the various options.-

That may or may not be true for the particular table, depending on your exact design, implementation and access patterns. Once you (Timasmith) get it up and running in a realistic high-load test, check out v$bh and see if the keep pool might help. You may find something like the short-term query will "naturally" get blocks with associated data that happened to be added contiguously in the orders, while the long-term item query might benefit from keeping an index.

jg

--
_at_home.com is bogus.
"...maybe what's true for Hollywood movies is true for Oracle too:
Some of us really like to see guts." - Jason Bucata