|
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|