Re: separate data/inidex

In article <37fab3ab.0205041701.508e934e_at_posting.google.com>, you said (and I quote):

> improvement in query times and in general overall performance. I can
> only go with what works, in this situation it made a definate
> improvement.

Absolutely. That is the most important thing to take away from this thread: test, verify and if you find that something works well and consistently so, then use it. And keep re-testing: next version of your software or next version of Oracle things might not be the same. By all means get and share ideas with others. Either through seminars, newsgroups or mail lists. But use of the grey stuff between the ears is not excluded.

>
> RAID blurs everything, the biggest problem with RAID is not going wide
> enough (not enough IO to satisfy demand) and not having proper stripe
> width.

Hey, that's not blurring! That's making it a lot easier. With non-RAID the number of parameters we have to contend with and their interactions is horrendous!

> In one test with a table with a large number of
> extents (>1000) an update involving a large percentage of rows
> required 14 hours. A rebuild of the indexes used by the update process
> reduced it to 12. An export and import with compress equal to NO
> reduced this to 10 or so, an export and import with compress=y reduced
> this to 6. Over a 200% improvement by just eliminating multiple
> extents.

Yes, but you probably would find (if you had the time to test it) that if you reduced the number of extents from 1000 to say 100, you'd have gotten the same improvement. Ie, it's not the reduction to 1 extent that is important. It's not letting the table go ape on extents. Say, over 300. This again varies with Oracle versions.

> I also suggest on systmes that allow it, but especially NT, disks be
> defragmented on a frequent basis for heavily updated disks. However,
> RAID should not require this.

Yes, most definitely. But this has to do with NT itself, not Oracle.

>
> I am afraid I have to go with the evidence of my own experiences,
> eliminating contention, optimizing index (and table) storage
> structure, minimizing extents all have their place in the DBA toolkit,
> even in this modern chaotic access environment we all operate in.
>

Absolutely. It's a combination of tactics that can help avoid problems. Not a single silver bullet.

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam
(I don't have time to read books,
and they want me to write one?)