RE: Informix On-Line 7.24 UC5 Shared Memory Error.

We kept getting that Segmentation violation until Informix told us it was a bug with 7.2.4 and we needed to apply the patch to UC8. UC9 exists but only for certain Unix flavors. Have not gotten seg. violation since... :) Val -----Original Message----- From: David Murray [mailto:dmurray2@nycap.rr.com] Sent: Tuesday, September 28, 1999 9:53 PM To: informix-list@iiug.org Subject: Re: Informix On-Line 7.24 UC5 Shared Memory Error. David: - There already is an INDEX on PROD_STATUS that allows duplicates. I don't know why it's not being used. With only 30,000 rows the optimizer may have 'decided' that a sequential scan is more efficient than indexed reads. - There already is a UNIQUE INDEX on PROD_UPC_NUM/PROD_PC_CODE_NUM on the PRODUCT table. Addtionally, there is an INDEX on PDVN_UPC_NUM/PDVN_PC_CODE_NUM/PDVN_VENDOR_NUM on PRODUCT_VENDOR that allows duplicates. The sequential scan is for PDVN_VENDOR which has no reference on the PRODUCT table. This is expected and unavoidable. Regardless of the INDEXs, why am I getting a '208: Segmentation Violation Error. Again, the query doesn't run slow, it simply doesn't run. David Williams wrote in message ... >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