Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Performance problem with substring in where clause

Posted on 2004-10-26
Medium Priority
Last Modified: 2010-08-05

i have a problem with the following SQL that results in a extremly slow view.

The DB is a Oracle 8.17. Thanks for any help!


select inpfha.objektid, inpfha.strassenid, inpfha.hausnr, inpfha.zusatz, jvafha.LOCATIONS_KEY, jvafha.ENE_HAUSUNTERNR from MB_INP_ANSCHLUSS_FHA inpfha, IDP_EXTERN_STRASSENTEIL strasse, MB_JVA_ANSCHLUSS_FHA jvafha
where inpfha.strassenid = strasse.strnr and jvafha.ENE_STRASSEID = strasse.KEY_STRASSE and inpfha.hausnr = jvafha.ENE_hausnr and
((inpfha.zusatz is null and substr(jvafha.ENE_HAUSUNTERNR,0,1) = ' ') or inpfha.zusatz = substr(jvafha.ENE_HAUSUNTERNR,0,length(inpfha.zusatz)))
Question by:Mobilant
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

morphman earned 2000 total points
ID: 12410206
2 functional indexes may help here.

1. on substr(jvafha.ENE_HAUSUNTERNR,0,1)
2. on substr(jvafha.ENE_HAUSUNTERNR,0,length(inpfha.zusatz))

otherwise you will be seeing a full table-scan for this query.

LVL 48

Expert Comment

ID: 12410910
Yes, thats right! You have to see if the substring you are searching for is permanently placed in the string itself. If the substring is not parametrized you can easily create function based index (es) on the tables.
Of course everything is balanced. If there are many indexes, the insert, update, delete DML statements will run slowly, but selects will run faster and vice versa.

Viel Glueck und Erfolg!

Author Comment

ID: 12411953

unluckily it is a Oracle 8.05 on the test server and a 8.17 on the production server. The functional index works on the 8.17 but on the old version. Any suggestions?

morphman you´ll get points!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 12412066
Yes, upgrade the test server. tell your bosses that 8.0.5 is not a supported database anymore, and that you cant do things in test that will speed things up in live!!

in 8.0.5 you cannot speed up your query. All I would suggest is performing a USE_HASH optimiser hint on both tables if there are many rows in each table.

Expert Comment

ID: 12412810
There's something wrong about this query even if you ignore the string comparison stuff.  For one thing, there is no literals or parameters involved in the WHERE clause, so it is bound to be a table scan on at least one table

Could you provide the following:
-number of rows for each table
-available indexes for each table

Expert Comment

ID: 12419027
One potential performance improvement you can get is to change the line:
and  ((inpfha.zusatz is null and substr(jvafha.ENE_HAUSUNTERNR,0,1) = ' ') or

To instead be
and  ((inpfha.zusatz is null and jvafha.ENE_HAUSUNTERNR LIKE ' %') or

This will also avoid the need for the function based index on this column (you could create a standard index if appropriate).

Author Comment

ID: 12435786
By the way the admin sets up a new Oracle 9i  ;-)
Then i will use the functional indices.
Damn, that i can´t split the points. I learned a lot of you guys!


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question