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

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

--


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