Re: SELECT subquery much slower than IN ( list...)???
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?
--
Matt Reprogle
IS Engineer, Delphi Delco Electronics Systems
phone:(765)451-9651 FAX: (765)451-8230
mcreprog@ictest.delcoelect.com