| Index [message #65390] |
Mon, 06 September 2004 21:48  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
I created a Index like below
Table TEST
ORIG_NPA number
ORIG_NXX number
...
Both ORIG_NPA and ORIG_NXX are Number columns.
CREATE INDEX Test_Ix (ORIG_NPA||ORIG_NXX);
Index is created.When I view the Index structure i noticed that TO_CHAR Function applied.
When I use select statement like following
SELECT * FROM Test when ORIG_NPA||ORIG_NXX =123456
It takes full table scan.
When I pass the value as charcter sting like below the performance was very fast.
SELECT * FROM Test when ORIG_NPA||ORIG_NXX ='123456'
Selected the records quickly with Index.
Can you explain me any one about this ? And do I have to pass the String values ?
Thanks
Suresh
|
|
|
|
| Re: Index [message #65401 is a reply to message #65390] |
Thu, 09 September 2004 06:41  |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
You cannot concatenate two numbers ( you can only add, subtract, divide or multiply two numbers ). When you use ||, this is in fact the same as a call to the CONCAT function, which is a string function.
When you create your index on table ( n1 || n2 ), the presence of the concatenation tells Oracle that in order to make this work, the numbers must be turned into strings. So it does it for you - known as an implicit conversion and generally known as "a bad thing" because things are happening outside of your control. So your index is now created on a set of string values.
So when you try to use this index, Oracle expects a string, else it cannot use it. When you try to use the index using n1 || n2, Oracle must again apply the TO_CHAR implicit conversion to make your request usable in a concatenation. If Oracle converts, you lose the index lookup ( unless you have created a function-basde index ).
If you need to concatenate numbers, then you should store them as strings, or create a function based index on table( to_char(n1) || to_char(n2) ). Read up on function-based indexes.
Regards
Adrian
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
