Issue with query path chosen by Informix 7.20 UC2 optimizer ***Help*** :*(

We're running Informix OnLine 7.20 UC2 in an HP-UX 10.10 environment and one of our more simplistic two table join queries seems to be being mis-optimized. The query is listed below: SELECT * FROM product_vendor, product WHERE prod_upc_num = pdvn_upc_num AND prod_pc_code_num = pdvn_pc_code_num ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num Note: Both the product and product_vendor tables contain 80K rows or approximately the same width. Product table Product_Vendor Table ------------------- ---------------------------------- prod_upc_num decimal(14,0) pdvn_upc_num decimal(14,0) prod_pc_code_num decimal(10,0) pdvn_pc_code_num decimal (10,0) prod_sales_dept smallint pdvn_vendor_num decimal (10,0) .... .... Product Table Index -------------------------- Index name Owner Type Cluster Columns 550_2539 informix unique No prod_upc_num prod_pc_code_num Product_Vendor Table Index ------------------------------------ Index name Owner Type Cluster Columns 549_2527 informix unique No pdvn_upc_num pdvn_pc_code_num pdvn_vendor_num I've run the above query with SET EXPLAIN ON and received the following disappointing results. The results indicate that the optimizer isn't "choosing" to use the upc/code index that exists in both tables. I've tried updating both tables HIGH and reseting the DISTRIBUTION w/o success. The query actually fails when it's run with an error indicating that there isn't enough SHARED MEMORY available to run the query. The system has an unlimited amount of memory allocated to it, so I think that the poor choice of query paths is leading to the error. The error shown is: 208:Memory allocation failed during query process. QUERY: -------- SELECT * FROM product_vendor, product WHERE prod_upc_num = pdvn_upc_num AND prod_pc_code_num = pdvn_pc_code_num AND prod_status = 'A' ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num Estimated Cost: 91517 Estimated # of Rows Returned: 108382 Temporary Files Required For: Order By 1) informix.product_vendor: SEQUENTIAL SCAN 2) informix.product: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.product_vendor.pdvn_pc_code_num = informix.product.prod_pc_code_num AND informix.product_vendor.pdvn_upc_num = informix.product.prod_upc_num ) I believe the optimizer should be choosing this path instead: QUERY: ------ SELECT * FROM product_vendor, product WHERE prod_upc_num = pdvn_upc_num AND prod_pc_code_num = pdvn_pc_code_num ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num Estimated Cost: ? Estimated # of Rows Returned: 108382 Temporary Files Required For: Order By 1) informix.product: SEQUENTIAL SCAN 2) informix.product_vendor: INDEX PATH (1) Index Keys: pdvn_upc_num pdvn_pc_code_num pdvn_vendor_num Lower Index Filter: (informix.product_vendor.pdvn_pc_code_num = informix .product.prod_pc_code_num AND informix.product_vendor.pdvn_upc_num = informix.product.prod_upc_num ) Any additional suggestions you can provide for making the optimizer use the existing indexes would be GREATLY appreciated. Thanks David Murray IS Project Leader Schenectady, NY

Looks like you have OPTCOMPIND set to 2. Reset that to zero (0) and bounce the engine so that it favors nested loop joins over hash joins. It is running out of memory to create the hash table on the inner table. Art S. Kagel David Murray wrote: > > We're running Informix OnLine 7.20 UC2 in an HP-UX 10.10 environment and > one of our more simplistic two table join queries seems to be being > mis-optimized. The query is listed below: > > SELECT * > FROM product_vendor, product > WHERE prod_upc_num = pdvn_upc_num > AND prod_pc_code_num = pdvn_pc_code_num > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > Note: Both the product and product_vendor tables contain 80K rows or > approximately the same width. > > Product table Product_Vendor Table > ------------------- > ---------------------------------- > prod_upc_num decimal(14,0) pdvn_upc_num decimal(14,0) > > prod_pc_code_num decimal(10,0) pdvn_pc_code_num decimal (10,0) > > prod_sales_dept smallint pdvn_vendor_num decimal > (10,0) > .... > .... > > Product Table Index > -------------------------- > Index name Owner Type Cluster Columns > 550_2539 informix unique No prod_upc_num > > prod_pc_code_num > > Product_Vendor Table Index > ------------------------------------ > Index name Owner Type Cluster Columns > > 549_2527 informix unique No pdvn_upc_num > pdvn_pc_code_num > pdvn_vendor_num > > I've run the above query with SET EXPLAIN ON and received the following > disappointing results. The results indicate that the optimizer isn't > "choosing" to use the upc/code index that exists in both tables. I've tried > updating both tables HIGH and reseting the DISTRIBUTION w/o success. The > query actually fails when it's run with an error indicating that there > isn't enough SHARED MEMORY available to run the query. The system has an > unlimited amount of memory allocated to it, so I think that the poor choice > of query paths is leading to the error. The error shown is: > > 208:Memory allocation failed during query process. > > QUERY: > -------- > SELECT * > FROM product_vendor, product > WHERE prod_upc_num = pdvn_upc_num > AND prod_pc_code_num = pdvn_pc_code_num > AND prod_status = 'A' > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > Estimated Cost: 91517 > Estimated # of Rows Returned: 108382 > Temporary Files Required For: Order By > > 1) informix.product_vendor: SEQUENTIAL SCAN > > 2) informix.product: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > Dynamic Hash Filters: (informix.product_vendor.pdvn_pc_code_num = > informix.product.prod_pc_code_num AND > informix.product_vendor.pdvn_upc_num = > informix.product.prod_upc_num ) > > I believe the optimizer should be choosing this path instead: > > QUERY: > ------ > SELECT * > FROM product_vendor, product > WHERE prod_upc_num = pdvn_upc_num > AND prod_pc_code_num = pdvn_pc_code_num > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > Estimated Cost: ? > Estimated # of Rows Returned: 108382 > Temporary Files Required For: Order By > > 1) informix.product: SEQUENTIAL SCAN > > 2) informix.product_vendor: INDEX PATH > > (1) Index Keys: pdvn_upc_num pdvn_pc_code_num pdvn_vendor_num > Lower Index Filter: (informix.product_vendor.pdvn_pc_code_num = > informix > .product.prod_pc_code_num AND informix.product_vendor.pdvn_upc_num > = > informix.product.prod_upc_num ) > > Any additional suggestions you can provide for making the optimizer use the > existing indexes would be GREATLY appreciated. Thanks > > David Murray > IS Project Leader > Schenectady, NY

This worked perfectly. Thanks for the tip! Art S. Kagel <kagel@bloomberg.net> wrote in message news:3846F2B9.4DC0E7AC@bloomberg.net... > Looks like you have OPTCOMPIND set to 2. Reset that to zero (0) and > bounce the engine so that it favors nested loop joins over hash joins. It > is running out of memory to create the hash table on the inner table. > > Art S. Kagel > > David Murray wrote: > > > > We're running Informix OnLine 7.20 UC2 in an HP-UX 10.10 environment and > > one of our more simplistic two table join queries seems to be being > > mis-optimized. The query is listed below: > > > > SELECT * > > FROM product_vendor, product > > WHERE prod_upc_num = pdvn_upc_num > > AND prod_pc_code_num = pdvn_pc_code_num > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > Note: Both the product and product_vendor tables contain 80K rows or > > approximately the same width. > > > > Product table Product_Vendor Table > > ------------------- > > ---------------------------------- > > prod_upc_num decimal(14,0) pdvn_upc_num decimal(14,0) > > > > prod_pc_code_num decimal(10,0) pdvn_pc_code_num decimal (10,0) > > > > prod_sales_dept smallint pdvn_vendor_num decimal > > (10,0) > > .... > > .... > > > > Product Table Index > > -------------------------- > > Index name Owner Type Cluster Columns > > 550_2539 informix unique No prod_upc_num > > > > prod_pc_code_num > > > > Product_Vendor Table Index > > ------------------------------------ > > Index name Owner Type Cluster Columns > > > > 549_2527 informix unique No pdvn_upc_num > > pdvn_pc_code_num > > pdvn_vendor_num > > > > I've run the above query with SET EXPLAIN ON and received the following > > disappointing results. The results indicate that the optimizer isn't > > "choosing" to use the upc/code index that exists in both tables. I've tried > > updating both tables HIGH and reseting the DISTRIBUTION w/o success. The > > query actually fails when it's run with an error indicating that there > > isn't enough SHARED MEMORY available to run the query. The system has an > > unlimited amount of memory allocated to it, so I think that the poor choice > > of query paths is leading to the error. The error shown is: > > > > 208:Memory allocation failed during query process. > > > > QUERY: > > -------- > > SELECT * > > FROM product_vendor, product > > WHERE prod_upc_num = pdvn_upc_num > > AND prod_pc_code_num = pdvn_pc_code_num > > AND prod_status = 'A' > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > Estimated Cost: 91517 > > Estimated # of Rows Returned: 108382 > > Temporary Files Required For: Order By > > > > 1) informix.product_vendor: SEQUENTIAL SCAN > > > > 2) informix.product: SEQUENTIAL SCAN > > > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.product_vendor.pdvn_pc_code_num = > > informix.product.prod_pc_code_num AND > > informix.product_vendor.pdvn_upc_num = > > informix.product.prod_upc_num ) > > > > I believe the optimizer should be choosing this path instead: > > > > QUERY: > > ------ > > SELECT * > > FROM product_vendor, product > > WHERE prod_upc_num = pdvn_upc_num > > AND prod_pc_code_num = pdvn_pc_code_num > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > Estimated Cost: ? > > Estimated # of Rows Returned: 108382 > > Temporary Files Required For: Order By > > > > 1) informix.product: SEQUENTIAL SCAN > > > > 2) informix.product_vendor: INDEX PATH > > > > (1) Index Keys: pdvn_upc_num pdvn_pc_code_num pdvn_vendor_num > > Lower Index Filter: (informix.product_vendor.pdvn_pc_code_num = > > informix > > .product.prod_pc_code_num AND informix.product_vendor.pdvn_upc_num > > = > > informix.product.prod_upc_num ) > > > > Any additional suggestions you can provide for making the optimizer use the > > existing indexes would be GREATLY appreciated. Thanks > > > > David Murray > > IS Project Leader > > Schenectady, NY

The OPTCOPMIND setting change from 1 to 0 fixed the query that I specified in my newsgroup below. The query is now is 'choosing' to use the available indexes in it query path. However, now another query is failing with the same 'ambiguous' Informix error: 208: Memory Allocation Failed During Query Processing It seems that I'm damned if "I DO" and damned if "I DON'T" change the OPTCOPMIND setting. Is there any chance that this 208 error is really indicating that I'm hitting some sort of Shared Memory limit. I know that my ID 7.20 UC2 DB is setup to with SHMTOTAL=0 (Unlimited), so how can I be running out of memory? If I'm hitting some other HP-UX 10.10 Shared Memory limitation, is there a way I can detect that situation. David Murray IS Project Leader Schenectady, NY Art S. Kagel <kagel@bloomberg.net> wrote in article <3846F2B9.4DC0E7AC@bloomberg.net>... > Looks like you have OPTCOMPIND set to 2. Reset that to zero (0) and > bounce the engine so that it favors nested loop joins over hash joins. It > is running out of memory to create the hash table on the inner table. > > Art S. Kagel > > David Murray wrote: > > > > We're running Informix OnLine 7.20 UC2 in an HP-UX 10.10 environment and > > one of our more simplistic two table join queries seems to be being > > mis-optimized. The query is listed below: > > > > SELECT * > > FROM product_vendor, product > > WHERE prod_upc_num = pdvn_upc_num > > AND prod_pc_code_num = pdvn_pc_code_num > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > Note: Both the product and product_vendor tables contain 80K rows or > > approximately the same width. > > > > Product table Product_Vendor Table > > ------------------- > > ---------------------------------- > > prod_upc_num decimal(14,0) pdvn_upc_num decimal(14,0) > > > > prod_pc_code_num decimal(10,0) pdvn_pc_code_num decimal (10,0) > > > > prod_sales_dept smallint pdvn_vendor_num decimal > > (10,0) > > .... > > .... > > > > Product Table Index > > -------------------------- > > Index name Owner Type Cluster Columns > > 550_2539 informix unique No prod_upc_num > > > > prod_pc_code_num > > > > Product_Vendor Table Index > > ------------------------------------ > > Index name Owner Type Cluster Columns > > > > 549_2527 informix unique No pdvn_upc_num > > pdvn_pc_code_num > > pdvn_vendor_num > > > > I've run the above query with SET EXPLAIN ON and received the following > > disappointing results. The results indicate that the optimizer isn't > > "choosing" to use the upc/code index that exists in both tables. I've tried > > updating both tables HIGH and reseting the DISTRIBUTION w/o success. The > > query actually fails when it's run with an error indicating that there > > isn't enough SHARED MEMORY available to run the query. The system has an > > unlimited amount of memory allocated to it, so I think that the poor choice > > of query paths is leading to the error. The error shown is: > > > > 208:Memory allocation failed during query process. > > > > QUERY: > > -------- > > SELECT * > > FROM product_vendor, product > > WHERE prod_upc_num = pdvn_upc_num > > AND prod_pc_code_num = pdvn_pc_code_num > > AND prod_status = 'A' > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > Estimated Cost: 91517 > > Estimated # of Rows Returned: 108382 > > Temporary Files Required For: Order By > > > > 1) informix.product_vendor: SEQUENTIAL SCAN > > > > 2) informix.product: SEQUENTIAL SCAN > > > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: (informix.product_vendor.pdvn_pc_code_num = > > informix.product.prod_pc_code_num AND > > informix.product_vendor.pdvn_upc_num = > > informix.product.prod_upc_num ) > > > > I believe the optimizer should be choosing this path instead: > > > > QUERY: > > ------ > > SELECT * > > FROM product_vendor, product > > WHERE prod_upc_num = pdvn_upc_num > > AND prod_pc_code_num = pdvn_pc_code_num > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > Estimated Cost: ? > > Estimated # of Rows Returned: 108382 > > Temporary Files Required For: Order By > > > > 1) informix.product: SEQUENTIAL SCAN > > > > 2) informix.product_vendor: INDEX PATH > > > > (1) Index Keys: pdvn_upc_num pdvn_pc_code_num pdvn_vendor_num > > Lower Index Filter: (informix.product_vendor.pdvn_pc_code_num = > > informix > > .product.prod_pc_code_num AND informix.product_vendor.pdvn_upc_num > > = > > informix.product.prod_upc_num ) > > > > Any additional suggestions you can provide for making the optimizer use the > > existing indexes would be GREATLY appreciated. Thanks > > > > David Murray > > IS Project Leader > > Schenectady, NY >

Art, I am puzzled why nested-loop joins are a preferred solution here when everything I ever learned about nested loop joins tells me this is the worst possible solution. Am I missing something here? Is there some kind of bug in older releases of the 7 engine that makes this the knee-jerk solution? It flies in the face of what Informix trainers are telling people, and good common sense. I realize there must be a valid reason for your solution Art, but I am baffled that this problem with the engine would persist in later releases where you would continue to recommend it. There was a bug in the XPS engine a while back where nested-loop joins were absolutely killing performance as a bug in the optimizer, and a noted bug. I really do need to understand the thinking behind this solution because it just doesn't make sense. ( at least to me :-) Thanks in advance, Tim David Murray wrote: > > The OPTCOPMIND setting change from 1 to 0 fixed the query that I specified > in my newsgroup below. The query is now is 'choosing' to use the available > indexes in it query path. However, now another query is failing with the > same 'ambiguous' Informix error: > > 208: Memory Allocation Failed During Query Processing> > It seems that I'm damned if "I DO" and damned if "I DON'T" change the > OPTCOPMIND setting. Is there any chance that this 208 error is really > indicating that I'm hitting some sort of Shared Memory limit. I know that > my ID 7.20 UC2 DB is setup to with SHMTOTAL=0 (Unlimited), so how can I be > running out of memory? If I'm hitting some other HP-UX 10.10 Shared Memory > limitation, is there a way I can detect that situation. > > David Murray > IS Project Leader > Schenectady, NY > > Art S. Kagel <kagel@bloomberg.net> wrote in article > <3846F2B9.4DC0E7AC@bloomberg.net>... > > Looks like you have OPTCOMPIND set to 2. Reset that to zero (0) and > > bounce the engine so that it favors nested loop joins over hash joins. > It > > is running out of memory to create the hash table on the inner table. > > > > Art S. Kagel > > > > David Murray wrote: > > > > > > We're running Informix OnLine 7.20 UC2 in an HP-UX 10.10 environment > and > > > one of our more simplistic two table join queries seems to be being > > > mis-optimized. The query is listed below: > > > > > > SELECT * > > > FROM product_vendor, product > > > WHERE prod_upc_num = pdvn_upc_num > > > AND prod_pc_code_num = pdvn_pc_code_num > > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > > > Note: Both the product and product_vendor tables contain 80K rows or > > > approximately the same width. > > > > > > Product table Product_Vendor > Table > > > ------------------- > > > ---------------------------------- > > > prod_upc_num decimal(14,0) pdvn_upc_num > decimal(14,0) > > > > > > prod_pc_code_num decimal(10,0) pdvn_pc_code_num decimal (10,0) > > > > > > prod_sales_dept smallint pdvn_vendor_num > decimal > > > (10,0) > > > .... > > > .... > > > > > > Product Table Index > > > -------------------------- > > > Index name Owner Type Cluster Columns > > > 550_2539 informix unique No prod_upc_num > > > > > > prod_pc_code_num > > > > > > Product_Vendor Table Index > > > ------------------------------------ > > > Index name Owner Type Cluster Columns > > > > > > 549_2527 informix unique No pdvn_upc_num > > > pdvn_pc_code_num > > > pdvn_vendor_num > > > > > > I've run the above query with SET EXPLAIN ON and received the following > > > disappointing results. The results indicate that the optimizer isn't > > > "choosing" to use the upc/code index that exists in both tables. I've > tried > > > updating both tables HIGH and reseting the DISTRIBUTION w/o success. > The > > > query actually fails when it's run with an error indicating that there > > > isn't enough SHARED MEMORY available to run the query. The system has > an > > > unlimited amount of memory allocated to it, so I think that the poor > choice > > > of query paths is leading to the error. The error shown is: > > > > > > 208:Memory allocation failed during query process. > > > > > > QUERY: > > > -------- > > > SELECT * > > > FROM product_vendor, product > > > WHERE prod_upc_num = pdvn_upc_num > > > AND prod_pc_code_num = pdvn_pc_code_num > > > AND prod_status = 'A' > > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > > > Estimated Cost: 91517 > > > Estimated # of Rows Returned: 108382 > > > Temporary Files Required For: Order By > > > > > > 1) informix.product_vendor: SEQUENTIAL SCAN > > > > > > 2) informix.product: SEQUENTIAL SCAN > > > > > > DYNAMIC HASH JOIN > > > Dynamic Hash Filters: (informix.product_vendor.pdvn_pc_code_num = > > > informix.product.prod_pc_code_num AND > > > informix.product_vendor.pdvn_upc_num = > > > informix.product.prod_upc_num ) > > > > > > I believe the optimizer should be choosing this path instead: > > > > > > QUERY: > > > ------ > > > SELECT * > > > FROM product_vendor, product > > > WHERE prod_upc_num = pdvn_upc_num > > > AND prod_pc_code_num = pdvn_pc_code_num > > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > > > Estimated Cost: ? > > > Estimated # of Rows Returned: 108382 > > > Temporary Files Required For: Order By > > > > > > 1) informix.product: SEQUENTIAL SCAN > > > > > > 2) informix.product_vendor: INDEX PATH > > > > > > (1) Index Keys: pdvn_upc_num pdvn_pc_code_num pdvn_vendor_num > > > Lower Index Filter: (informix.product_vendor.pdvn_pc_code_num > = > > > informix > > > .product.prod_pc_code_num AND > informix.product_vendor.pdvn_upc_num > > > = > > > informix.product.prod_upc_num ) > > > > > > Any additional suggestions you can provide for making the optimizer use > the > > > existing indexes would be GREATLY appreciated. Thanks > > > > > > David Murray > > > IS Project Leader > > > Schenectady, NY > > -- . .- .-- .--- .---- Tim Schaefer .----- tschaefe@bellsouth.net .---- http://www.inxutil.com .--- .-- .- .

Tim Schaefer wrote: > > Art, > > I am puzzled why nested-loop joins are a preferred solution here > when everything I ever learned about nested loop joins tells me > this is the worst possible solution. Often they are, sometimes they are not. Seldom granted but true. For example if you prefer FIRST ROWS optimization, as we do, it is the only responsive solution because the overhead of the index or hash table build will prevent the first rows from being returned in a timely fashion even though the complete query will take considerably longer. In our case it is highly likely that the users will NEVER retrieve more than the first 18 to 54 rows when thousands are available that meet the query filters. Before 7.30 and FIRST ROWS optimization available explicitely setting OPTCOMPIND this way was the only way to force the engine to do things in a reasonable, for our needs and those of MANY other users, way. HOWEVER, that was not the point here. David's problem was that he did not have enough memory available to complete the sorting needed to produce the hash table so his queries were failing. By changing OPTCOMPIND and favoring nested loop joins he avoids the sort and gets the data. In this case getting the data faster was less important than getting the data at all. As to his second inquiry: To David Murray, I missed the followup posting, sorry. You should post sqexplain output for the query that is now failing and some system information like onstat -g seg, amount of RAM and swap, header lines from top or sar or another tool that will report on total, used, and available memory and swap around the time the query fails. Also post the schema of the table(s) involved in the query. We will help if we can. Art S. Kagel > Am I missing something here? Is there some kind of bug in older > releases of the 7 engine that makes this the knee-jerk solution? > It flies in the face of what Informix trainers are telling people, > and good common sense. I realize there must be a valid reason for > your solution Art, but I am baffled that this problem with the > engine would persist in later releases where you would continue > to recommend it. There was a bug in the XPS engine a while back > where nested-loop joins were absolutely killing performance as a bug > in the optimizer, and a noted bug. I really do need to understand > the thinking behind this solution because it just doesn't make sense. > ( at least to me :-) > > Thanks in advance, > > Tim > > David Murray wrote: > > > > The OPTCOPMIND setting change from 1 to 0 fixed the query that I specified > > in my newsgroup below. The query is now is 'choosing' to use the available > > indexes in it query path. However, now another query is failing with the > > same 'ambiguous' Informix error: > > > > 208: Memory Allocation Failed During Query Processing> > > > It seems that I'm damned if "I DO" and damned if "I DON'T" change the > > OPTCOPMIND setting. Is there any chance that this 208 error is really > > indicating that I'm hitting some sort of Shared Memory limit. I know that > > my ID 7.20 UC2 DB is setup to with SHMTOTAL=0 (Unlimited), so how can I be > > running out of memory? If I'm hitting some other HP-UX 10.10 Shared Memory > > limitation, is there a way I can detect that situation. > > > > David Murray > > IS Project Leader > > Schenectady, NY > > > > Art S. Kagel <kagel@bloomberg.net> wrote in article > > <3846F2B9.4DC0E7AC@bloomberg.net>... > > > Looks like you have OPTCOMPIND set to 2. Reset that to zero (0) and > > > bounce the engine so that it favors nested loop joins over hash joins. > > It > > > is running out of memory to create the hash table on the inner table. > > > > > > Art S. Kagel > > > > > > David Murray wrote: > > > > > > > > We're running Informix OnLine 7.20 UC2 in an HP-UX 10.10 environment > > and > > > > one of our more simplistic two table join queries seems to be being > > > > mis-optimized. The query is listed below: > > > > > > > > SELECT * > > > > FROM product_vendor, product > > > > WHERE prod_upc_num = pdvn_upc_num > > > > AND prod_pc_code_num = pdvn_pc_code_num > > > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > > > > > Note: Both the product and product_vendor tables contain 80K rows or > > > > approximately the same width. > > > > > > > > Product table Product_Vendor > > Table > > > > ------------------- > > > > ---------------------------------- > > > > prod_upc_num decimal(14,0) pdvn_upc_num > > decimal(14,0) > > > > > > > > prod_pc_code_num decimal(10,0) pdvn_pc_code_num decimal (10,0) > > > > > > > > prod_sales_dept smallint pdvn_vendor_num > > decimal > > > > (10,0) > > > > .... > > > > .... > > > > > > > > Product Table Index > > > > -------------------------- > > > > Index name Owner Type Cluster Columns > > > > 550_2539 informix unique No prod_upc_num > > > > > > > > prod_pc_code_num > > > > > > > > Product_Vendor Table Index > > > > ------------------------------------ > > > > Index name Owner Type Cluster Columns > > > > > > > > 549_2527 informix unique No pdvn_upc_num > > > > pdvn_pc_code_num > > > > pdvn_vendor_num > > > > > > > > I've run the above query with SET EXPLAIN ON and received the following > > > > disappointing results. The results indicate that the optimizer isn't > > > > "choosing" to use the upc/code index that exists in both tables. I've > > tried > > > > updating both tables HIGH and reseting the DISTRIBUTION w/o success. > > The > > > > query actually fails when it's run with an error indicating that there > > > > isn't enough SHARED MEMORY available to run the query. The system has > > an > > > > unlimited amount of memory allocated to it, so I think that the poor > > choice > > > > of query paths is leading to the error. The error shown is: > > > > > > > > 208:Memory allocation failed during query process. > > > > > > > > QUERY: > > > > -------- > > > > SELECT * > > > > FROM product_vendor, product > > > > WHERE prod_upc_num = pdvn_upc_num > > > > AND prod_pc_code_num = pdvn_pc_code_num > > > > AND prod_status = 'A' > > > > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > > > > > > > Estimated Cost: 91517 > > > > Estimated # of Rows Returned: 108382 > > > > Temporary Files Required For: Order By > > > > > > > > 1) informix.product_vendor: SEQUENTIAL SCAN > > > > > > > > 2) informix.product: SEQUENTIAL SCAN > > > > > > > > DYNAMIC HASH JOIN > > > > Dynamic Hash Filters: (informix.product_vendor.pdvn_pc_code_num = > > > > informix.product.prod_pc_code_num AND > > > > informix.product_vendor.pdvn_upc_num = > > > > informix.product.prod_upc_num ) > > > > > > > > I believe the optimizer should be choosing this path ins