Re: Poor view performance

Posted in 2006

Ben — Thu, 25 May 2006 07:57:40 -0400 — source: Usenet: comp.databases.informix

Can you post the view definition and the output from 'set explain on'. Be sure to post the exact view definition - newlines and all. Ben ----- Original Message ----- From: "Paul Watson" <paul@iiug.org> To: <mosserp@wellsfargo.com>; <informix-list@iiug.org> Cc: <paul@oninit.com> Sent: Wednesday, May 24, 2006 3:13 PM Subject: 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. >> <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><> >> <><><><><> > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list