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

Thanks very much indeed, Howard. Prolixness has its justification at times and this time round it really did;))

By the way, if you could possibly point me to some further technical readings about how does Oracle actually handle deferred *constraints* untile the commit statement. Is it a normal insertion and then the commit/rollback depending on the constraint violation or something different?

Thanks.

ATTA In article <39c914a3_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: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.

>

>


--

getting the meanin' of data...


Sent via Deja.com http://www.deja.com/
Before you buy.