Database Administration (MOSC)

MOSC Banner

How to get estimated number of rows of query without running actual query?

User_W916Z
User_W916Z Posts: 2 Blue Ribbon
edited May 14, 2009 6:28AM in Database Administration (MOSC) 1 comment

Comments

  • Mark D Powell
    Mark D Powell Posts: 17,366 Rubellite
    edited Aug 27, 2009 5:44AM
     1 - There needs to be some verbage describing the need to ensure the CBO statistics are current and accurate before running this function

    2 - Considering how often the CBO gets the cardinality wrong there should probably be some warning about the accuracy

    --

    HTH -- Mark D Powell --

    Probably more importantly there is no example execution.  When I tried I got an Oracle error:

    ORA-14551: cannot perform a DML operation inside a query

    which makes me wonder if the code was tested before it was posted.

    Also if you run it twice and do not change the statement_id you will get an ORA-01422: exact fetch returns more than requested number of rows since the first execution would place rows int the plan table under the statement_id.

    --

    I suggest changing the function to a procedure and adding a delete on the plan table prior to running the explain.

    --

    HTH -- Mark D Powell --

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center