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.
Received on Tue Sep 19 2000 - 02:20:53 CDT
