Informix On-Line 7.24 UC5 Shared Memory Error.

Gurus: Whenever I run a rather simplist SQL query I get an immediate error indicating an "208-Memory Allocation Error". This seems to indicate that the DB doesn't have enough memory to support the query. However, the query is a simple two table sorted SQL UNLOAD that will produce about 30000 rows. The DB is setup to use an unlimited portion of the HP 400 box's 1 GIG of memory. It's almost as if the optimizer is OVER estimating the resources needed successfully execute the query. Most of the applications, other more complex queries run OK. Any suggestions would be appreciated. Thanks. Notes: - We have another production DB instance setup under 7.20 UC2 w/ half the memory of the box we're getting the error on. The query runs OK under that Informix release and environment. - I've already tried to update the DB statistics prior to running the query. Didn't improve results. - When I remove the order by clauses from the query it works ok. - When I run the sql explain on the query, I get the following: QUERY: ------ SELECT * FROM product_vendor, product Note: 30000 rows in product_vendor, 30000 rows in 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: 39577 ***NOTE: This seems way too high. Estimated # of Rows Returned: 31142 Maximum Threads: 4 Temporary Files Required For: Order By 1) informix.product: SEQUENTIAL SCAN ***NOTE: There is a dup idx on prod_status. Not sure why it's not being used. Updated table stats HIGH Filters: informix.product.prod_status = 'A' 2) informix.product_vendor: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.product.prod_pc_code_num = informix.product_ vendor.pdvn_pc_code_num AND informix.product.prod_upc_num = informix.product_ven dor.pdvn_upc_num )

In article <MeUH3.3375$86.149850@typhoon.nyroc.rr.com>, David Murray <dmurray2@nycap.rr.com> writes >Gurus: > >Whenever I run a rather simplist SQL query I get an immediate error >indicating an "208-Memory Allocation Error". This seems to indicate that the >DB >doesn't have enough memory to support the query. However, the query is a >simple two table sorted SQL UNLOAD that will produce about 30000 rows. The >DB is setup >to use an unlimited portion of the HP 400 box's 1 GIG of memory. It's >almost as if the optimizer is OVER estimating the resources needed >successfully execute the query. Most of the applications, other more complex >queries run OK. Any suggestions would be appreciated. Thanks. > >Notes: - We have another production DB instance setup under 7.20 UC2 w/ half > the memory of the box we're getting the error on. The query >runs OK under that Informix > release and environment. > - I've already tried to update the DB statistics prior to >running the query. Didn't improve results. > - When I remove the order by clauses from the query it works ok. > - When I run the sql explain on the query, I get the following: > >QUERY: >------ >SELECT * > FROM product_vendor, product Note: 30000 rows in >product_vendor, 30000 rows in 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: 39577 ***NOTE: This seems way too high. >Estimated # of Rows Returned: 31142 >Maximum Threads: 4 >Temporary Files Required For: Order By > >1) informix.product: SEQUENTIAL SCAN ***NOTE: There is a dup idx on >prod_status. Not sure why it's not > Surely prod_status is not that unique? Try creating an index on prod_status,prod_upc_num,prod_pc_code_num make this unique if you can.. >being used. Updated table stats HIGH > Filters: informix.product.prod_status = 'A' > >2) informix.product_vendor: SEQUENTIAL SCAN > Create an index on this table on pdvn_pc_code_num,pdvn_upc_num again unique if possible. In general indexes should exist on ALL column referenced in the where clause of the query and they should be unique. >DYNAMIC HASH JOIN > Dynamic Hash Filters: (informix.product.prod_pc_code_num = >informix.product_ >vendor.pdvn_pc_code_num AND informix.product.prod_upc_num = >informix.product_ven >dor.pdvn_upc_num ) > > > > -- David Williams