Re: speed of query

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Fri, 22 Oct 1999 11:44:41 -0700
Message-ID: <7uqbfs$1qs$1@plo.sierra.com>

In the absence of any other information, the rule-based optimizer must use the WHRE clause
sequencing to help determine the best execution path. If you are able to specify the most efficient indexed conditions early in your WHERE clauses, the optimizer will be more likely to choose the most efficient execution plan.

Unique indexes rank the highest when compared against constants. Non-unique, next.

Oracle evaluates non-indexed equations, linked by AND, in a bottom-up fashion.
Always try to position the expensive AND clause first in the WHERE clause sequencing.

Use ROWID when possible. (the single fastest method of record retrieval)

Try not to use != (or <>) -- inequalities generally require full-table scans.
ie.
select * from A, B where A.field != B.field;

Use something like this instead:
select * from A, B where A.field = B.field(+)

AND B.field is null;

Use hints whenever possible

select /*+ INDEX(A, indexed_or_primary_key_on_A) */ A.* from A, B
where a.field = b.field;

In general, form the WHERE clause like this (based on your Table A, B, etc) select ...
from A, B, C, D, E
where E.field = D.field

    and D.field = C.field
    and C.field = B.field
    and B.field = A.field;        (<=== "most important" join listed last in

the where clause)

<kshave_at_health.gov.mb.ca> wrote in message news:7uppo9$sup$1_at_nnrp1.deja.com...
> Let's say I have a sql query that looks at 5 tables ...
>
> Table A - 100 rows
> Table B - 200 rows
> Table C - 300 rows
> Table D - 150 rows
> Table E - 1000000 rows
>
> Assuming that it's possible, I always thought that joining the
> smaller tables together first would speed the query. Once Table A-D are
> joined, that entire result set is then joined with Table E. Only one
> large join is performed.
>
> However, after reading the Oracle 7 Server SQL Reference Manual, page
> 4-21 states that "the optimizer determines the order in which Oracle7
> joins tables based on the join conditions, indexes on the tables, and,
> in the case of the cost-based optimization approach, statistics for the
> tables". So, I guess this means that the order that you list the tables
> in the where clause doesn't matter (btw I'm using the CBO).
>
> Does anybody have any insight regarding this topic?
>
> --
>
> -Keith
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 22 1999 - 13:44:41 CDT

Re: speed of query - help