Analyze temporary tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Analyze temporary tables

  1. #1
    Join Date
    Feb 2001
    Posts
    180
    Hi you all,
    I have created some temporary tables, which contents are user-dependent.
    I tried to analyze the table and did not get any errors,
    but when examining the statistics I don't see any figures.
    When selecting from those tables, the explain plan the optimizer stays on CHOOSE.
    So, is it possible to compute statistics on temporary tables or doesn't it make any sence?

    Who can tell something about it?

    TIA
    Regards
    Ben de Boer

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Do you get statistics on others?
    If not then you must set timed_statistics=true.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    On ordinairy tables I do have statistic-figures and normally queries will execute CostBased.
    Regards
    Ben de Boer

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Analyzing temporary tables does not result in any ORA error, but on the other hand it doesn't collect any statistics either! So it is useless to analyze global temporary tables.

    However the fact that CBO does not have any clue of how large temp table realy is might sometimes give it a big trouble in deciding the optimal execution path, resulting sometimes in very bad decision. But if you know that every user accesing that temporary table will use it the same way with approximately the same number of rows, you can fake the statistics for that table by using DBMS_STATS.SET_TABLE_STATS procedure, thus artificially importing your own "guessed" values for number of rows and blocks into the data dictionary, for example.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2001
    Posts
    180
    Thanks Jurij,
    It helps.
    Regards
    Ben de Boer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width