Update statistics info for temporary tables
Milan Rafaj asked where to get update statistics information for temporary tables. Art Kagel said it is not available anywhere, except index distributions on lead columns. Milan had an ORDERED directive reported as followed but with a bad join order. Art advised comparing estimated and actual rows in the SET EXPLAIN output to judge stale or insufficient distributions.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, can I get from somewhere update statistics info on temporary table this info is not available in systables, sysindexes and sysdistrib Thank you ------------------------------ Milan Rafaj ------------------------------ #Informix
Milan: No, the data distribution and basic statistics for temp tables are not available anywhere. If you created one or more indexes on the temp table after populating it, then there will be the usual distributions on the lead column of each index, but that's it unless you ran a manual UPDATE STATISTICS on the temp table. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Thanks Art, it's pity, so only optimizer has access to this info somewhere in memory. I wanted to provide more info to support guys as we have problem with select {+ordered explain} ... from main_permanent main inner join temp1 on main.col = temp1.col -- more inner join can be present left outer temp2 on main.col2 = temp2.col2 -- mote left outer can be present ... and even though explain says, ORDERED directive is FOLLOWED, main table is chosen not to be 1st one in plan - leading to never-ending processing. All temp tables are indexed and updates statistics run o them ------------------------------ Milan Rafaj ------------------------------
Yea, the only way you can see if the distributions on the tables are sufficient is to look at the run time table at the end of the SET EXPLAIN output and compare the estimated and actual rows processed. If they are not close for a given table then the distributions are stale or insufficiently detailed (ie MEDIUM instead of HIGH or too few buckets, etc.). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.