Re: View performance in IDS - suggestions?

What ?? That is not supposed to happen isn't it ? Or is the optimizer less suited to do its work with ansi syntax ? J. Matt Penning escribi': > Thanks, that seems to do the trick. > > - Matt > > Richard Harnden wrote: > >>Matt Penning wrote: >> >> >>>Hi all, >>> >>>I'm having performance problems with views in IDS 10 (RC3,Linux). >>> >>>Creating a simple view with one join and then querying against it >>>results in not only a temporary table being created, but a temporary >>>table with no indexes on it. The temp table creation followed by >>>sequential scan is obviously very expensive. >>> >>>Is there anyway to provide hints in the view query or anything else >>>that can be done to make a view more performant? >>> >>>This is the test I did with the stores_demo database, and the results: >>> >>>Test >>>-------------------------- >>>set explain on; >>>create view vw_orderfname as >>>select order_num, fname from orders >>> left join customer on orders.customer_num=customer.customer_num;>> >> >>I'd try re-writing the view: >> >>create view vw_orderfname as >> select order_num, fname >> from orders, customer >> where orders.customer_num=customer.customer_num;>> >>Don't know if it'll make any difference, but the optimizer usually seems >>to be happier with the informix from syntax as opposed to the ansi syntax. >> >> >>> select * from vw_orderfname where order_num=1008;>>> >>> >>>Results >>>-------------------------- >>>Estimated Cost: 19 >>>Estimated # of Rows Returned: 1 >>> >>> 1) (Temp Table For View): SEQUENTIAL SCAN >>> >>> >>>- Matt > >