Re: SELECT subquery much slower than IN ( list...)???

In article <35005D0E.4E76@ictest.delcoelect.com>, Matt Reprogle <mcreprog@ictest.delcoelect.com> writes >Douglas Wilson wrote: >> have you done a 'set explain on'? >> I had a similar situation once, and I didn't realize >> (until the 'explain') that the >> table in the main query was really an alias (synonym, whatever) for >> a table in another database on another machine. The optimizer >> therefore could not use the index on the main table. >> Also could be an 'update statistics' thing. >> >> Cheers, >> Douglas Wilson >First, some additional information: >1) the main table I am querying is about 3,000,000 rows. >2) I have a unique index for table h_tab on columns (l_key, h_seq) > >Here is the sqexplain.out for each query mode: > >EXPLICIT LIST (runs in about 3 seconds) >QUERY: >------ >select l_key,max(h_seq) last_h_seq >from h_tab >where l_key in ( >'80914', >'80D74', >'80C30', >'80C28', >'80F98', >'80915', >'80A26', >'80917', >'80F92', >'80A25', >'80A24', >'80A23', >'80811') >group by l_key >into temp last_temp >with no log> >Estimated Cost: 362 >Estimated # of Rows Returned: 2 >Temporary Files Required For: Group By > >1) h_tab: INDEX PATH > > (1) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80914' > > (2) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80D74' > > (3) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80C30' > > (4) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80C28' > > (5) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80F98' > > (6) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80915' > > (7) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80A26' > > (8) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80917' > > (9) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80F92' > > (10) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80A25' > > (11) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80A24' > > (12) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80A23' > > (13) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > Lower Index Filter: h_tab.l_key = '80811' > > >SUBQUERY: runs in about 7 minutes >QUERY: >------ >select l_key,max(h_seq) last_h_seq >from h_tab >where l_key in (select temp_l from l_temp_tab) >group by l_key >into temp last_temp >with no log> >Estimated Cost: 88140 >Estimated # of Rows Returned: 9142 > >1) h_tab: INDEX PATH > > Filters: h_tab.l_key = ANY <subquery> > > (1) Index Keys: l_key h_seq (Key-Only) (Serial, fragments: ALL) > > Subquery: > --------- > Estimated Cost: 2 > Estimated # of Rows Returned: 10 > > 1) mcreprog.l_temp_tab: SEQUENTIAL SCAN (Serial, fragments: ALL) > >This tells me that it is doing a key-only query on the big table, and a >sequential scan on the temp table. Isn't that what you would expect? > What happens if you use a join instead of a subquery? -- 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