Re: performance of joins

There are a number of issues I see with your test:

1. Your test query seems somewhat bizarre. A query of that nature is almost never going to be used in a real-world scenario - you've got what nearly amounts to a cartesian product. Are you positive that that query models something you're interested in doing?
2. When evaluating query performance from the perspective of query plans, the most important column is usually "query", which indicates the number of data pages that Oracle visited during the execution of the query (note that this number would count N visits to the same page as N, so it represents the amount of "work" done by the database). Often the "disk" number is more important, since this represents getting a page from disk, but this number can often be influenced by the size of your cache or other activity in other sessions, so I don't put too much weight on it for my first pass in analyzing a query.

That said, your tests show:

NESTED LOOPS: 380,000 buffer gets
NESTED INDEX JOIN: 126,280 buffer gets
MERGE: 76 buffer gets

so the stats would confirm your initial assumptions about performance based on plan were correct.

3) The times that are posted for cpu & elapsed seem abnormally long - a query that performs 76 buffer gets and 0 disk reads should be measured in hundredths of a second. There may be something else going on your hardware that is leading to these slow times beyond what's going on in this query.

Can you run these queries again from sqlplus using the autotrace (set autotrace on) feature (it gives mostly the same information, but with a few other bits of info). If you get an error when setting autotrace on, you need to create the plan tables in your schema, which you can using the file utlxplan.sql located in the rdbms/admin directory of your oracle installation.

4) Given that your query is somewhat strange, it would not be unexpected to see unusual results with respect to various access plans - sometimes "dumb" plans are the right ones. If this were not the case, the optimizer would always choose sort-merge (or whatever plan you felt was best) for everything. For example, even an index that is highly selective (like one on a primary key) might still be a poor choice in a query like yours where you're comparing every row in table A to every other row in table B. Received on Thu Jun 02 2005 - 16:01:40 CDT