473,395 Members | 1,532 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,395 software developers and data experts.

SELECT ... WHERE column LIKE parameter||'%'

I have a database containing alot of procedures with this kind of coding logic:
BigTable
column1 varchar2(40);
Procedure SomeProcedure (in_parameter varchar2)
SELECT column1
FROM BigTable
WHERE column1 LIKE in_parameter||'%';
END;
The procedure is called like this:

SomeProcedure('abc');
Although this coding logic selects the correct rows, there is a big problem - it does not use the index on BigTable.
But if you do perform at little test and change the procedure like this:
Procedure SomeProcedure (in_parameter varchar2)
SELECT column1
FROM BigTable
WHERE column1 LIKE 'abc'||'%';
END;

Then it uses the index. But ofcourse now there is not the desired flexibility of using a parameter.
QUESTIONS:
1. Why does Oracle not use the index when a parameter is used in the LIKE-statement?

Aug 11 '06 #1
0 10363

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: wabr7030 | last post by:
I have two xml file 1) xml source: <Indici> <F1>xxx</F1> <F2>yyy</F2> </Indici> 2) File colsDef.xml:
3
by: Terri | last post by:
Hi, I'm using the following code to build a concatenated string from a multi-select combo. For Each varItem In Forms!frmreports!Combo1.ItemsSelected strSQL = strSQL &...
0
by: Jean | last post by:
Hi all, This problem has me dumbstruck at the moment and I was wondering if someone could offer some advice while I have a go at it myself. Background: I have a query qryAll that takes data...
0
by: Stephan Bour | last post by:
I need to implement a simple search function to lookup order information in a SQLserver table. The code below works fine for a search to the ³OrderID² column. However, I¹d like to offer the user...
8
by: felecha | last post by:
Is there a control that I can put on a Form that would let a user select just the month value? I tried to find a way to make the DateTimePicker or the Month Calendar do what I want but I can't. ...
5
by: Han Lim | last post by:
Dear All, I have an application written by VB.Net with connect to a Microsoft Access database. One of the forms is to select data using oleAdapter and fill it into a dataset. In the oleAdapter, i...
5
by: John | last post by:
I just cannot manage to perform a SELECT query with NULL parameter... My CATEGORY table does have one row where TCATEGORYPARENTID is null (real DB null value). TCATEGORYID and TCATEGORYPARENTID...
2
by: JP2R | last post by:
The following is my code - I'm new to VB - not to Excel - just VB and need some support/guidance/direction (please) I have a spreadsheet and in column "X" titled COS I need to put an integer that...
1
by: Kat | last post by:
This is my grid and my datasource. I cannot seem to find where the Select button is coming from, I even tried in the databind event to remove it, but column 0 apparently is the edit/delete field (...
17
by: trose178 | last post by:
Good day all, I am working on a multi-select list box for a standard question checklist database and I am running into a syntax error in the code that I cannot seem to correct. I will also note...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
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
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
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
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.