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

Rick Denoire <100.17706_at_germanynet.de> wrote in message news:<mras209smjn3g6ndo2o6119f5dhoo666t5_at_4ax.com>...

> I am preparing a small presentation offering DB users some assistance
> about "best practices" when using Oracle, and defining the general
> policy in the Admin scope. Most of the users are not specially
> proficient in SQL and are even completely ignorant about DB internals.
>

Hmmm, I know where you're coming from, but I tend to shy away from "best practices". 9 times out of 10 they are taken by the users totally out of context, made into "rules NEVER TO BE BENT" and the result is another bunch of myths!

The same reason I refuse to create "site standards" and other such crap. Standards are created by industry organizations, not by a site! At best, we're talking guidelines. I'm not pedantic enough to allow myself to replace an entire industry...

> Well, I am not specially proficient in SQL either, but I would never
> do a "delete * from <table>".

Neither would anyone... :)
Anyways, I've added bits and pieces here and there that I consider important as well as a few comments where I feel a contribution is needed.

> 3) Create indexes based on the most frequently used predicate columns.

I'd say: IF you have to create indexes, then follow this rule.

3.5) Do NOT create too many indexes in a highly volatile table. What is "too many"? If the table is updated (INSERT/DELETE/UPDATE) by every single user on every single operation, then 2 is too many. If not, then I'll go up to 6, tops. If the table is never updated (lookup), then make it read-only and go for it on indexing. But that is very rare.

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

> 4) When you create an index, confirm that it is actually being used in
> the query. Avoid access to an indexed column through a function,
> otherwise the index will be ignored.

Corollary: NEVER create a design with a time series (start_date, end_date) where the "current" record has END_DATE is NULL. It simply will NEVER be picked up by an index and it WILL stop indexes including that column from being used. ALWAYS set the "current" end_date to an agreed value sufficiently well off in the future that no one will hit it. Then make a function to return that value and use that function as an "=" predicate whenever you want to find the active row.

This one is directly responsible for 50% of the performance disasters I've seen out there!

> 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.

> 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.

> 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.

> 12) Avoid sort, distinct, union when possible [the 24GB TEMP TS gets
> full up to the top - how do they manage to do that?]; "union all" does
> not need sorting.

Yes, most definitely. DISTINCT to me usually means someone didn't bother to figure out how to join a table to others to eliminate "duplicates" from the result. Without thinking that distinct forces a full result retrieve, followed by a sort unique. This one is another onme of those I've seen time and time again out there, with usual disasters as a result.

> 13) Avoid IN lists with more than aprox. 10 entries.
> [but how to rewrite the code?]

EXISTS?
> 14) Avoid NULL values in indexes columns.

Yes. Make sure that a NULL means a NULL. A column set to a NULL value means ALWAYS UNKNOWN, not "TBD/TBA", or "entered later" or something like that. If those are the meaning, then get a value to represent it. Not NULL!

> 15) Prefer joins instead of subselects.

Yes. But subselects ( I assume you mean queries in column list) are the ONLY way to implement certain combinations of outer joins. So this is definitely a guideline rather than a rule.

> 16) Avoid outer joins and full outer joins [but how to rewrite the
> code?].

Sub-selects? :)

> 17) Avoid PL/SQL loops of single steps if it can all be done in SQL.
> [examples?]

YES!!!!!! Process data "set at a time", not "row by row".

> 19) How to cope with the "Snapshot too old" problem.

Take your pick, really. 17) helps a LOT.

> 20) Avoid filling rows in two steps: insert and update. Better combine
> rows together and insert them in one strike [example?]. Even insert
> into several tables is possible (9i).

Yes. Nothing wrong with 1-1 relationships, where the additional table stores the odd values when and if they occur and the original table is freed up of lots of NULLable columns. I'm all for it, although I keep getting a lot of objections from design purists.

> 21) Always name your objects (for example: indexes). System given
> names can cause trouble for the Admin.

Can you tell that to the Oracle Designer mob, pretty please? :)

> 22) Do not specify any storage clauses without a special reason. The
> Admin should be consulted in this case.

Yes. Never make a design dependent on physical storage clauses.

> 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.

> 25) If the use of your scripts is not restricted by yourself or if
> they are going to be used reliably on a regular base (for example,
> monthly reports), then write your statements based on views and/or
> synonyms to avoid a direct dependency on the underlying schema. If the
> schema (removing column etc.) is changed, a redefinition of the view
> will be enough not to break all dependent scripts.

I used to refuse this one, but nowadays I've turned around. It is so much better to write for a view and not have your code break if the DBA needs to make a change to a table for optimization reasons! I'm all for views and synonyms nowadays and I'll just wear the additional overhead as a necessary evil. Trade-off, really: lose performance, gain flexibility. Fine by me.

> For more advanced users:

I assume you mean developers. I don't expect end-users to do direct SQL.

> 29) Using hints to improve performance

Yes, AFTER checking them with the DBA.

> Still missing:
>
> 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.

> 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.

> My problem with this is that users expect from me very specific
> information about how to make their queries run faster.

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

> and are mostly already enforced. What I need now is a compilation of
> understandable guidelines for sql writers. I can't find anything
> related in the Web.

Precisely. Not much around. And what there is is old, out of date or completely inapproriate for modern applications. Most unfortunate. Jonathan's book offers some ideas, so do Tom's. But the best around is still Dave Ensor and Ian Stevenson's books. A bit out of date but still tremendously useful.

> If you take a look at
> http://www.dbdomain.com/article8.htm
> you will realize that all this stuff is for the Admin in the first
> place, and not appropriate for casual DB users.

Exactly. Fine for those of us who enjoy nuts and bolts, tremendously boring and useless for everyone else.

> Your input will be high appreciated!

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.

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Sat Feb 14 2004 - 21:07:40 CST