Re: Views and Informix

Bryan Hughes wrote: > 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. Bryan, I don't know where this Informix Systems Engineer gets his drugs from but whether a temp table is created to support a view or not is dependent on several factors including the isolation level, value of OPTCOMPIND, PDQPRIORITY, table fragmentation, the query itself, whether an ORDER BY is specified, whether the statement was prepared with replaceable parameters, etc. Run your queries against the View under SET EXPLAIN ON and view the output to see if temp tables are beingused. I just did that on a query against a 3 table view with a filter clause on one of the view's columns. The sqexplain.out file shows no temp tables and the filter condition was folded into the lower index filter for one of the three tables comprising the view. I also looked in the view syssqexplain for the session and the record for the query above shows zero for sqx_tempfile and sqx_tempview, the same query without a where clause shows -1 for these columns but that query with an ORDER BY on an unindexed column shows positive 1 for sql_tempfile and the sqexplain.out shows "Temporary Files Required For: Order By" for that last statement. All queries run with OPTCOMPIND=0, PDQPRIORITY=0, default isolation on an unbuffered logged database in IDS 7.21UD3. The Informix optimizer uses temp tables when it finds a need for them and does not when it does not need to. Even if such a temp table were created, dynamic temp tables are NEVER reused by the engine because the underlying data may have been modified invalidating the contents of the temp table. Such dynamic temp tables are destroyed immediately after the queries cursor (explicit or implied) is closed. The only time the engine will always create a temp table, and this is true even for queries that do not reference views, is under ISOLATION CURSOR STABILITY because while the cursor is open the cursor must get a consistent, point in time, view of the data regardless of any modifications being made at the time. In this case a lock is acquired on the source data until the temp table is fully populated and then released. Even here though the temp table is destroyed immediately after the cursor is closed and repeating the query will show the data including any interim modifications. Similar for CURSOR FOR SCROLL, except that no locks are acquired while the temp table is built and again it is immediately destroyed when the SCROLL CURSOR is closed. Drugs, definitely drugs. Art S. Kagel