Home » RDBMS Server » Performance Tuning » Index
Index [message #65390] Mon, 06 September 2004 21:48 Go to next message
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 Go to previous message
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
Previous Topic: Table locking problem
Next Topic: Oracle 10G OEM tuning & diagnostics pack missing
Goto Forum:
  


Current Time: Fri Mar 29 02:46:50 CDT 2024