Re: Help (Serial, Fragments:ALL) Again

kevin patten (patten@test) wrote: <SNIP> : HELP. What does fragments:ALL mean? How do we fix it. : BTW on the index the number of pages allocated is 13012 and the number of : pages used is 10578 to I don't know how it could be fragmented. The : database has only one extent. Kevin, "Fragmented" in Informix-speak is not a bad thing, as it is in the rest of the computer world. Fragmented means that someone has consciously decided to spread a table across multiple chunks (and probably different disks if they did it right) in order to best optimize the parallel capabilities of the database. : The set explain is as follows : select * from flmastpymt : where pa_prechk_date = "07/1597" : and pa_prechk_code = "00:45" from the explain data on the next page, it looks like pa_prechk_code is a datetime. Try the query without this part of the WHERE and see what it does. If it goes a lot faster, you've found the problem. I suspect that you're getting some datetime conversion problems here. The "00:45" format is not optimal. Try "00:45:00" if that's what you mean....it may avoid the conversion. : and pa_payee_code = 70102 : and pa_host_status = 'N' : and pa_check_number is NULL : Estimated cost :1 Please post the table schema and the indexes on the table. run a dbschema with the -ss option and we'll also get the fragmentation strategy. Do you really need the "select *"? Can you pull in less data? Do you use all the fields in the table in this select? : Filters: (family.flmastpymt.pa_payee_code = 70102 AND : (family.flmastpymt.pa_host_status = 'N' AND : family.flmastpymt.pa_check_number is NULL) ) : (1) Index Keys: pa_precheck_date pa_precheck_time pa_receipt_id (Serial, : fragments: ALL) How unique are the values in pa_precheck_date? How many checks per day? Is pa_precheck_date an Informix date field? : Lower Index Filter: (family.flmastpymt.pa_prechk_date = 07/15/199 AND : family.flmastpymt.pa_prechk_time = datetime(00:45) hour to minute) : -- : Kevin Patten : Technical Support Manager : Florida Association of Court Clerks : 3375 N.E. Capital Circle Suite 1 : Tallahassee, Fl. 32308 : 904-921-0808 : http://www.flclerks.com : patten@flclerks.com Overall, my bet's on the datetime conversion as the culprit. Joe -- --------------------------------------------------------------------------- Joe Lumbley(jlumbley@netcom.com) author of: "INFORMIX DBA Survival Guide" Slaving away on "INFORMIX DEBUGGERS Survival Guide", available early 1998 from Prentice Hall/Informix Press. If you have debugging tips, tools, or ideas to share, please send me some e-mail. ---------------------------------------------------------------------------