Help! Query Performance

I am trying to find a way to improve performance on the following query. From the optimizer output (shown below), it appears that the MATCH condition is causing the most drag on performance. I'd like to get the optimizer to filter on "alert_partnum.family" (2.1 below) BEFORE the MATCHES filter. Is this possible? I've already tried updating the statistics and setting the distribution levels. I've also tried restructuring the query. And, $OPTCOMPIND is set to 2. Perhaps there is something unique about the MATCHES operation? Any help would be much appreciated. Thanks... QUERY: ------ select alert_partnum.norm_pn, company_avl.supplier_name, company_avl.supplier_partnum,company_avl.owner_partnum from alert_partnum, company_avl where alert_partnum.family = 'obs590' and company_avl.owner_id = 117 and (company_avl.supplier_id = 18 or company_avl.supplier_id = 2) -- 'Unknown' company and company_avl.supplier_norm_pn matches alert_partnum.norm_pn Estimated Cost: 23426 Estimated # of Rows Returned: 23813 1) company_avl: INDEX PATH Filters: company_avl.owner_id = 117 (1) Index Keys: supplier_id Lower Index Filter: company_avl.supplier_id = 18 (2) Index Keys: supplier_id Lower Index Filter: company_avl.supplier_id = 2 2) alert_partnum: INDEX PATH Filters: company_avl.supplier_norm_pn MATCHES alert_partnum.norm_pn (1) Index Keys: family Lower Index Filter: alert_partnum.family = 'obs590'

What is the join column between the two tables? If the join columns are norm_pn and supplier_norm_pn, then why not use: company_avl.supplier_norm_pn = alert_partnum.norm_pn If an index exists that would support this join, then your query should fly. John Carlson Informix DBA WHSmith USA Tim Veazey wrote: > > I am trying to find a way to improve performance on the following query. > From the optimizer output (shown below), it appears that the MATCH condition > is causing the most drag on performance. I'd like to get the optimizer to > filter on "alert_partnum.family" (2.1 below) BEFORE the MATCHES filter. Is > this possible? I've already tried updating the statistics and setting the > distribution levels. I've also tried restructuring the query. And, > $OPTCOMPIND is set to 2. Perhaps there is something unique about the > MATCHES operation? > > Any help would be much appreciated. Thanks... > > QUERY: > ------ > select alert_partnum.norm_pn, > company_avl.supplier_name, company_avl.supplier_partnum,> company_avl.owner_partnum > from alert_partnum, company_avl > where alert_partnum.family = 'obs590' > and company_avl.owner_id = 117 > and (company_avl.supplier_id = 18 or > company_avl.supplier_id = 2) -- 'Unknown' company > and company_avl.supplier_norm_pn matches > alert_partnum.norm_pn > > Estimated Cost: 23426 > Estimated # of Rows Returned: 23813 > > 1) company_avl: INDEX PATH > > Filters: company_avl.owner_id = 117 > > (1) Index Keys: supplier_id > Lower Index Filter: company_avl.supplier_id = 18 > > (2) Index Keys: supplier_id > Lower Index Filter: company_avl.supplier_id = 2 > > 2) alert_partnum: INDEX PATH > > Filters: company_avl.supplier_norm_pn MATCHES alert_partnum.norm_pn > > (1) Index Keys: family > Lower Index Filter: alert_partnum.family = 'obs590'

I need to keep the "matches" operator. There are wildcard characters in the norm_pn column which are matched against supplier_norm_pn. I just want to make sure that the match is the last action performed by the query. As it stands now, I don't think that is the case. Carlson@WHSmith <carlson1@bellsouth.net> wrote in message news:379F6117.66B67ECF@bellsouth.net... > What is the join column between the two tables? If the join columns are > norm_pn and supplier_norm_pn, then why not use: > company_avl.supplier_norm_pn = alert_partnum.norm_pn > > If an index exists that would support this join, then your query should > fly. > > > John Carlson > Informix DBA > WHSmith USA > > >

> then why not use: > company_avl.supplier_norm_pn = alert_partnum.norm_pn I need to use the 'matches' operator because there are wild-card characters in the second column. Basically, I just want to make sure that the match is the absolute LAST thing done by the query. Right now, I don't think that is the case.

Tim, Actually, I believe the filter IS being applied last. The index information is used to determine what data rows to retrieve and then the filter is applied. Perhaps adding the part number to the index would improve things, because then the optimizer can select based on the index without having to read the data page.. Doug Tim Veazey wrote in message <7nns9f$7eh$1@paxfeed.eni.net>... >> then why not use: >> company_avl.supplier_norm_pn = alert_partnum.norm_pn > >I need to use the 'matches' operator because there are wild-card characters >in the second column. > >Basically, I just want to make sure that the match is the absolute LAST >thing done by the query. Right now, I don't think that is the case. > >