File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Query Estimation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Query Estimation" Watch "Query Estimation" New topic

Query Estimation

Scott Way

Joined: Mar 17, 2004
Posts: 1
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...
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
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.
I agree. Here's the link:
subject: Query Estimation
It's not a secret anymore!