About Index

[Former Member]

Hi all,

i have doubt in using index. If i am having 2 index for a table T.

index 1 having field F1 and Index 2 having Field F2.

My doubt is

1. if i use field F1 and some other fields in the single query,will index work. or i have to use index field only in the query in order to index to work.

suppose query is like

select * from T

into table itab

where F1 = '0202' and

F3 = '122' .

.

2. if i used more than one index in the same query , will my index works.

suppose query is like

select * from T

into table itab

where F1 = '0202' and

F2 = '122'.

Thanks in Advance

[Former Member]

Hi joe,

1. If any field has an index, and we use this field

in the where condition,

then the database automatically uses the respective index.

2. So to your question

1. Yes, the index will work

2. Yes, both indexes will work.

regards,

amit m.

[Former Member]

Hi joe,

1. If any field has an index, and we use this field

in the where condition,

then the database automatically uses the respective index.

2. So to your question

1. Yes, the index will work

2. Yes, both indexes will work.

regards,

amit m.

[Former Member]

Hi Amit,

Thank you for answer.My another doubt is how many index is recommended to use for an table.

suppose my table has 12 index and if i use the 12th index,will it works.

[Former Member]

Hi again,

1. If a table has 12 fields, then we can

create as many index as we like (depending max

as per the database)

2. The index can be based on one field only,

or a combination of fields.

3. The fields (apart from the primary key(s))

which are more used in select queries

are the target for making indexes on.

regards,

amit m.

[ThomasZloch]

Depends on your database and its so called optimizer.

I can speak about Oracle 10, the cost based optimizer is very advanced and will pick the most effective access path to the data (with the least "cost", whatever this exactly means) for that very simple case you are describing.

However Oracle has bugs too, and in very complex cases (e.g. table with seven indexes, query using some fields from index1, some from index2, some non-index fields) I've seen it not using the best index.

Cheers

Thomas