Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: The Admin's point of view on DB users best practices

Re: The Admin's point of view on DB users best practices

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Sun, 15 Feb 2004 15:39:34 +0100
Message-ID: <ksuu20h48pu7nem73e1t66lpqb6a5pieaj@4ax.com>


wizofoz2k_at_yahoo.com.au (Noons) wrote:

>Make sure that where needed, concatenated indexes (multi-column
>indexes) are created.

Yes, and if they span all involved columns accross tables, then the join conditions point straight to the rows needed. AFAIK it is possible to build an index accross on columns accross different tables.

>> 7) Do not delete large amounts of data from a table. Transfer data to
>> be kept to another table instead, and rename as appropriate.
>
>Careful when there are heaps of FKs and declarative RI.

Thanks, good point.

>> 10) Avoid right-handed indexes based on a growing sequence.
>
>That just about rules out using surrogate keys and they ARE needed!
>I'd re-examine this one.

Sorry, what do you mean with surrogate keys?

>> 11) Be aware of the degree of selectivity of indexes. Use B*tree
>> indexes for large cardinality columns and bitmap indexes for low
>> cardinality columns.
>
>I'd go as far as saying: do not use bitmap indexes unless you
>are dealing with a data warehouse. Too many restrictions and
>potential problems for normal DSS or OLTP volatile use.

Yes, it is me who is going to suffer from these bitmap indexes. Sooner or later, a pitfall with catch me.

>> 19) How to cope with the "Snapshot too old" problem.
>
>Take your pick, really. 17) helps a LOT.

At times, it is as simple as agreeing upon who is going to start a long running job at any time, and who is going to wait to start its own.

>> 23) The only tablespace that should be explicitly named is the one for
>> indexes.
>
>No. Tablespaces should be up to the DBA to sort out. That's physical
>placement of data and users and designers have NOTHING to do with that.

But then the Admin has the only option to do an "alter table <user.tab> move" or rebuilding indexes to put the segment in the right place later - better to do it right at creation time.

>> a) Guidelines for use of temporary tables, examples (improving
>> performance).
>
>Assuming "permanent" temporary tables (scratch-pads):
>Use surrogate keys ALWAYS for temporary tables and a sequence to
>generate them together with the session_id. Make sure there is
>ALWAYS a point in the processing where use of temporary tables
>goes quiescent. Then use that space to clean them up, maintain
>indexes, whatever. Make sure you have control over hints or
>stats with temp tables: they are so volatile that normal
>stats gathering becomes totally useless. Go for hints.
>Or use 10g.

I meant really temporary tables, holding data for the duration of a session or even a statement. Interim changes on temporary tables won't go to redologs (as I understand), operations on these tables are faster. Put data into "normal" tables when intermediate dml operations are finished to be commited.

I am just missing a good example.

>
>> c) Best GUI or RAD or IDE ("grafical user interface", "rapid
>> application development", "integrated development environment") for
>> Oracle [some use Access as a Front-End and get into problems]; either
>> an Oracle product or third party one (?); recommendations on
>> advantages/disadvantages.
>
>Forms and reports 9iDS, without a doubt. Closely followed
>by VB and JDeveloper.
>

What about "Discoverer"?
What about "Oracle Power Objects"

I am desperately looking for a substitute of MS Access, before it is too late for people to readapt.

>
>Ah, they think you have the accelerator pedal all for yourself, eh?

These are not IT people, but chemists, biologists, etc. They know very well what the data is all about, but lack enough training to deal with Oracle reasonably. They do expect the IT specialists to solve any problem they did not cause, including slow queries...

>I could go on for days and this is already long.
>I'm trying to put some thoughts together for a
>talk on design at the local UG next week, will send you
>whatever I come up with if you want. Send me a target
>address, use d c s 2 k at optusnet
>dot com dot au to communicate with me.

Sent you an email, hope it gets through.

Thanks
Rick Denoire Received on Sun Feb 15 2004 - 08:39:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US