Informix

 View Only

  • 1.  Update statistics info for temporary tables

    Posted Fri October 02, 2020 08:01 AM
    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


  • 2.  RE: Update statistics info for temporary tables

    IBM Champion
    Posted Fri October 02, 2020 01:05 PM
    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


    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.






    Original Message


  • 3.  RE: Update statistics info for temporary tables

    Posted Mon October 05, 2020 10:49 AM
    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
    ------------------------------

    Original Message


  • 4.  RE: Update statistics info for temporary tables

    IBM Champion
    Posted Mon October 05, 2020 11:30 AM
    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


    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.






    Original Message