Re: Separate Index or Composite Indice ?

"userjohn" <userjohn@xxxxxxxxxxxxxx> wrote in message news:13ddrbf3th7fsd8@xxxxxxxxxxxxxxxxxxxxx
10gR2

If you have :
2 columns on a hi-traffic transactional table
- one ("some_id") high cardinality
- and one ("yes_no") low cardinality

You use both columns in SQL - "some_id" is used in JOIN and "yes_no" as a predicate

Would you create normal index on BOTH , just "some_id" or a composite indice on "some_id" + "yes_no" ?


Can't really answer the question without a bit more info but some things to consider include:

Is this one SQL statement or are we talking about separate / differing SQL statements ?

Could the some_id column also be used as a predicate or will all resultant values of some_id be of interest ?

Is some_id a PK or a FK in this table ?

Are there other columns in the table (or could an index organized table be considered) ? If there are other columns, how frequently are they referenced ?

How large is this table and how large is the table (or tables) that it joins with ?

What's the data distribution in the yes_no column ? Is there a value which constitutes the vast percentage of rows or is yes and no reasonably evenly distributed ?

Note by placing the yes_no column first in a composite column, you might be able to compress the index a tad (depends on actual length of column values). Unlikely the other way around.

Note by placing the yes_no column first in a composite column, you can potentially use a skip-scan access if you happen to not reference yes_no in a predicate. Unlikely the other way around. So I would consider a composite index on yes_no, some_id a more likely combination than the other way around if you go down the composite index path although it does depend on the distribution of data on the yes_no column and how likely the column is referenced.

Some things to consider anyways.

Cheers

Richard


.

Relevant Pages

  • Re: The Structure of Aristotelian Logic
    ... I am having difficulties with the concept of DISTRIBUTION. ... asserts something about the whole of its denotation. ... subject-class from the whole of the predicate-class. ... anything of the whole of the denotation of the predicate. ...
    (sci.logic)
  • Re: The Structure of Aristotelian Logic
    ... I am having difficulties with the concept of DISTRIBUTION. ... asserts something about the whole of its denotation. ... anything of the whole of the denotation of the predicate. ...
    (sci.logic)
  • Re: Global Warming: Junk science at its [best] worst
    ... personal freedoms might be constricted by popular demand. ... which you deleted from your reply and provides some point of reference ... planets and not other planets, so that should be representative in ... composite of satellite data on the subject -- from the first orbital ...
    (sci.electronics.design)
  • Re: functor for serializing a reg_multi_sz
    ... "Marc Sherman" wrote in message ... > Since for_each takes the predicate by value and makes copies, ... > also be worried about passing a reference to the vector? ...
    (microsoft.public.vc.stl)
  • Re: Aggregation vs composition
    ... >> aggregation, take your pick of whichever seems conceptually more ... > The UML specifies the semantics of a composition (composite aggregation) ... > object has sole responsibility for the disposition of its parts" ... if more than one object has a reference to our "part" then no ...
    (comp.object)