Re: Views and Informix

>I was wondering if someone might know how Informix implements VIEWS. In a >discussion with our Informix Systems Engineer, he indicated that when a >query is executed through a view, the engine first creates and populates a >temporary table with a subquery constructed from the viewdefinition in >sysviews. The outer query is then executed against the temporary table. He >then went on to say that the temporary table can be reused if the view >creation and population was not part of a transaction and the session that >created and populated the view is still open. > >Problem is that we wanted to test this out. So we have a test database with >no one on it. First we note the free space in the temp dbspace, create a >view, note the free space again, then execute the query through the view, >and then note the space a final time. Strangely enough the free space >doesn't change. > >I am wondering if temp tables are indeed created, if they are small enough >does the engine create them in memory? > >If anyone is familiar with the inner workings of Informix's views, I would >love some insight. I'm more familar with 7.x than 5.x so will answer this accordingly. When a query is made against a view, before the query is optimized, we expand/translate the query by including the definition of the view. Then the view projection is reduced by eliminating non-essential items from the query. Finally this reduced query is passed to the optimizer. Temporary tables will not be created unless they are deemed necessary by the optimizer. In effect the query becomes simply the expeanded view. The removal of items from the expanded query would include items from the view's select clause that are not actually selected as part of the query. This does not eliminate items which are essential to the view's definition, such as items in the where clause, etc. We try very hard to make a selection against a view to be as efficient as a selection against the underlying tables. Madison Pruet