Re: View performance in IDS - suggestions?

You got it - seems that the optimizer doesn't grok ansi syntax. You would think that each query would boil down to the same type of internal structure and *then* the optimizer would do it's magic, but apparently that's not the way it works. - Matt Jean Sagi wrote: > 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 >> >> >>