I am running Informix 10 and have a question about indexes on view. I have a table that has an associated view. The view is basically an exact copy of the table, column for column, except that one column calls an external function to return a "Clear form of an encrypted value (the column in the table is encrypted and the the column in the view appears "in the clear"). Since the table and view are pretty much identical (the encrypted column is not indexed) do I need to create indexes on the view or will the database know to utilize the indexes on the table? Would I "buy" anything by creating separate indexes in the view? FYI - the table contains approx 32 million rows, rows length of 370 characters, and there are currently 12 indexes on the table.
Due to the fact that views in Informix are not materialized, it is not possible to put indexes on views. Informix will always just rewrite the query to execute the query on the real tables. Of course it will re-use any existing indexes on the underlying table(s).