Re: to_char( ) column indexable

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 09 Feb 1999 00:50:12 GMT
Message-ID: <36c285fb.3939855@192.86.155.100>

A copy of this was sent to "Steve" <schen_at_prodigy.net> (if that email address didn't require changing) On 9 Feb 1999 00:40:26 GMT, you wrote:

>Hi,
>
>In the query
>"select a, b from XYZ where to_char(cid) = :d1",
>the cid index is not used due to to_char function. Has anyone indexed
>on to_char(column) to do the query, or any other way around?
>
>Thanks.
>
>Steven

In 8i you can index a function (so you could create index my_index on xyz(to_char(cid)) and it'll get used)... however, in the above why not code:

select a, b from XYZ where cid = to_number(:d1)       if cid is a number or 
select a, b from XYZ where cid = to_date(:d1)         if cid is a date

then it'll use an index on cid...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Feb 08 1999 - 18:50:12 CST