Home » RDBMS Server » Performance Tuning » Creation of index
| Creation of index [message #256642] |
Mon, 06 August 2007 03:50  |
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  |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
Goto Forum:
Current Time: Mon Nov 04 21:20:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
