In article <01bda914$bd98f500$ec05bfce_at_default>, Paul G Young
<pgy_at_magma.ca> writes
>It's been my experience that a full system application to have three basic
>modes which have different processing characteristics vis-à-vis indexing::
>1. load into system - all write, no read: this is addressed by Oracle's
>direct loader - good - I never use it.
tis good, I use it a lot.
Its a shame there is no programmatic interface (pro*c/oci etc) - I
always find it untidy to have to scan logfiles for errors.
(can't rely on exit code, ie: load fails, but loader "works" = success).
>2. process data - balanced read, lot's of write: numerous indexes are a
>serious performance liability.
oh yeah.
>3. report information - little write, lot's of indexed reads. To provide
>reasonable performance and a multitude of access paths, a lot of indexes
>are required.
>There is often good reason to keep these data together in the same tables
>on the same databases.
>
agreed.
>Clearly processing and reporting are at odds without any means to manage
>them. I was told (apparently incorrectly) by Oracle that with Oracle 8's
>partitioning feature, indexes could be controlled by partition. That is,
>partition A could have minimal indexing, while at the same time partition B
>could have reporting-level indexing. This appears not to be the case.
>
>Any suggestions?
>
Hmmm, not tried that. I believe it's possible though.
Subject to testing the above, my approach here would probably be to
create as many copies of the table as required and index how you wish -
maybe with a union view if needed. (I know I know...)
A sort of do it yourself partitioning.
It is an area where one needs details details and time to ponder/test.
I've just done one system where I drop all reporting indexes then
do all the loading and updating then recreate the indexes as nologging.
It worked out *lots* faster in that system (than having index
maintanance overhead during processing).
--
"The floggings will continue until morale improves."
Received on Tue Jul 07 1998 - 17:49:05 CDT
