Posting from the Informix-list

Posted in 2001

Topics: Performance & Tuning, Server Administration

shahid.mehmood — Mon, 29 Jan 2001 17:58:06 +0500 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C089F3.1F97E380 Content-Type: text/plain; charset="iso-8859-1" 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 ------_=_NextPart_001_01C089F3.1F97E380 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2652.35"> <TITLE></TITLE> </HEAD> <BODY> 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 =3D = 58 number of columns =3D 9 index size =3D 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 =3D = 2000130000    and employee.internalno  =3D = 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 =3D 2000130000 2) paydba.employee: INDEX PATH     (1) Index Keys: internalno         Lower = Index Filter: paydba.employee.internalno =3D = 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 </BODY> </HTML> ------_=_NextPart_001_01C089F3.1F97E380--

↪ replying to shahid.mehmood

Norman Erickson Lugtu — Mon, 29 Jan 2001 15:16:52 GMT — source: Informix-list mailing list archive (1991-1998)

Have you run update statistics recently? In article <953qg7$9r7$1@news.xmission.com>, "shahid.mehmood" <shahid.mehmood@aku.edu> wrote: > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01C089F3.1F97E380 > Content-Type: text/plain; > charset="iso-8859-1" > > 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 > > ------_=_NextPart_001_01C089F3.1F97E380 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2652.35"> > <TITLE></TITLE> > </HEAD> > <BODY> > > 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 =3D = > 58 number of columns =3D 9 index size =3D 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 =3D = > 2000130000 >    and employee.internalno  =3D = > 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 =3D 2000130000 > > > 2) paydba.employee: INDEX PATH > > >     (1) Index Keys: internalno >         Lower = > Index Filter: paydba.employee.internalno =3D = > paydba.payprmbcalc.internalno > > > an early reply will be highly appreciated. > > >

Posting from the Informix-list

Related threads

Your privacy choices