RE: FW: Sequential scan

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_01C08C13.9BD37E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable no the directivs does not help either ... here is the output from sqexplain.out. QUERY: ------ select --+INDEX (payprmbcalc pp001idx) 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: 3 Temporary Files Required For: Order By 1) paydba.payprmbcalc: SEQUENTIAL 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 The query is returning the results very fast (even without this = --+INDEX directive), I'm just wondering why it is not considering the index! Thanks to all for all the help. Shahid Mehmood Software Designer & IX DBA Information Systems Department The Aga Khan University Karachi Pakistan Official: Yes -----Original Message----- From: Paulo Amorim [mailto:pauloamorim@uol.com.br] Sent: Thursday, February 01, 2001 00:54 AM To: informix-list@iiug.org Subject: RE: FW: Sequential scan The optimizer=B4s decision is based on information from all tables envolved in the query. It seems to me that the performance is not so bad, as we can see in the query cost. Maybe it=B4s easier to read the table sequentially, as you are = searching for a plain date. Try to use directives to force the optimizer to use the index you want, then you can compare the performance. Your command should be written like this: select --+INDEX (payprmbcalc pp001idx) 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 Hope this helps. -- Paulo Roberto Marelli de Amorim TS&0 Consulting Brasil In article <959mk8$c26$1@news.xmission.com>, "Parker, Jack" <JParker@engage.com> wrote: > > > Cost looks WAY low - I would suspect that statistics have not been updated. > Or that you only have five rows in the table. Sorry if I missed something > mentioned in other posts - I'm catching up here. > > cheers > j. > > > -----Original Message----- > > From: Mark Griebling [mailto:mgriebling@webswift.com] > > Sent: Tuesday, January 30, 2001 5:57 AM > > To: Dirk Moolman; informix-list > > Subject: Re: FW: Sequential scan > > > > > > Hi Dirk, > > > > It was just a suggestion. Just try it and if it doesn't work > > - drop the > > index. In my experience the Optimizer is not always so > > bright. If it works > > and does the job - what's the harm? > > > > Later, > > > > Mark > > > > > > At 08:54 AM 1/30/01 +0200, Dirk Moolman wrote: > > 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 =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.in

Hi, I didn't follow the original article, but if you enter optimizer directives, you will see the information in the sqexplain.out file, whether the optimizer followed the directives or not. Something like: DIRECTIVES FOLLOWED: DIRECTICES NOT FOLLOWED: INDEX(...) Therefore I think you either use a version < 7.3 or you forgot to set the DIRECTIVE parameter in your configuration file. Have a look at the file "onconfig.std" in $INFORMIXDIR/etc and check at the end of the file the possible settings for that parameter. Compare these settings against your current $ONCONFIG file. Best regards, Stefan shahid.mehmood 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. > > > no the directivs does not help either ... here is the output from > sqexplain.out. > > QUERY: > ------ > select --+INDEX (payprmbcalc pp001idx) > 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: 3 > Temporary Files Required For: Order By > > 1) paydba.payprmbcalc: SEQUENTIAL 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 > > The query is returning the results very fast (even without this = > --+INDEX > directive), I'm just wondering why it is not considering the index! > > Thanks to all for all the help. > -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----