Re: Optimizing queries:: The Rules

That is because Oracle is NOT SQL Server. You need to use EXPLAIN PLAN and TKPROF and DBMS_PROFILER and make sure that table and index statistics are current. In Oracle ... real work is involved. Of course ... that is balanced by gaining performance and scalability.

I will give you an example. Here are six queries that do the exact same thing:

SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT srvr_id
   FROM serv_inst);

SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT i.srvr_id
   FROM serv_inst i, servers s
   WHERE i.srvr_id = s.srvr_id);

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

   SELECT srvr_id
   FROM servers
   MINUS
   SELECT srvr_id
   FROM serv_inst);

SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

I'll bet a latte' at Starbuck's that no one, without using EXPLAIN PLAN, and/or running them could order these based on COST. I can tell you the difference is 700%.

Many people will tell you "rules of thumb". But with improvements in the CBO (cost based optimizer) many of the rules of thumb developed over the years are no longer valid.

Daniel Morgan

Sandy Murdock wrote:

> I am (by far) more familiar with SQL server than Oracle.
>
> I am aware of a great many 'Rules of Thumb' for making your
> queries/stored procedures run faster. How to determine which table to
> join to which when the join can be made from more than one table in
> the query, order of operation, naming issues etc.
>
> I have not had much luck in finding the same for Oracle.
>
> There is a lot about using performance optimizing tools to help, but
> wouldn't it be easier for me if I just wrote better code in the first
> place - THEN optimized that???
>
> The applications that we are building use Packages and Stored
> Procedures only, no query is allowed to run that we do not build in
> advance.
>
> Anyone know a good source of such information. I am not looking for
> just syntax for SQL, but information on when to use which command for
> efficiency reasons etc.
>
> If you know of a good source please let me know. Internet is better
> (it is immediately available and searchable) but I am not at all
> adverse to getting in my car and buying a book.
>
> Thanks.
Received on Wed Feb 06 2002 - 14:52:38 CST