OraFAQ Forum: Performance Tuning » help for not equal and index usage

Home » RDBMS Server » Performance Tuning » help for not equal and index usage

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help for not equal and index usage [message #161708]

Mon, 06 March 2006 18:04

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

I have a query bellow. Because of the not equal sign the index on Status is not in use. I receive FTS on Search_result or Search (if I use exists subselect on search_result).
The tables ae growing in the last days and that startedto cause problems.
Please, help me to tune up the query.
Thanks a lot,
mj
SELECT codes.code_desc, search_result.status
AS searchResultStatus, COUNT(*) AS cnt
FROM search_result, search, codes
WHERE search_result.search_id = search.search_id
AND search.reason_code = codes.code
AND search.search_group ='DEFAULT'
AND search.sys_delete_dt IS NULL
AND (search_result.status IS NULL
OR (search_result.status <> 'ASSIGNED'
AND search_result.status <> 'CLOSED'))
AND SEARCH.SYS_EXPIRE_DT >= search.sys_create_dt
GROUP BY codes.code_desc, search_result.status

Report message to a moderator

Re: help for not equal and index usage [message #161717 is a reply to message #161708]

Mon, 06 March 2006 19:15

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

So, you want the following clause to use an index:

AND (search_result.status IS NULL 
OR (search_result.status <> 'ASSIGNED' 
AND search_result.status <> 'CLOSED'))

Right?

I assume that only a small percentage of the table has values other than ASSIGNED and CLOSED. If not, then indexing this clause will not help. When you select a significant proportion (say, >10%) of a table via an index, performance goes DOWN.

You can do it with a function based index, but you will have to change your SQL.

CREATE INDEX index_name ON search_result (
    decode(status, 'ASSIGNED', NULL, 'CLOSED', NULL, 'Y')
);

And in your SQL:

AND decode(search_result.status, 'ASSIGNED', NULL, 'CLOSED', NULL, 'Y') = 'Y'

_____________
Ross Leishman

Report message to a moderator

Re: help for not equal and index usage [message #161867 is a reply to message #161717]

Tue, 07 March 2006 10:08

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

Thank you very much. It's a great idea.
The only problem is if I'll be allowed to use it.
This query runs on Oracle, DB2, and SQL server (from different app versions). Usually I'm not allow to change Oracle if the other 2 cannot be changed similar way... have to check with the other system people.
If I cannot use decode for creating the index, what else I could look to improve the performance?

Thanks a lot again, mj

Report message to a moderator

Re: help for not equal and index usage [message #161908 is a reply to message #161867]

Tue, 07 March 2006 20:02

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

If you cannot use the Function-Based index, then you're in big trouble. I can't think of a solution that is not horrible.

One such horrible solution that I personally would never implement for fear of ridicule (yet I have no problem telling you about it Smile

) is to create a denormalized column identifying "search_result rows of interest".

ALTER TABLE search_result ADD (search_of_interest VARCHAR2(1));
CREATE INDEX ix_search_of_interest ON seach_result(search_of_interest);

Then change your application so that whenever a row is inserted or the status is updated, the column value is updated accordingly. This could be done in a trigger, but the code would be RDBMS specific.

It would be better if you could change the values of the STATUS column so that:
1. It is never NULL
2. Values of interest are in a single discrete range: eg.

NULL     => 00_NULL     
ASSIGNED => 01_ASSIGNED
CLOSED   => 02_CLOSED
NEW      => 03_NEW
REOPENED => 04_REOPENED

Then you could use the predicate

AND status >= '03'

This would be able to range scan an index on the status column.

Another idea. If you could get rid of the NULLs, you could just change your predicate to

IN (val1, val2, val3)

listing all of the valid values other than ASSIGNED and CLOSED.

_____________
Ross Leishman

Report message to a moderator

Re: help for not equal and index usage [message #162104 is a reply to message #161908]

Wed, 08 March 2006 15:10

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

Well, It looks I'm even in a bigger trouble...
I cannot change the table structure as this is a third party product and I do not have right to touch any of their tables.
I cannot use IN() clause as this statuses are user defined and can change at any time through the GUI except the 2 bellow.

I guess, I got to accept that there's no better solution then the regular indexes I have now...
Thanks a lot Ross, it was a great help.
mj

Report message to a moderator

Previous Topic:	Sort and hash area size problem
Next Topic:	best way to create an index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 23:11:09 CDT 2024