473,397 Members | 1,950 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,397 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 10364

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: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
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
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
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
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...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

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.