Re: Oracle's Myth: keep tables and indexes in separate tablespaces

"Joel Garry" <joel-garry_at_home.com> schrieb im Newsbeitrag news:91884734.0310171628.5e5dc2a8_at_posting.google.com...
> So why do people think interspersing indices will hurt? Consider a
> master/detail relationship - Order headers and lines. The order
> numbers are always increasing, and periodically the older ones are
> sent elsewhere. So a certain number of headers will fit in the blocks
> of this table H, and a different number of lines will fit in the
> blocks of that table L, such that one of the tables will be extending
> faster than the other, and of course the index blocks I will extend at
> a different rate. So you might wind up with this:
>
> HLIHLIHLLHILHHLLIHHHLLIHLLIIHLIHHHLLLLLLIHHLLIHHLHLHLHII...
>
> (Hmm, shoulda called the index E :-)
>
> Anyways, now we are running some damn report, and it does a full table
> scan of just the headers, and another of just the lines, because it's
> some old OCI piece of, uh, work that does it's own join and then later
> gets by rowid's. It becomes difficult for people to accept that the
> disk device is able to scoop up all the H's, even with linked lists,
> faster than if they were contiguous, since there's some kind of
> multiblock read going on - and with hundreds of tables in an actual
> system, the dispersal would be higher. It is difficult for people to
> accept that Oracle will not reload them contiguously with an import
> (especially if they've looked at a file with a debugger after a
> compress=y, and they see HHHHHHH...LLLLLLLLLL...IIIIII, even with
> LMT). It is difficult for people to accept that unix will scatter the
> disk blocks about invisibly, especially difficult on a newly created
> file system that have big data files that will never be deleted.
>
> Now archive all those old records. All those beginning blocks are now
> free, and how will Oracle load them?
>
> Dang, sounds like these critical tables and indices should _each_ be
> in their own tablespace.

So, there are two problems:
- The OS is looking for free blocks anywhere. Ok, so you might allocate   the datafiles first and large enough. That's IMHO a job for a scheduled   maintenance once you know how much data you're dealing with. - The Db is looking for free extents anywhere in the datafile. Ok, set

   the extent size properly and the skips won't matter anymore. Not having    tried this I would probably start with an extent size of n    multiblock_read_counts. Received on Mon Oct 20 2003 - 04:26:11 CDT