help with query plan
Below is the sqexplain output for a query, and below
that is the schema for the table in question.
I don't understand why this is doing a sequential scan on the scprofil table.
It is not doing this on the development box, that has the exact same schema.
I ran the required update statistics on scprofil, and it still does the
sequential scan and uses the auto index path.
I am thinking that if I put an index on scp.state, then it won't, but I still
would like to understand why this is happening, plus I want to be sure about
the index, before I lock up the production table by creating the index.
Any ideas would be appreciated.
Thanks
QUERY:
------
SELECT sen_scprofil_token, sen_schl_code, sen_schl_branch,
sen_schl_name, scp.state, count(*) num_recs FROM T11752 sub,scprofil scp WHERE output_flag = 'Y' AND rejectflag = 'N' AND
sen_schl_code = scp.schl_code AND sen_schl_branch = scp.schl_branch
AND sub.token = src_token GROUP BY sen_scprofil_token, sen_schl_code,
sen_schl_branch, sen_schl_name,scp.state ORDER BY num_recs DESC,
scp.state ASC
Estimated Cost: 551194
Estimated # of Rows Returned: 1
Maximum Threads: 5
Temporary Files Required For: Order By Group By
1) root.scp: SEQUENTIAL SCAN
2) root.sub: AUTOINDEX PATH
Filters:
Table Scan Filters: ((root.sub.token = root.sub.src_token AND
root.sub.output_flag = 'Y' ) AND root
.sub.rejectflag = 'N' )
(1) Index Keys: sen_schl_code sen_schl_branch
Lower Index Filter: (root.sub.sen_schl_code = root.scp.schl_code AND
root.sub.sen_schl_branch = roo
t.scp.schl_branch )
NESTED LOOP JOIN
sql for table scprofile is:
{ TABLE "sentrycf".scprofil row size = 897 number of columns = 53 index size =
83
}
create table "sentrycf".scprofil
(
token integer not null ,
name char(50) not null ,
short_name char(15),
schl_code char(6) not null ,
schl_branch char(2) not null ,
type char(1) not null ,
college_type char(1),
tin_school_flag char(1)
default 'N' not null ,
alt_ssn_range varchar(50)
default null,
sch_city varchar(20),
state char(2),
member_status char(1) not null ,
closed_flag char(1)
default 'N' not null ,
member_start_dt date,
data_since_dt date,
stdt_population integer,
num_enrollees integer not null ,
software_used char(15) not null ,
tt_participant char(1) not null ,
tt_mbprofil_token integer,
schl_block_rpt char(1) not null ,
oedo_block_rpt char(1) not null ,
block_email char(1) not null ,
dbi_programmed char(1) not null ,
dbi_percent_allow integer,
priority_flag char(1) not null ,
perkins_flat_rate char(1) not null ,
contract_type varchar(15),
es_paid_through_dt date,
es_ntf_tape_spec varchar(150),
commercial_verify char(1) not null ,
enrollstat_release char(1),
address_release char(1),
sss_participant char(1)
default 'N' not null ,
sss_enable_to_only char(1)
default 'N' not null ,
sss_ec_agd char(1)
default 'N' not null ,
sss_ec_notes_token integer,
to_participant char(1)
default 'N' not null ,
to_active_dt date,
ev_trans_fee money(16,2)
default 0.00 not null ,
ev_block_public char(1)
default 'N' not null ,
dv_participant char(1),
dv_scprofil_token integer,
dv_gen_grad_file char(1)
default 'N' not null ,
dv_data_since_dt date,
dv_trans_fee money(16,2)
default 0.00 not null ,
dv_active_dt date,
dv_refers_requests char(1) not null ,
dv_block_autoemail char(1)
default 'N' not null ,
cv_contact_phrase varchar(200),
standing_instr varchar(255),
operator_id char(9) not null ,
timestamp datetime year to second not null
) in schl_dat extent size 1536 next size 256 lock mode page;
revoke all on "sentrycf".scprofil from "public";
create unique index "sentrycf".scprofil_x01 on "sentrycf".scprofil
(token) using btree in table ;
create unique index "sentrycf".scprofil_x02 on "sentrycf".scprofil
(schl_code,schl_branch) using btree in table ;
create index "sentrycf".scprofil_x03 on "sentrycf".scprofil (name)
using btree in table ;
create index "sentrycf".scprofil_x04 on "sentrycf".scprofil (member_status)
using btree in table ;
Darren,
Pretty weird. I think the difference in the queries is because the development
box has much less rows in the t table, and made a different plan, accordingly.
I used optimizer directives, to force the same index use on production, so
that both sqexplain output files looked identical, and it ran even slower.
So I guess it did pick the best plan.
Thanks.
Darren_Jacobs@carmax.com wrote:
Have you thought about adding both token and state to the scprofil_x02
index. This would create a key only for the table I believe. You could
also create the index with state asc which would help with your order by.
"Floyd Welle...."
om> To
Sent by: ids@iiug.org
forum.subscriber@ cc
iiug.org
Subject
help with query plan [4940]
05/13/2005 07:48
AM
Below is the sqexplain output for a query, and below that is the schema for
the table in question.
I don't understand why this is doing a sequential scan on the scprofil
table. It is not doing this on the development box, that has the exact same
schema.
I ran the required update statistics on scprofil, and it still does the
sequential scan and uses the auto index path.
I am thinking that if I put an index on scp.state, then it won't, but I
still would like to understand why this is happening, plus I want to be
sure about the index, before I lock up the production table by creating the
index.
Any ideas would be appreciated.
Thanks
QUERY:
------
SELECT sen_scprofil_token, sen_schl_code, sen_schl_branch,
sen_schl_name, scp.state, count(*) num_recs FROM T11752 sub,scprofil scp WHERE output_flag = 'Y' AND rejectflag = 'N' AND
sen_schl_code = scp.schl_code AND sen_schl_branch = scp.schl_branch
AND sub.token = src_token GROUP BY sen_scprofil_token, sen_schl_code,
sen_schl_branch, sen_schl_name,scp.state ORDER BY num_recs
DESC,
scp.state ASC
Estimated Cost: 551194
Estimated # of Rows Returned: 1
Maximum Threads: 5
Temporary Files Required For: Order By Group By
1) root.scp: SEQUENTIAL SCAN
2) root.sub: AUTOINDEX PATH
Filters:
Table Scan Filters: ((root.sub.token = root.sub.src_token AND
root.sub.output_flag = 'Y' ) AND root
.sub.rejectflag = 'N' )
(1) Index Keys: sen_schl_code sen_schl_branch
Lower Index Filter: (root.sub.sen_schl_code = root.scp.schl_code
AND root.sub.sen_schl_branch = roo
t.scp.schl_branch )
NESTED LOOP JOIN
sql for table scprofile is:
{ TABLE "sentrycf".scprofil row size = 897 number of columns = 53 index
size = 83
}
create table "sentrycf".scprofil
(
token integer not null ,
name char(50) not null ,
short_name char(15),
schl_code char(6) not null ,
schl_branch char(2) not null ,
type char(1) not null ,
college_type char(1),
tin_school_flag char(1)
default 'N' not null ,
alt_ssn_range varchar(50)
default null,
sch_city varchar(20),
state char(2),
member_status char(1) not null ,
closed_flag char(1)
default 'N' not null ,
member_start_dt date,
data_since_dt date,
stdt_population integer,
num_enrollees integer not null ,
software_used char(15) not null ,
tt_participant char(1) not null ,
tt_mbprofil_token integer,
schl_block_rpt char(1) not null ,
oedo_block_rpt char(1) not null ,
block_email char(1) not null ,
dbi_programmed char(1) not null ,
dbi_percent_allow integer,
priority_flag char(1) not null ,
perkins_flat_rate char(1) not null ,
contract_type varchar(15),
es_paid_through_dt date,
es_ntf_tape_spec varchar(150),
commercial_verify char(1) not null ,
enrollstat_release char(1),
address_release char(1),
sss_participant char(1)
default 'N' not null ,
sss_enable_to_only char(1)
default 'N' not null ,
sss_ec_agd char(1)
default 'N' not null ,
sss_ec_notes_token integer,
to_participant char(1)
default 'N' not null ,
to_active_dt date,
ev_trans_fee money(16,2)
default 0.00 not null ,
ev_block_public char(1)
default 'N' not null ,
dv_participant char(1),
dv_scprofil_token integer,
dv_gen_grad_file char(1)
default 'N' not null ,
dv_data_since_dt date,
dv_trans_fee money(16,2)
default 0.00 not null ,
dv_active_dt date,
dv_refers_requests char(1) not null ,
dv_block_autoemail char(1)
default 'N' not null ,
cv_contact_phrase varchar(200),
standing_instr varchar(255),
operator_id char(9) not null ,
timestamp datetime year to second not null
) in schl_dat extent size 1536 next size 256 lock mode page;
revoke all on "sentrycf".scprofil from "public";
create unique index "sentrycf".scprofil_x01 on "sentrycf".scprofil
(token) using btree in table ;
create unique index "sentrycf".scprofil_x02 on "sentrycf".scprofil
(schl_code,schl_branch) using btree in table ;
create index "sentrycf".scprofil_x03 on "sentrycf".scprofil (name)
using btree in table ;
create index "sentrycf".scprofil_x04 on "sentrycf".scprofil (member_status)
using btree in table ;
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================