Xref: alice comp.databases.oracle.server:24262 Path: alice!news-feed.fnsi.net!news2.ais.net!jamie!ais.net!btnet-peer!btnet!dispose.news.demon.net!demon!news.demon.co.uk!demon!rwx777.demon.co.uk!steve From: Steve Haynes Newsgroups: comp.databases.oracle.server Subject: Re: Alter index enable would be nice Date: Tue, 7 Jul 1998 23:49:05 +0100 Organization: . Message-ID: References: <01bda914$bd98f500$ec05bfce@default> X-Trace: news.demon.co.uk 899923851 nnrp-04:21509 NO-IDENT rwx777.demon.co.uk:158.152.187.113 X-Complaints-To: abuse@demon.net MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=iso-8859-1 X-Newsreader: Turnpike (32) Version 3.05 Lines: 41 In article <01bda914$bd98f500$ec05bfce@default>, Paul G Young 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."