| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: The Admin's point of view on DB users best practices
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:ksuu20h48pu7nem73e1t66lpqb6a5pieaj_at_4ax.com...
> >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.
Er.....
no. Unless I'm very much mistaken or misunderstood.
> >> 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?
PKs that are generated via sequence, ascending usually. As opposed to natural PKs, which are just the text or number value that identifies the row.
Say for example, a codes table.
you can have it as:
CREATE TABLE MY_CODES (
CODE_ABBREV VARCHAR2(10) NOT NULL,
CODE_TEXT VARCHAR2(500) NOT NULL
);
ALTER TABLE MY_CODES ADD CONSTRAINT CODES_PK PRIMARY KEY (CODE_ABBREV);
with CODE_ABBREV being used as the FK on related tables.
or as
CREATE TABLE MY_CODES (
CODE_KEY NUMBER NOT NULL, CODE_ABBREV VARCHAR2(10) NOT NULL, CODE_TEXT VARCHAR2(500) NOT NULL
There are big advantages one way or the other. I tend to use the second, it lets me easily update CODE_ABBREV if a user has mistyped it. Without huge updates on all related FKs.
> Yes, it is me who is going to suffer from these bitmap indexes. Sooner
> or later, a pitfall with catch me.
Well, those indexes make sense if you can use more than one of them in predicates and benefit from the very fast XOR for row elimination in a join, for example. Otherwise, just because a column has low cardinality doesn't mean you must use a bitmap.
It is a combination of two things that gives the bitmap index its performance advantage: the storage of the index as a bitmap and the use of the index IN COMBINATION with other bitmap indexes in query predicates. If you don't get this combo, then it's not worth using bitmap indexes at all. Except in highly abnormal cases.
> 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.
Exactly. Highly variable.
> 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.
Not really. The admin should be able to move anything anywhere as required by performance considerations. What if he/she wants to share a tablespace between apps because they have tables of similar storage characteristics and use? Leave the physical placement out of the DDL, it's the responsibility of the admin person to figure that one out.
> 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.
Partial results. For example, someone wants to build some aggregate results to be used in the same session later on? Or build up results as the transaction progresses? Assuming a PL/SQL table can't be used for the same purpose.
> What about "Discoverer"?
Yuck! Ever tried to redo a report in Discoverer when the
db design has been modified? All you need is a changed FK
or PK and bang: there goes the report down the drain!
DAMHIKT...
> What about "Oracle Power Objects"
Never used it, can't comment.
> 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...
Hehehe! Of course. Doesn't matter what they do: they are users, period!
:D
Best approach for these guys is to give them views and/or functions
returning a cursor. That way they get to say what they want, you
control how good the SQL really is, and all are happy.
> Sent you an email, hope it gets through.
Not yet. Is yours up on the header of the msg OK? Anyways, I only have it ready Wednesday.
-- Cheers Nuno Souto wizofoz2k_at_yahoo.com.au.nospamReceived on Mon Feb 16 2004 - 04:30:13 CST
 |
 |