Re:
Hi Shadid,
You need an index on payprmbcalc.paymonth.
Later,
Mark
At 05:58 PM 1/29/01 +0500, shahid.mehmood wrote:
OS: SCO UNIX 5.0, IX: OWS 7.20.UC2
I have a table named payprmbcalc which also has index on its three fields. This
table contains 28000+ records. when I run a query on this table with the db
engine searches the table using sequential scan, where as I am expecting search
using an index. this query is taking too long to complete. can you guys suggest
any thing how to improve this query ...
The table schema and 'set explain on' output is attached here with.
The Table Schema ...
{ TABLE "paydba".payprmbcalc row size = 58 number of columns = 9 index size =
36 }
create table "paydba".payprmbcalc
(
paymonth integer not null constraint "paydba".n196_305,
internalno char(6) not null constraint "paydba".n196_306,
currency char(10) not null constraint "paydba".n196_307,
balance decimal(10,2),
product decimal(10,2),
taxprofit decimal(10,2),
ntaxprofit decimal(10,2),
lastedit date,
lastuser char(10)
);
create unique index "paydba".pp001idx on "paydba".payprmbcalc
(paymonth,internalno, currency);
the 'set explain output' ...
QUERY:
------
select employee.employeeno , payprmbcalc.internalno,
employee.fname , employee.mname , employee.lname ,
payprmbcalc.currency , payprmbcalc.balance ,
employee.appointment , employee.termination ,
payprmbcalc.product , payprmbcalc.taxprofit ,
payprmbcalc.ntaxprofit
from payprmbcalc, employee
where payprmbcalc.paymonth = 2000130000
and employee.internalno = payprmbcalc.internalno
order by 1
Estimated Cost: 7
Estimated # of Rows Returned: 5
Temporary Files Required For: Order By
1) paydba.payprmbcalc: SEQUENTIAL SCAN { why ? i need index scan }
Filters: paydba.payprmbcalc.paymonth = 2000130000
2) paydba.employee: INDEX PATH
(1) Index Keys: internalno
Lower Index Filter: paydba.employee.internalno =
paydba.payprmbcalc.internalno
an early reply will be highly appreciated.
thanks.
Shahid Mehmood
Software Designer & IX DBA
Information Systems Department
The Aga Khan University,
Karachi
Official: Yes