-
One of our customer wrote when I asked him why he had created a combination index on the columns that already had individual indexes and he wrote...
index 1 - res_entity_type
index 2 - res_level
index 3 - res_entity_type, res_level
index4 - res_status, res_int_ext8, res_entity_type
What I am concerned about is index 3 !
Part1:
------
In our mwebres table, the res_entity_type column values are very skewed. We have 72 rows where
res_entity_type = 1 and 32,456 rows where res_entity_type = 3. I added a histogram on res_entity_type so that when the query asks for rows where res_entity_type = 1, at least the optimizer will use the index for that query.
If the query asks for rows where res_entity_type = 3, it should never use an index if the query is just based on that column. It would be more efficient to do the full table scan.
--I know that the 1st part is correct.
Part2:
------
That is why I added indexes that include that column
along with others. For example, for queries that request rows where res_entity_type = 3 and where res_level = 5 (which has 18 rows in our database), the optimizer will go ahead and use an index that contains those two columns.
What I am confused is the 2nd Part. Is what he saying true/correct ?
thanks
Sonali
-
Sonali,
I believe that you can drop index #1. When queries are issued that need to use the indexed column RES_ENTITY_TYPE, they can just as easily use index#3 as they can use index#1.
-John
-
It is true but it depends on the selectivity of res_level columns , does combined index increase selectivity, clustering factor.
If cardinality is low, think bitmap , u can have multiple indexes with not much space degradation and enough performace improvement
-
Sonali,
Actually a better idea might be to rebuild your indexes like this :
index 1 - res_entity_type
index 2 - res_level
index4 - res_status, res_int_ext8
I did not look closesly enough the first time.
Oracle can use 2 indexes at once, and since you have index 3 being essentially index1 + index2, yes I agree that you don't need it. Also, I think you can drop the last column off index 4 since it is already indexed in index1.
I am assuming that your tables are analyzed and that you are using the cost based optimizer.
-John
-
be careful with bitmap indexes - you don't want to use them if the underlying table is changing very much. bitmap indexes are best in static tables (and yes with low cardinality)
-
Thanks, but I still don't understand that what he wrote in Part2 is correct or not...
Any one please help..
I forgot to mention the index positions in my 1st post..
index 1 - res_entity_type
index 2 - res_level
index 3 - res_level(1), res_entity_type(2)
index4 - res_int_ext8(1), res_entity_type(2) , res_status(3)
Thanks a lot
Sonali
-
As I see it, your developer wants to be sure he gets a full table scan when he selects a value that returns many rows.
I did a little test. I hope the results can help you.
btw, i'm on 7.3.4
Create table gdn ( nummer number(4), nummer number(4),tekst varchar2(20));
create index gdn_i2 on gdn ( nummer,nummer2)
I inserted records in the table
nummer = 1 , nummer2 = 1, tekst = 'ONE'
about 16000 records
nummer = 1 , nummer2 = 2, tekst = '12'
about 7 records
nummer = 2 , nummer2 = 2, tekst = '2'
about 4 records
analyze table gdn compute statistics;
analyze index gdn_i2 compute statistics;
analyze table gdn compute statistics for columns nummer,nummer2;
select count(1) from gdn where nummer=1;
-- results in full table scan
select count(1) from gdn where nummer=1 and nummer2=1;
-- results in full table scan
select count(1) from gdn where nummer=2;
-- results in index scan using GDN_I2
select count(1) from gdn where nummer=2 and nummer2=2
-- results in index scan using GDN_I2
If this is the result you developer wants to achieve,
the index 2 is not necessary.
Hope this helps
Gert