473,387 Members | 2,436 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Index on Numeric column not being used when using Like operator

Hi,

The application I am currently working on is developed in Oracle 8i.

I have a table which has a nullable numeric column say CustNo. This column has an index defined on it and oracle uses the index when I am using this column with a numeric operator ( eg < , = etc).

However if I try to use the LIKE operator on this clause Oracle refuses to use the index. I have tried giving the index hint also but that also does not work.

I have a suspicion that the reason for this might be that Oracle implicitly tries to convert the numeric datatype to Varchar2 datatype and hence does not use index.

Would creating a functional index on to_char(CustNo) help in tuning the query in this case?

Thanks,
Deepa
Aug 30 '07 #1
2 3984
amitpatel66
2,367 Expert 2GB
CBO will bypass indexes even when u use <>, !=, any functions in the indexed column of the WHERE clause. So probably creating FUNCTION index on a column with TO_CHAR might Help!!.

Pls do post the outcome after trying function index.

Thanks
Amit
Aug 31 '07 #2
gintsp
36
Hmmm does Oracle 8i even Function Based indexes? Somehow I doubt it...

Gints Plivna
Sep 3 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: Andrew Dalke | last post by:
Is there an author index for the new version of the Python cookbook? As a contributor I got my comp version delivered today and my ego wanted some gratification. I couldn't find my entries. ...
1
by: Amir | last post by:
Hi all, I have a table called PTRANS with few columns (see create script below). I have created a view on top that this table VwTransaction (See below) I can now run this query without a...
1
by: ziga.seilnacht | last post by:
""" I am trying to write some classes representing the quaternion number. I wrote a base class, which implements only the numerical interface, and a few subclasses, which provide methods for their...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
3
by: Marcio Caetano | last post by:
Hello ! Consider the examples bellow: 1- I create a table named TBA with columns ( A int , B char(10) ) 2- I create an B-Tree index named IDXA using the column A 3- I create an GIST (or...
1
by: Waldo Nell | last post by:
Hi, I have a *huge* problem. I have a table with indexes on but the moment I have an OR in my SELECT query it seems to not use the appropriate index. oasis=> \d purchases Table...
8
by: ALiX | last post by:
Hi all, In my code I use different vectors of the same size to hold some data. At some point I need to iterate through all vectors at the same time. Now, the question is what type should the...
122
by: C.L. | last post by:
I was looking for a function or method that would return the index to the first matching element in a list. Coming from a C++ STL background, I thought it might be called "find". My first stop was...
6
by: Henry J. | last post by:
I have a composite index on two columns in a table. However, the index is not used in a query that restricts the 2nd column to a constant. If both columns are linked with columns in other join...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.