Re: SQL deparser ?

I would use a combination of explain plan's filter predicates column for getting predicate information.

Also I would set _dump_qbc_tree=1 at session level before parsing the query (10.2 only) - that dumps out the parse tree of the query, fully resolved to base tables. So there's no need to start reverse engineering column/tablename meanings from view definitions.

Note that you might get some strange inconsistencies between those two sources as the QBC tree is generated right after successfully parsing the query text into references to base tables, but the explain plan's filter predicates are unparsed from compiled cursor after transformations though.

Cheers,
Tanel.

• Original Message ----- From: Bernard Polarski To: oracle-l_at_freelists.org Sent: Thursday, May 18, 2006 8:50 PM Subject: SQL deparser ?

  Hello lister,

  I am wondering if anybody knows a resource for a procedure/program that will isolate predicates for a given SQL.

  I am quite interrested in the work of Dan Tow (http://www.oreilly.com/catalog/sqltuning/) and SQL diagramming as a generic approache to tune SQL. However this method requests for an SQL of 20 tables and 50 joins to generate counts and ratio for each predicate and it is rapidily over the hundred SQL statement.

--
http://www.freelists.org/webpage/oracle-l