Thursday, December 24, 2009

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


CREATE OR REPLACE FUNCTION get_rows_number (sql_select VARCHAR2, sql_id VARCHAR2) RETURN NUMBER AS
      rows_number NUMBER;‎
BEGIN
    EXECUTE IMMEDIATE
        ‘EXPLAIN PLAN SET STATEMENT_ID = ‘||CHR(39) || sql_id || CHR(39)||’ FOR ‘ || sql_select;‎

    SELECT t.cardinality
       INTO rows_number
       FROM plan_table T
    WHERE statement_id = sql_id AND ID = 0;‎

   RETURN rows_number;‎
END;‎


No comments: