Re: slow query
Floyd
UPDATE STATISTICS HIGH for the the leading column of each index might help,
however the 'sid.scprofil_token IN((1383))' clause (or indeed any IN clause)always seems (to me) to be a bit of an issue and will cause the optimiser to
try to use any other index rather than the one containing the target column.
I assume there are usually more than one value here (else why not use
equals), could you use token = ?? OR token = ?? OR .... I find this tends to
perform better.
Keith
On 29/11/06, 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.
>
> 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
>
> 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
>
>
>
>
>
> CREATE TABLE degreesid>
> (
>
> token serial not null,
>
> dvsid_token integer,
>
> dvsublog_token integer not null,
>
> g_token integer not null,
>
> scprofil_token integer not null,
>
> stprofil_token integer,
>
> ssn char(9),
>
> student_id char(15),
>
> first_name varchar(40),
>
> first_name_s varchar(40),
>
> middle_name varchar(40),
>
> middle_name_s varchar(40),
>
> last_name varchar(40) not null,
>
> last_name_s varchar(40) not null,
>
> name_suffix varchar(5),
>
> prev_last_name char(40),
>
> prev_last_name_s char(40),
>
> prev_first_name varchar(40),
>
> prev_first_name_s varchar(40),
>
> birth_dt date,
>
> source_flag char(1) not null,
>
> rec_status char(1) not null,
>
> operator_id varchar(9),
>
> timestamp datetime year to second
>
> ) IN dv_sid EXTENT SIZE 1048572 NEXT SIZE 1048572;
>
>
>
> CREATE UNIQUE INDEX degreesid_x01 ON degreesid(token);>
>
>
> CREATE INDEX degreesid_x02 ON degreesid(ssn)> FILLFACTOR 70;>
> CREATE INDEX degreesid_x03 ON degreesid(last_name, birth_dt)> FILLFACTOR 70;>
> CREATE INDEX degreesid_x04 ON degreesid(last_name_s, birth_dt)> FILLFACTOR 70;>
> CREATE INDEX degreesid_x05 ON> degreesid(stprofil_token) FILLFACTOR 70;
>
> CREATE INDEX degreesid_x06 ON degreesid(dvsid_token);>
>
>
> {
>
> # this index is used when the user wants to query degree for a specific
>
> # school via sentry client. Therefore, first column should be
> scprofil_token.
>
> #
>
> # also used during degree verification.
>
> }
>
> CREATE INDEX degreesid_x07 ON degreesid(scprofil_token,
> prev_last_name_s, first_name_s, birth_dt) FILLFACTOR 70;>
>
>
>
>
> CREATE INDEX degreesid_x08 ON degreesid(student_id)> FILLFACTOR 70;>
>
> { TABLE "sentrycf".degreedtl row size = 199 number of columns = 45 index
> size = 27
> }
> create table "sentrycf".degreedtl
> (
> token serial not null constraint "sentrycf".n857093_1276465,
> degreesid_token integer not null constraint
> "sentrycf".n857093_1276466,
> dvsublog_token integer not null constraint "sentrycf".n857093_1276467,
> g_token integer not null constraint "sentrycf".n857093_1276468,
> degree_level_ind char(1),
> ddd_degt_token integer,
> ddd_scad_token integer,
> ddd_jins_token integer,
> award_dt date,
> award_dt_mmyyyy datetime year to month,
> major_1_token integer,
> major_2_token integer,
> major_3_token integer,
> major_4_token integer,
> minor_1_token integer,
> minor_2_token integer,
> minor_3_token integer,
> minor_4_token integer,
> major_opt_1_token integer,
> major_opt_2_token integer,
> major_con_1_token integer,
> major_con_2_token integer,
> major_con_3_token integer,
> ncescip_major_1 varchar(6),
> ncescip_major_2 varchar(6),
> ncescip_major_3 varchar(6),
> ncescip_major_4 varchar(6),
> ncescip_minor_1 varchar(6),
> ncescip_minor_2 varchar(6),
> ncescip_minor_3 varchar(6),
> ncescip_minor_4 varchar(6),
> ddd_ahnr_token integer,
> ddd_hnrp_token integer,
> ddd_ohnr_token integer,
> attend_from_dt date,
> attend_from_mmyyyy datetime year to month,
> attend_to_dt date,
> attend_to_mmyyyy datetime year to month,
> ferpa_block char(1),
> schl_finance_block char(1),
> schl_aka_token integer not null constraint "sentrycf".n857093_1276469,
> rec_type char(1) not null ,
> rec_status char(1) not null constraint "sentrycf".n857093_1276470,
> operator_id varchar(9),
> timestamp datetime year to second
> ) in dv_dtl extent size 1048572 next size 1048572 lock mode page;
> revoke all on "sentrycf".degreedtl from "public";>
> create index "sentrycf".degreedtl_x03 on "sentrycf".degreedtl
> (dvsublog_token) using btree in table ;
> create unique index "sentrycf".degreedtl_x1 on "sentrycf".degreedtl
> (token) using btree in table ;
> create index "sentrycf".degreedtl_x2 on "sentrycf".degreedtl
> (degreesid_token)
> using btree in table ;
>
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix