What SQL query shows me the tables and indexes used by the Informix view?

The table sysdepend

displays document dependencies. Columns:

• btabid - base table id
• btype - usually T for table or V for view
• dtabid - dependent table id
• dtype - usually T for table or V for view

Therefore, for a given view with tabid N, you can write:

SELECT b.owner, b.tabname, d.*
    FROM "informix".systables b, "informix".sysdepend d
    WHERE d.dtabid = N
      AND d.btabid = b.tabid;

      

        
        
        
      

    

If you only know the name of the view, then defining a taboo view is surprisingly tricky if your database is an ANSI MODE database where you can have multiple tables with the same table name (or in this case the view name), but each with a different owner ... However, in the usual case (non-ANSI database or unique table / view name) the query is quite simple:

SELECT b.owner, b.tabname, d.*
    FROM "informix".systables b, "informix".sysdepend d
    WHERE d.dtabid = (SELECT v.tabid FROM "informix".systables v
                         WHERE v.tabname = "viewname"
                     )
      AND d.btabid = b.tabid;

      

        
        
        
      

    

The question asks about the indexes used by the view. Indexes are not used by the look itself; indexes are used by the query engine when processing a query, but the indexes used can vary depending on the overall query - therefore, different indexes can be used for the two queries:

SELECT * FROM SomeView;

SELECT * FROM SomeView
    WHERE Column1 BETWEEN 12 AND 314;

      

        
        
        
      

    

The indexes to be used are not written anywhere in the system directory; they are dynamically overridden when the statement is prepared.

The question also notes:

Reference Information. I need to make sure that some critical views point to current (eg after "refactored") tables. Too often I've seen views pointing to old backup tables that were no longer indexed and required a query forever.

How do you do your reorganization? Do you create a new table with the required structure, copy data from old to new, then rename old, rename new? This will probably be an explanation - renaming a table will re-arrange the views that reference the table. What form of reorganization are you doing? Can you use a different technique? The classic standby is to use the ALTER INDEX on CLUSTER (after changing it to NOT CLUSTER if it has already been clustered). This will restore the table and indexes - without breaking the views. Alternatively, you might want to consider the ALTER FRAGMENT operation.

It's also a bit weird to keep old tables. This suggests that your reorganization is more of a matter of discarding old data. Perhaps you should fragment the table by date ranges so that you split the fragment when it reaches its end-of-life date. Dropping tables will also result in the loss of the views that depend on it, allowing you to rebuild the views with new table names.

Another alternative is simply to ensure that the reorganization shrinks and recreates the views.

I need to regularly identify these queries and "remind" the tuning DBA to rebuild the views / indexes.

Worrisome ... it should just be part of the standard procedure to complete the reorganization. Basically, you have a bug for reporting in this procedure - it doesn't guarantee that the views are fully functional.