Home » RDBMS Server » Performance Tuning » Creation of index
Creation of index [message #256642] Mon, 06 August 2007 03:50 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

How to create a indexes.

I have a table's Tab1 and Tab2 with col1, col2, col3, col4, col5, col6, col11, col12, col13, col14, col15, col16.

Tab1 (col1, col2, col3, col4, col5, col6) pk(col1, col2)
Tab2 (col11, col12, col13, col14, col15, col16) pk(col11, col12)



Here below is my select queries. Which i need to create indexes on Tab1.

1.select * from tab1 a, tab2 b where a.col1=b.col11 and a.col2=b.col12 and a.col3='ABC' and a.col4='123';

For this above query i have to create indexes of which below combination

(col1, col2,col3, col4)
or
(col3,col4)


2.select * from tab1 a, tab2 b where a.col1=b.col11 and a.col2=b.col12 and a.col4='123';

(col1, col2, col4)
or
(col4)

3.select * from tab1 a, tab2 b where a.col1=b.col11 and a.col2=b.col12 and a.col5='ABC' and a.col6='123';

(col1, col2, col5, col6)
or
(col5, col6)


I dont understand i have to include primary key cols or not in indexing or else i have to create index on all the cols which i have used in where clause.

Like (col1, col2, col3,col4,col5, col6) or
( col3,col4,col5, col6)

Please let me know. which is correct possible above combination to create indexes on Tab1.

Any help really appreciated..

Thanks in advance.




Re: Creation of index [message #256860 is a reply to message #256642] Mon, 06 August 2007 23:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
An optimal indexing strategy will depend on the data in your tables.

A general indexing strategy that should produce good performance in most circumstances is to:

- Index join keys
- Index filter keys

This would produce the following indexes
1. taba(col1, col2)
2. tabb(col11, col12)
3. taba(col3, col4)
4. taba(col4)
5. taba(col5, col6)

From this, we see that index 4 is a subset of index 3. If we reverse the columns of index 3 (col4, col3), then index 4 becomes a LEADING subset, which means we can get rid of it.

There are circumstances where you might create indexes that combine join keys and filter keys together, but they are typically in response to specific data conditions or SQL statements.

Ross Leishman
Re: Creation of index [message #256893 is a reply to message #256642] Tue, 07 August 2007 00:48 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi Thanks a lot

I have one more question in my mind.For col3,col4, col5 and col6 there are repeating values like

col3 values are ('Y','N')
col4 values are ('I','o')
col5 values are ('A','B','C','D')
col6 values are ('X','Y','Z')

Is it better to create bitmap index or btree index.

If have to create a bitmap index for Tab1

I have to create 4 differnt bit map indexes like

create bitmap index index1 on tab1(col3)
create bitmap index index2 on tab1(col4)
create bitmap index index3 on tab1(col5)
create bitmap index index3on tab1(col6)

I have done so. But while executing query its taking only index2. while other not.

so i created bitmap index below
create bitmap index index on tab1(col3,col4,col5,col6)

While executing querry its picking the above bitmap index.

Is this a rgt way to create a index on table.

Thanks in advance


Re: Creation of index [message #257264 is a reply to message #256893] Tue, 07 August 2007 22:19 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bitmap indexes are useful if you have many different access paths - or unpredictable access paths. ie. The user may choose any combination of columns to query.

If the user does not have acces to query the table directly, then the access paths are restricted to those used by the system. For these, concatenated b-tree indexes will always out-perform bitmap indexes.

If you create the bitmap indexes anyway, make sure you gather statistics - otherwise Oracle will probably not use them.

If Oracle is choosing to use just one of the indexes, then it figures that the others will not help. eg. If Index2 filters the results down to 10 rows, what benefit could be gained from scanning another index? It's better to just get the 10 rows and filter the remaining non-matches from the table data.

Ross Leishman
Previous Topic: reverse key index
Next Topic: Error during Merge Statment
Goto Forum:
  


Current Time: Thu Dec 08 18:29:52 CST 2016

Total time taken to generate the page: 0.32046 seconds