query optimizer (set explain) question

i've run set explain on three different systems (all hpux 10.20 with informix ds 7.3 uc2) the databases in question are the same schema (tables and indexes, same onconfigs, etc.) on each machine (however number of rows in each table varies. I ran set explain on each machine with the same query (the exception being the id of a data point) (shown as 'lid' in queries below). the results are below. my question (and the other dba's at the other two systems) is why did system # 2 use a different optimization (or do i not understand the set explain results, which is a possibility given my experience level). the number of rows in the 'height' column at each site is: #1 530,000 #2 150,000 #3 150,000 at first i thought it had to do with number of rows until i checked #3. any answers/thoughts are greatly appreciated! Server #1: QUERY: ------ select lid, pe, dur, ts, extremum, obstime, value from height where lid = 'TCMO2' and pe = 'HG' and ts != 'RX' order by obstime Estimated Cost: 189 Estimated # of Rows Returned: 361 Temporary Files Required For: Order By 1) oper.height: INDEX PATH (1) Index Keys: lid pe dur ts extremum obstime (Key-First) Lower Index Filter: (oper.height.lid = 'TCMO2' AND oper.height.pe = 'HG' ) Key-First Filters: (oper.height.ts != 'RX' ) Server #2: QUERY: ------ select lid, pe, dur, ts, extremum, obstime, value from height where lid = 'OTTM7' and pe = 'HG' and ts != 'RX' order by obstime Estimated Cost: 175 Estimated # of Rows Returned: 295 Temporary Files Required For: Order By 1) oper.height: INDEX PATH Filters: (oper.height.pe = 'HG' AND oper.height.ts != 'RX' ) (1) Index Keys: lid Lower Index Filter: oper.height.lid = 'OTTM7' Server #3: QUERY: ------ select lid, pe, dur, ts, extremum, obstime, value from height where lid = 'CHIN7' and pe = 'HG' and ts != 'RX' order by obstime Estimated Cost: 796 Estimated # of Rows Returned: 1181 Temporary Files Required For: Order By 1) oper.height: INDEX PATH (1) Index Keys: lid pe dur ts extremum obstime (Key-First) Lower Index Filter: (oper.height.lid = 'CHIN7' AND oper.height.pe = 'HG' ) Key-First Filters: (oper.height.ts != 'RX' ) thanks james paul jhp@awips1.abrfc.noaa.gov Sent via Deja.com http://www.deja.com/ Before you buy.

In article <7usgdo$n8u$1@nnrp1.deja.com>, jhp@awips1.abrfc.noaa.gov writes >i've run set explain on three different systems >(all hpux 10.20 with informix ds 7.3 uc2) >the databases in question are the same schema >(tables and indexes, same onconfigs, etc.) >on each machine (however number of rows in each >table varies. > >I ran set explain on each machine with the same >query (the exception being the id of a data point) >(shown as 'lid' in queries below). the results are >below. my question (and the other dba's at the other two >systems) is why did system # 2 use a different optimization >(or do i not understand the set explain results, which >is a possibility given my experience level). > > Update stats not done on server #2 ??? >the number of rows in the 'height' column at each site >is: #1 530,000 > #2 150,000 > #3 150,000 >at first i thought it had to do with number of rows until >i checked #3. > >any answers/thoughts are greatly appreciated! > >Server #1: > QUERY: > ------ > select lid, pe, dur, ts, extremum, obstime, value from height > where lid = 'TCMO2' and pe = 'HG' and ts != 'RX' order by obstime> > Estimated Cost: 189 > Estimated # of Rows Returned: 361 > Temporary Files Required For: Order By > > 1) oper.height: INDEX PATH > > (1) Index Keys: lid pe dur ts extremum obstime (Key-First) > Lower Index Filter: (oper.height.lid = 'TCMO2' AND >oper.height.pe = 'HG' ) > Key-First Filters: (oper.height.ts != 'RX' ) > > > >Server #2: > QUERY: > ------ > select lid, pe, dur, ts, extremum, obstime, value from height > where lid = 'OTTM7' and pe = 'HG' and ts != 'RX' order by obstime> > Estimated Cost: 175 > Estimated # of Rows Returned: 295 > Temporary Files Required For: Order By > > 1) oper.height: INDEX PATH > > Filters: (oper.height.pe = 'HG' AND oper.height.ts != 'RX' ) > > (1) Index Keys: lid > Lower Index Filter: oper.height.lid = 'OTTM7' > > > >Server #3: > QUERY: > ------ > select lid, pe, dur, ts, extremum, obstime, value from height > where lid = 'CHIN7' and pe = 'HG' and ts != 'RX' order by obstime> > Estimated Cost: 796 > Estimated # of Rows Returned: 1181 > Temporary Files Required For: Order By > > 1) oper.height: INDEX PATH > > (1) Index Keys: lid pe dur ts extremum obstime (Key-First) > Lower Index Filter: (oper.height.lid = 'CHIN7' AND >oper.height.pe = 'HG' ) > Key-First Filters: (oper.height.ts != 'RX' ) > > > >thanks > >james paul >jhp@awips1.abrfc.noaa.gov > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams