| indexes difference as table columns [message #236527] |
Thu, 10 May 2007 03:02  |
fozerol
Messages: 17
Registered: March 2007
|
Junior Member |
|
|
is there any difference between separated columns indexes
sample
A index (x,y) column table B
between
C index (x) column table B
D index (y) column table B
(all of them b-tree)
why
oracle em console sql access adviser advises to create index A, while C and D indexes exist
should i drop C and D indexes and create A index ?
thanks for replies
|
|
|
|
|
|
| Re: indexes difference as table columns [message #236535 is a reply to message #236530] |
Thu, 10 May 2007 03:24  |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Does the columns on these tables have null values ?
Probably worth examining
if all of the columns in an index entry is NULL then the index entry will not be made. That is if an index is built on a column with all
NULLS in it then index will have zero rows in it.
If you have Index A (x,y ) and have values (1,1) , (1,null),(null,1), (null,null)
Then the index will have 3 entries
But if you have index on B(x) or C(y) then the index will have 2 entries.
Thanks
|
|
|
|
| Re: indexes difference as table columns [message #236539 is a reply to message #236535] |
Thu, 10 May 2007 03:42 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Forgot to mention
Also if you have queries with predicates as listed below , the its worth considering concatenated index as oracle has something called as index skip scan which can optimize or allow the index on (x,y) to be used for such scenarios.
where x = 100
where y = 101
where x = 100 and y = 101
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
