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

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.
Received on Tue Sep 19 2000 - 08:30:54 CDT