Re:RE: FW: Sequential scan

This is a multi-part message in MIME format... ------------=_981032461-8913-0 Content-Type: text/plain Content-Disposition: inline Shahid, the command is not using the directive, I think it's because you must set your database to accept the use of them. You can do that by setting the parameter DIRECTIVES to 1 in your onconfig file to 1, or you can set the environment variable IFX_DIRECTIVES=ON which has the same effect. Your onconfig variable OPTCOMPIND must be set to 2 as well. This will make the optimizer to choose the best query path from all possible paths. # Optimizer DIRECTIVES ON (1/Default) or OFF (0) DIRECTIVES 1 You can see if your directives are being followed in the sqexplain.out file, as the example above (note the DIRECTIVES FOLLOWED clause): QUERY: ------ select {+ORDERED} * from cptipodoc,cpdocumento where cpdocumento.id_tipodoc = cptipodoc.id_tipodoc DIRECTIVES FOLLOWED:ORDERED DIRECTIVES NOT FOLLOWED: Estimated Cost: 6 Estimated # of Rows Returned: 10 1) informix.cptipodoc: SEQUENTIAL SCAN 2) informix.cpdocumento: INDEX PATH (1) Index Keys: id_tipodoc Lower Index Filter: informix.cpdocumento.id_tipodoc = informix.cptipodoc .id_tipodoc NESTED LOOP JOIN Let's see if this time it works !!!! Regards Paulo >>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 = 2000130000 > and employee.internalno = 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 = 2000130000 > >2) paydba.employee: INDEX PATH > > (1) Index Keys: internalno > Lower Index Filter: paydba.employee.internalno = >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's 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's 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 = 2000130000 > and employee.internalno = 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 = 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