Re: speed of query - help

Hi.

 It's difficult to give advice without much data.  CBO is supposed to make a join in best possible order  all by himself, but in a lot of cases it's not so.  Post EXPLAIN - if possible.

 Michael.

In article <7uppo9$sup$1_at_nnrp1.deja.com>,   kshave_at_health.gov.mb.ca wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 23 1999 - 02:32:00 CDT