Re: What index is prefered?

In article <7olvoe$rdo$1_at_news.netway.at>,   "Oleg Roshchin" <oleg_at_ca-ib.com> wrote:
> Hi all,
> Could you please help me in increasing performance of my queries?
>
> I have a table with near 15 fields: one of date type,
> one of number type (my value field)
> and all others of integer type (Foreign Keys) .
> The size of this table is longer than 200,000 records.
> The most often queries I need are like:
> 1) select * from MyTable where DateFIeld<=D1
> and id1=.. and id2=... (and so on for every integer
field)
> 2) select max(DateField) where DateField<=D1
> and id1=.. and id2=... (and so on for every integer
field)
> group by is1, id2, ...
> 3) select sum(ValueField) where DateField<=D1
> and id3=.. and id5=... (NOT for every integer field but
FOR
> SOME of them)
> group by is3, id5, ...
>
> Performance is very important!!!!!!!!! But I have problems with it!!!
> -------------------------------------------------
>
> QUESTIONS:
> 1) What are the prefered indexes?

Best to run an explain plan to see what the optimizer is doing. Are you using cost or rule? Are your tables/indexes analyzed? -> (cost only)

> What is better: to make many single indexes (one single index per each
> field)
> OR to make one compound index containing all fields
> OR both?

If you know that you will usually be selecting based on multiple fields, you are better off with a compound index containing those fields, otherwise the optimizer has to concatenate all the individual indexes that it uses together anyways.

> 2) How to deal with DateField in case of using compound index?
> (because I use "<=", not "=" comparing).
> Is it a good idea to put this field the first field in the index?

Put DateField first in the index if you often have select statements of this kind:
Select * from t1 where DateField <= whatever; In other words, it should go first in the compound index if you select datefield on its own in where clause, otherwise the index will not be used.

Sounds like you would benefit a lot by getting to know your sql trace and tkprof utilities. Guy Harrison's book "Oracle SQL..." is very helpful.
Good luck.

> Or to make compound index without DateField
> and to make another single index for DateField?

> 3) How should I use hints for these queries?
> Now I try to use /*+ INDEX (Mytable MyCompoundIndex) */
> Is it anything better?
>
> ORACLE 8.0.5.
>
> Thank you in advance.
>
> Oleg
> Moscow,
> Russia
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 09 1999 - 14:59:52 CDT