Home » RDBMS Server » Performance Tuning » indexes difference as table columns
indexes difference as table columns [message #236527] Thu, 10 May 2007 03:02 Go to next message
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 #236530 is a reply to message #236527] Thu, 10 May 2007 03:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you index (x,y), you can drop the index on (x), but the index on (y) in place.

Ross Leishman
Re: indexes difference as table columns [message #236535 is a reply to message #236530] Thu, 10 May 2007 03:24 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Optimal size for my memory components ( development db )
Next Topic: Tuning update statement
Goto Forum:
  


Current Time: Fri Dec 02 20:21:22 CST 2016

Total time taken to generate the page: 0.06897 seconds