Re: Performance question

In article <6df289$lms$1@news.xmission.com>, Leffler, Jonathan <jleffler@visa.com> writes > >There are two factors which affect whether the OR operation works with >indexes or not, and they're the usual culprits - the version of the engine >and the SQL statement. > >Some versions of the engine didn't handle OR conditions very well; I don't >have the details available. You can find out whether your version does by >looking at the SET EXPLAIN output. > I thought I saw something in the 7.1 Performance Tuning Guide about replacing OR's with UNIONS. >Some OR conditions are not readily handlable with indexes and others are. >Paul's example is one that can be dealt with because both clauses in the OR >are on the same column. The condition could also be written as: > > ext_id IN (3840000,383000) > >Other OR conditions cannot easily be handled with indexes: for example, a >OR condition referring to two different columns on either side is not so easily >handled: > > (ext_id = 3840000) OR (other_column BETWEEN 37 and 92) > >Even if there is an index on ext_id and a separate index on other_column, >it isn't clear how the optimizer will, or should, handle the query. If there >isn't >much overlap between the sets of rows identified by the two conditions, then >it may be quicker to do a UNION because there won't be much to do in the >duplicate elimination phase of UNION. On the other hand, if there is a lot of >overlap (so many rows satisfy both conditions), then the single scan may be >quicker than the UNION with two separate indexed SELECT operations. It >also depends on the size of the table, of course. > True, but can't the duplicate elimination be done in parallel with the two scans? Also duplicate elimination should be CPU intensive and hence faster than the extra disk I/O required by a table scan.. Surely then it really comes down to index vs sequential scan? >Isn't life fun! The answer is 'it varies, and it depends'... > >Yours, >Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h> > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care