RE: Optimizing Select count distinct statement
Also, try setting the OPTCOMPIND variable to 0, bounce the server, rerun
update statistics,then rerun the query.
You can change this variable through the menus : onmonitor/Parameter/pdQ/
Optimizer Hint: 0
Let me know if that changes the performace.
ALSO- 7.12 versions have memory leaks which cause some queries to take
forever to
complete. We greatly improved our performance upgrading to version 7.13.
You way want to check you release note on update statistics. When we
went to 7.12 on-line I used the same update statistics as discussed
below and had some problems with our application not getting queries
optimized correctly. What I found in the 7.12 release notes is the
following rules for update statistics.
1. update statistics medium for all fields that are not the first
element in any index on the table.
2. update statistics high for all fields that are the first element in
any index on the table.
3. update statistics low for all fields that are in compound indexes.
After following these rules my problems with optimiziation were complete
corrected.
Hope this helps...
Russ.....
}----------
}From: staccuc@riq.qc.ca[SMTP:staccuc@riq.qc.ca]
}Sent: Friday, February 14, 1997 12:00 AM
}To: informix-list@rmy.emory.edu
}Subject: Re: Optimizing Select count distinct statement
}
}On 13 Feb 1997 17:53:03 GMT, kleckab@river.it.gvsu.edu (Barry KLecka)
}wrote:
}No, field2 is not part of an index, but I ran the UPDATE STATISTIC on
}each indexed field of that table.
}
}>If field2 is part of a multiple field index then run update statistics
}>high on that field. I have encountered this with 7.2. It ignores
}>a multiple field index when you use only one field.
}>
}>staccuc@riq.qc.ca wrote:
}>: Anyone has an idea on how to optimize this kind of statement
}>
}>: select count (distinct field1)
}>: from table1
}>: where field2 = 'something'
}>
}>: There is indexes on both field1 and field2, but Informix Online 7.2 is
}>: not using them according to the explain plan.
}>
}>: Any idea ?
}>
}>: Thank you
}>
}>--
}>And I still use vi.........
}>
}
}