RE: Poor view performance
It ignores optimizer hints :-) Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 European Conference. Vienna, Austria. 2-6 October 2006 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: mosserp@wellsfargo.com [mailto:mosserp@wellsfargo.com] > Sent: 24 May 2006 12:47 > To: informix-list@iiug.org > Cc: paul@oninit.com > Subject: RE: Poor view performance > > > -----Original Message----- > > From: informix-list-bounces@iiug.org > > [mailto:informix-list-bounces@iiug.org] On Behalf Of Paul Watson > > Sent: Wednesday, May 24, 2006 10:01 AM > > To: informix-list@iiug.org > > Subject: Poor view performance > > > > Solaris 2.8 IDS 10.0.FC4 > > > > Simple view joining two tables (4M and 25M rows). One is the live > > table, the other the archive > > > > As a select/union the query is quick and uses all the > > 'proper' indexes. > > As a view it does a S-scan over the 25M table and I always got bored > > before it completed. > > > > Any ideas ? > > > > [Yes, stats are upto date] > > > > Paul Watson > > Don't know if it's possible, but have you tried using > optimizer hints in > the view definition? > > HTH, > Paul M. > <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><> > <><><><><> > This message may contain confidential and/or privileged information. > If you are not the addressee or authorized to receive this for the > addressee, you must not use, copy, disclose, or take any action based > on this message or any information herein. If you have received this > message in error, please advise the sender immediately by reply e-mail > and delete this message. Thank you for your cooperation. > <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><> > <><><><><>