Re: slow query
On Wed, 29 Nov 2006 03:55:19 -0800 (PST), Floyd Wellershaus
<fwellers@yahoo.com> wrote:
>Is there anything glaring that I'm missing here ? The below sqexplain output describes a query that is running slow.
>I've attached the ddl for the tables involved also.
>
Ah, but you forgot one . .. . . scprofil.
>Thanks in advance for any insight.
>
>SELECT sid.ssn,
> sid.student_id,
> sid.first_name_s,
> sid.middle_name_s,
> sid.last_name_s,
> sid.name_suffix,
> sid.prev_last_name_s,
> sid.prev_first_name_s,
> sid.birth_dt,
> (SELECT schl_branch FROM scprofil where scprofil.token = sid.scprofil_token) branch, dtl.token
How fast does the query run without the SELECT listed above? I've
seen where the explain plan doesn't include this query as part of the
explain plan. Is there an index on scprofil.token? Can you verify
that the index (if any) is being used?
>FROM degreedtl dtl,
> degreesid sid
>WHERE sid.token = dtl.degreesid_token
>AND sid.first_name_s = 'test'
>AND sid.last_name_s = 'test'
>AND sid.scprofil_token IN((1383))
>AND dtl.rec_type = 'D'
>AND dtl.rec_status = 'A'
>
>
>Estimated Cost: 24
>Estimated # of Rows Returned: 1
>
> 1) stevet.sid: INDEX PATH
>
> Filters: (stevet.sid.scprofil_token = 1383 AND stevet.sid.first_name_s = 'test' )
>
> (1) Index Keys: last_name_s birth_dt
> Lower Index Filter: stevet.sid.last_name_s = 'test'
>
> 2) stevet.dtl: INDEX PATH
>
> Filters: (stevet.dtl.rec_status = 'A' AND stevet.dtl.rec_type = 'D' )
>
> (1) Index Keys: degreesid_token
> Lower Index Filter: stevet.sid.token = stevet.dtl.degreesid_token
>NESTED LOOP JOIN
>
See there, table scprofil isn't listed here. Explain plans don't
include any sub-selects within the SELECT columns.
JWC