topic Re: About Index in Application Development and Automation Discussions

About Index

Former Member — Tue, 15 Apr 2008 11:54:12 GMT

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

Re: About Index

Former Member — Tue, 15 Apr 2008 11:56:11 GMT

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.

Re: About Index

ThomasZloch — Tue, 15 Apr 2008 12:03:24 GMT

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

Re: About Index

Former Member — Tue, 15 Apr 2008 12:19:02 GMT

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.

Re: About Index

Former Member — Wed, 16 Apr 2008 08:11:54 GMT

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.