Re: sql optimization (Orac. 6)

I've just begun to look at explain plan as a tool to help me speed up some cumbersome joins and am having difficulty interpreting the output.

Given the two tables:     names         educat.
                          -----         -------
			id char          id char
                       company_id char   degree char
                       status  char      discipline char

All fields are individually indexed, the id being unique in names. educat has around twice the amount of records as names and a many to 1 correspondence (educat to names).

When querying :
select names.id from names, educat
where names.id = educat.id
and names.company = '1'
and names.status = '1'
and educat.degree = 'BACH'
and educat.discipline = 'COMP';

The query runs MUCH faster than if I switch the from clause to "educat, names" and presumably drive the join by names. The difference on my system was 32 seconds for the former and over 1000 for the latter. The explain plan output on the "from educat, names" query (the cumbersome one) is as follows:

1.0 Nested Loops
  2.1 Table Access By ROWID NAMES
    3.1 and-equal

     4.1  Index Range Scan names_company non-unique
     4.2  Index Range Scan names_status non-unique
  2.2 and-equal
     3.1  Index Range Scan educat_id non-unique
     3.2  Index Range Scan educat_degree non-unique
     3.3  Index Range Scan educat_discipline non-unique

When I drove the join by educat, with the from clause "from names, educat" the explain plan output was:

1.0 Nested Loops
  2.1 Table Access By ROWID educat
    3.1 and-equal

     4.1  Index Range Scan educat.degree non-unique
     4.2  Index Range Scan educat.discipline non-unique
  2.2 Table Access By ROWID names
     3.1  Index UNIQUE Scan names_id unique

Can someone explain the rhyme and reason here? Please email me, If there are requests for the responses I will post them.

Thank you.

Robert Karen
rkaren\_at_cbnewsl.att.com Received on Fri Sep 03 1993 - 19:54:41 CEST