Re: can a index consist of 3 columns (Oracle newbie)

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?

is there any real use of deferrable indexes that i can't see?

or is it just the feeling of being in control of things when you enable/disable your indexes?

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.

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.