Performance Tuning HUGE queries running WHERE UPPER fn [message #369147] |
Wed, 29 March 2000 14:53 |
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 |
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...
|
|
|
|
|