Home » RDBMS Server » Server Administration » Performance Tuning HUGE queries running WHERE UPPER fn
Performance Tuning HUGE queries running WHERE UPPER fn [message #369147] Wed, 29 March 2000 14:53 Go to next message
John Edwards
Messages: 1
Registered: March 2000
Junior Member
Appologies if this question is being asked in the wrong forum but ......

I have a challenging question....

I am attempting to tune a HUGE database (with limited experience) that is querying VIEWS on VIEWS on tables.

The SQL is performing;
SELECT * FROM VIEW_ANYVIEW WHERE UPPER(fieldname) like 'RUBBIS%'.

As far as I understand it, this is causing COUNTLESS tablescans.... this should allow case insensitive searching on VaRIablE CasE strings. (Volumes of data around 20Gb+).

Any suggestions on optimising these WHERE clauses with UPPER function?
Or perhaps any other suggestions on doing this case insensitive searching ?

Any comments (even sympathy will suffice!) are welcomed !

Cheers

John.
Re: Performance Tuning HUGE queries running WHERE UPPER fn [message #369148 is a reply to message #369147] Wed, 29 March 2000 15:02 Go to previous messageGo to next message
Chris Ruel
Messages: 6
Registered: March 2000
Junior Member
Simply put, that UPPER conversion is making it so Oracle does not use the index. In fact any functions in the WHERE clause will cause index suppression (lower, instr, substr etc.). I am not sure if there is a way around this. Is it not within your scope to convert to UPPER as the data is inserted into the DB? In Oracle 8i you can create Function based indexes to take care of this problem. Good luck...
Re: Performance Tuning HUGE queries running WHERE UPPER fn [message #369149 is a reply to message #369148] Wed, 29 March 2000 15:15 Go to previous messageGo to next message
Jack Stefanic
Messages: 1
Registered: March 2000
Junior Member
I've been told that you could add a column to the original table that contains the values in upper case, and then index off of that column.
Re: Performance Tuning HUGE queries running WHERE UPPER fn [message #369163 is a reply to message #369147] Fri, 31 March 2000 15:00 Go to previous message
NSA
Messages: 3
Registered: March 2000
Junior Member
Accept my sympathy...

Good Luck
Previous Topic: Re: Lpad
Next Topic: thanks a lot ,Jayshree !
Goto Forum:
  


Current Time: Thu Mar 28 18:47:49 CDT 2024