Re: primary key index

Hello,

columns in select i.e.
select docID, docName, docCODE, docRelevantDate from DOC
where .....

DocID is primary key, but big table, have lot of results in query, and description data for DOC data
usually are (docName, docCODE, docRelevantDate). Client side want relevant data (of course, well designed !)

from this point of view I have PK on DOC (docID), and additionaly helpful key
ix_docrelevantdata (docID, docName, docCODE, docRelevantDate) and my select is much quicker!, as we know values of columns are in Btree index.

Problem is that I need another select for another functionality and special kind of problem is function on column (I know how to build this index), ........

Where boundaries are? (common sence?)
I can have i.e. 1 pk, 3 bitmap keys, and 2 complex index like I describe? What if doc have 1000000 columns?, how increasing of columns reflect on table&index sturcture?

"Frank" <fvanbortel_at_netscape.net> wrote in message news:3E5A7396.9030006_at_netscape.net...
> iskon.hr wrote:
> > hello,
> >
> >
> > using usual general data in select statement from table like :
> > 1. primary key
> > 2. names
> > 3. code
> > 4. some kind of general info... etc
> >
> >
> > tells me to expand primary key with these values!
> > (because B-tree index take values of column).
> > But, in the other hand I have primary key and declaring same index twice
> > (pk,col1,col2...) is not smart decision!
> >
> > Any suggestions???
> >
> >
> > MB
> >
> >
>
> Not sure what you mean. Adding columns to your primary key?
> If you have the need to do so, your model was bad to start with
> (or it has severely changed, of course).
>
> Adding indexes? Is not the cure for all problems. Inserts will
> be slower; compary a table with just a primary key to a table
> with 23 indexes, one of these being the primry key.
>
> You can add an index, but is is not neccesary to do if the columns
> are already in an index. It may help performance selecting an extra,
> not needed, field if that will make the query use an index.
>
> --
> Regards, Frank van Bortel
>
Received on Fri Feb 28 2003 - 10:32:52 CET