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

User_W916Z

Mark D Powell

 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.

--