Subselects in Oracle Query

Question

Hi,

could anyone tell me if it makes a difference to Oracle 10g whether I use:

SELECT col1 FROM myTable WHERE col2 = 'someval' AND col3 = "someotherval"

or

SELECT col1 FROM
    SELECT col1, col2, col3 FROM (
        SELECT * FROM myTable
    ) WHERE col2 = 'someval'
) WHERE col3 = "someotherval"

According to the explain plan, the cost for the above is the same, but Im a blank when it comes to performance measurement.

Background is: in my application, I have several basic queries that I need to modify by arbitrary criteria at runtime to provide datasets for different clients. The WHERE conditions and fetched columns are specific to a client configuration file and some configs may require looking in col2 while others look in col5 and so on. In addition, users may add additional criteria and columns on top of the client configuration. I was thinking of using a set of decorators to achieve this, so if the above is the same performance wise, I could keep all three query parts cleanly separated.

It's an Oracle 10g. There is some hundred thousand rows in the table (actually it is a View).

Thanks for any suggestions.

Answer 1

Hello Gordon,

I would not worry too much about it beforehand. Oracle in general is pretty sophisticated when it comes to optimization, so I am confident it doesn't get confused by your nested queries. What you are trying to do sounds complicated enough, so you should first get it running, then make it fast (if its performance is not good enough).

Remember: "Premature optimization is the root of all evil." - Donald Knuth

Cheers, Tom

Answer 2

I may well work, i.e. the second query may well have the same impact performance/resource-wise than the first one, in the current implementation of Oracle.

Never the less, I would not rely on this type of thing, in particular when it comes to merely simplify the query building logic. In fact, depending on the specific business rules and underlying data model, there may be several alternatives to the decorator pattern (or even alternative ways of implementing it) you are thinking of using.

Answer 3

In general Oracle's Cost Based Optimizer will merge queries containing views (or in-line views in your case, which is essentially the same thing) as part of the query transformation phase of the optimisation.

This is known as "view merging", and will transform your second query into the first query. In some cases full view merging cannot be performed but even then "predicate pushing" might be allowed and performance will improve as a result.

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14211/optimops.htm#i37745

also see:

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14211/optimops.htm#i37745 http://www.oracle.com/pls/db102/to%5FURL?remark=ranked&urlname=http%3A%2F%2Fdownload.oracle.com%2Fdocs%2Fcd%2FB19306%5F01%2Fserver.102%2Fb14200%2Fsql%5Felements006.htm%23SQLRF50908 http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14237/initparams142.htm#REFRN10141 http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14200/sql%5Felements006.htm#SQLRF50706

Long story short, you should not have a problem here.