Comments below
HJR
--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------
"Atta ur-Rehman" <atta707_at_my-deja.com> wrote in message
news:8q9etv$fiv$1_at_nnrp1.deja.com...
> thanks again, howard. okay, i was looking at the deferrable primay key
> indexes and yeah they are enforced thru nonunique indexes. my question
> is, given that you're an ardent preacher for them, does it really make
> any difference, in terms of performace, to have an index declared as
> deferrable or otherwise?
>
To be picky, it's not the index that gets declared as deferrable or
otherwise: it's the constraint. Non-deferrable constraints get unique
indexes; deferrable get non-unique indexes.
But in any event, there is zero measurable performance degradation when
uniqueness is enforced with a non-unique index as opposed to a unique one.
Quite the opposite, in fact: data loads go a bit faster (when the constraint
is actually deferred, rather than just being deferrable). And, of course,
if you have self-referencing foreign keys, or an application that inserts
data in a strange order such that it's all OK by the time it's finished, but
all over the place during any particular part of the insert, having deferred
constraints is the *only* way of actually getting the job done.
> is there any real use of deferrable indexes that i can't see?
>
Deferrable *constraints* -absolutely. Self-referencing foreign keys,
otherwise you can never do the initial load of the data (unless you defined
or enable the constraint AFTER the load, which is all you could do in Oracle
7). And in the real world, I once developed an applicaiton that had to
produce inspection rounds from asset data. The primary key was the visit
order number, but I couldn't know *that* until I had my random sample of
things to visit safely selected (and the relevant table populated, without
its primary key). So I needed to be able to half-populate a table, and
*then* assign the primary key.... this wasn't using Oracle, and I had to
jump through hoops to do it. Life wuld have been a whole lot simpler with a
deferrable constraint (because, by the time of commiting, everything would
have come good).
> or is it just the feeling of being in control of things when you
> enable/disable your indexes?
>
That too. But you could achieve the same degree of control by defining your
*own* indexes on the relevant field(s) -choose unique or non-unique, it
doesn't matter. Neither will get dropped when you disable the constraint
because the index was manually created by you first. In other words, for me
the issue is not really about whether uniqueness is enforced with a unique
or non-unique index, and if it's *that* that is the only worry for you,
create your own unique indexes before defining constraints... you get the
uniqueness, and you get the control. But the real issue is that deferrable
*constraints* can sometimes be a life-saver.
> i would tend to think that RDMBS engine has to do a tad more to check
> the current status of a deferrable index to decide wheather to wait for
> a commit or do the check immediately.
>
Not that I've measured, anyway. In any event, it's a one-field check to see
whether an index is deferrable or not, nad one other field to see whether
it's actually deferred or enforced immediately. And that record is already
accessed at parse time. So I don't think it's my measuring
techniques -there really isn't much work to do.
> chao then,
>
> :) ATTA
>
> In article <39c75c14_at_news.iprimus.com.au>,
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > I think if a newbie was asking if a 3-col composite index was
possible in
> > Oracle, and we were trying to keep it as simple as possible, the word
"yes"
> > springs to mind as the best possible answer.
> >
> > Once you go down the road of advising on syntax, however, I suspect
that it
> > might be a tad important to actually get the syntax accurate...
> >
> > "Make 'em deferrable" is short, sharp advice I'd teach anyone, from
day one.
> >
> > And "house the Index correctly" is likewise pretty basic stuff.
> >
> > But I take the point about prolixity!
> >
> > Have a good one,
> > HJR
> >
> > "Atta ur-Rehman" <atta707_at_my-deja.com> wrote in message
> > news:8q7ba1$uui$1_at_nnrp1.deja.com...
> > > Thanks Howard, for your comments. For a newbie asking if he could
have
> > > a 3-cols composite index in Oracle, this much detail would have an
> > > overkill! just a thought, ya see;)
> > >
> > > cheers,
> > >
> > > :) ATTA
> > >
> > >
> > > In article <39c7055a_at_news.iprimus.com.au>,
> > > "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > > > Comments below
> > > > HJR
> > > > --
> > > >
> > > >
> > > > "Atta ur-Rehman" <atta707_at_my-deja.com> wrote in message
> > > > news:8q6rac$d7e$1_at_nnrp1.deja.com...
> > > > > as you said (entity_code, version_index, sector_code) columns
are
used
> > > > > to identify each row in the table, these three columns, then,
make a
> > > > > composite primary key for the table. I'm just wondering if by an
index
> > > > > you mean a primary key. anyway primary keys are always have a
> > > > > associated unique index with them.
> > > >
> > > > Depending on your version, that last statement is not true. If
you
declare
> > > > your primary key constraint to be 'deferrable', assuming you have
Oracle 8
> > > > or above, then it will be enforced with a non-unique index. I
tend to
> > > > recommend you use deferrable constraints, since if the constraint
is
> > > > enforced with a unique index (ie, it's non-deferrable), disabling
the
> > > > constraint means the index is dropped automatically. If you defer
your
> > > > constraint (or even make it deferrable initially immediate), then
the
> > > > non-unique index enforcing it is *not* automatically dropped.
> > > >
> > > > In either case, uniqueness is enforced appropriately, and
performance
is the
> > > > same in either case.
> > > >
> > > > My problem is with indexes taking it upon themselves to do the
disappearing
> > > > act: I'd prefer to be in charge of that process, and drop them
manually when
> > > > appropriate.
> > > >
> > > > >so create a primary key on the
> > > > > table, and hence a composite unique index on the given three
cols
do:
> > > > >
> > > > > alter table article
> > > > > add primary key(entity_code, version_index, sector_code);
> > > > >
> > > >
> > > > I think you might want to look at this syntax a bit more closely.
> > > >
> > > > From memory, I think it should more accurately take the form
> > > >
> > > > Alter table xxxx
> > > > Add (Constraint <name> Primary Key (col1,col2,col3) )
> > > > deferrable initially immediate
> > > > Using Index
> > > > Tablespace indx01
> > > >
> > > > It's important to make sure the implied Index is created in the
proper Index
> > > > tablespace, otherwise it will end up housed in the default
tablespace
of the
> > > > User making the alteration -which is usually the Data tablespace
(which is
> > > > exactly where you DON'T want it of course)
> > > >
> > > > > this command would create a primary key for your table, if the
existing
> > > > > data doesn't violate the primay key constraints. if you're
interested
> > > > > in [unique] index then:
> > > > >
> > > > > create [unique] index your_index_name
> > > > > on article(entity_code, version_index, sector_code);
> > > > >
> > > >
> > > > Given the general thrust of what you've posted here, I might have
expected a
> > > > recommendation to declare a unique *constraint*, which again can
be
> > > > deferrable, deferred or initially immediate
> > > >
> > > > > hope that helps.
> > > > >
> > > > > :) ATTA
> > > > >
> > > > > In article <ssd5rebkqldp14_at_corp.supernews.com>,
> > > > > mr_oatmeal <mr_oatmeal_at_hotmail.com> wrote:
> > > > > > Hey All,
> > > > > >
> > > > > > We don't have a DBA here, so i've been trying to figure things
out on
my
> > > > > > own....
> > > > > >
> > > > > > I need to add an index to a table--article. In order to find
a
unique row
> > > > > > you have to query based on 3 columns (entity_code,
version_index,
> > > > > > sector_code), all of them are varchar2. What would be the
best
way
to
> > > > > > index this table. The table has about 100,000 rows.
> > > > > >
> > > > > > table: article
> > > > > >
> > > > > > entity_code varchar2
> > > > > > version_index varchar2
> > > > > > sector_code varchar2
> > > > > > other_column
> > > > > > other_column
> > > > > >
> > > > > > Thanks,
> > > > > > mr_oatmeal
> > > > > >
> > > > > > --
> > > > > > Posted via CNET Help.com
> > > > > > http://www.help.com/
> > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > getting the meanin' of data...
> > > > >
> > > > >
> > > > > Sent via Deja.com http://www.deja.com/
> > > > > Before you buy.
> > > >
> > > >
> > >
> > > --
> > >
> > > getting the meanin' of data...
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> --
>
> getting the meanin' of data...
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Sep 21 2000 - 00:00:00 CDT
