Hello all,

I'm trying to sort out how to make my query as optimal as possible. And doing a
fair amount of online reading in the process, but haven't found the definitive
answer to a few Qs, so here goes...

First off, does the order in which fields are defined within an index matter to
the query optimizer? For example in my table TableX with columns Col1, Col2, and
Col3. Do these indexs differ as far as the optimizer is concerned:

CREATE INDEX IDX_TEST1 ON TABLEX (COL1, COL2) vs

CREATE INDEX IDX_TEST1 ON TABLEX (COL2, COL1)

Again with the above index defs, if I'm doing a JOIN to TableX on Col1 - will
either index be used? neither index is used?


Next Q...
We never allow users to access tables directly. They always access views. For
the sake of simplicity, let us say that there is a date column which is checked
within the definition of the view:

Create View MyView(.....)
select * From MyTable
where DateCol > [someDate]


New let's say we are doing a join with the above MyView. Let's say it is
something like this:

Select x.Col1, x.Col2, x.Col3,
v.*
From TABLEX x
JOIN MyView v on (v.someCol = x.someCol)

If I wanted to optimize the above, do I want an index on:
1) v.someCol
2) x.SomeCol
3) v.DateCol
4) v.someCol and v.DateCol
5) Some other combination of above

Last Q...
Is it the case that the Query Optimizer will use an index, even if the
statistics for that index are very poor? Let's assume the stats are .5 (about as
bad as they can be). Still it cuts the number of entities roughly in half
(assuming the column data is split 50/50). So does it use this index? Or are
their cases where the overhead of using an index means that performance will go
backward and hence opts to do a natural join instead?

and if it is /heLen answering... be gentle :) Love the book! Sad you stopped
making it in paper form.

-randall


[Non-text portions of this message have been removed]