Query Estimation

[Scott Way]

We will be constructing a VLDB that allows end users to build and execute dynamic queries. We plan to queue expensive queries using JMS, and execute cheap queries immediately. Can you suggest a means to estimate the cost of running a SQL query in Java? This will be against an Oracle 10g database. I am familiar with explain plan, but I have only used that in the native SQL environment. Is there something similar or can I use explain plan? Your assistance may help cure disease and better humanity...
Scott

[Jeanne Boyarsky]

Scott,
I don't think you can get an explain type plan in java. You could do your own rough one though. If you know the approximate number of rows in each table, indexes on the table and join clauses, that should give you a rough order of maginitude for the query.

[Loren Rosen]

Explain plan basically creates a table with the plan info, and this table can be queried (using JDBC). You ought to be able to issue the explain plan via JDBC, or, failing that, write a PL/SQL stored function which gets the cost and call it.