RE: Sequential scan

Posted in 2001

shahid.mehmood — Tue, 30 Jan 2001 13:27:27 +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_01C08A96.7AAEAFD0 Content-Type: text/plain; charset="iso-8859-1" i am repeating the schema here again ... 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); we need this index to force the unique occurance of the three columns when combined! we could have used primary key, but it is a long story. we have to live with this stand-alone index. as your teck support suggest, if we drop this index, how can we force uniqueness required? Regards, Shahid Mehmood Software Designer Information Systems Department The Aga Khan University Karachi Pakistan Official: Yes -----Original Message----- From: Dirk Moolman [mailto:dirkm@reach.co.za] Sent: Tuesday, January 30, 2001 11:54 AM To: informix-list Subject: FW: Sequential scan Is an index on paymonth necessary ? We had technical support out here at our site, and they requested that I drop all indexes on my system where the column of a stand-alone index is already the leading column in another composite index (for the same table of course). They said that we had alot of duplication where indexes were concerned and by duplicating the optimiser had more work to do, which could affect our performance. Any comments ? Dirk Reach Technologies -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Mark Griebling Sent: Monday, January 29, 2001 5:41 PM To: shahid.mehmood; informix-list@iiug. org (E-mail) Subject: 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 ------_=_NextPart_001_01C08A96.7AAEAFD0 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>RE: Sequential scan</TITLE> </HEAD> <BODY> i am repeating the schema here again ... 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); we need this index to force the unique occurance of = the three columns when combined! we could have used primary key, but it = is a long story. we have to live with this stand-alone = index. as your teck support suggest, if we drop this index, = how can we force uniqueness required? Regards, Shahid Mehmood Software Designer Information Systems Department The Aga Khan University Karachi Pakistan Official: Yes -----Origina

RE: Sequential scan

Related threads

Your privacy choices