Flat view performance improvements

[obruchez]

Following #1069, we should investigate how performances with flat views, especially flat views with repeated sections/grids, can be improved.

Generic XML indexes sound like a good start. Materialized views could be another way to improve performances, but we should check that standard views can be adapted to materialized views without additional constraints.

Materialized views

• Oracle: automatic updates supported (ON COMMIT)
• PostgreSQL: automatic updates supported (REFRESH CONCURRENTLY)
• DB2: manual updates only? can be used with triggers, etc.? (REFRESH TABLE)
• SQL Server: automatic updates supported ("automatically gets updated as data changes in the underlying tables")
• MySQL: not supported

Generic XML indexes

• Oracle: XMLIndex ("Oracle recommends that you use the structured component of XMLIndex instead [of function-based indexes]")
• PostgreSQL: not supported
• DB2: XML indexes
• SQL Server: primary XML indexes
• MySQL: not supported

Functional indexes

For databases that don't support generic XML indexes (PostgreSQL and MySQL), function indexes could be used, although they would probably not help for repeated sections/grids.

• Oracle: supported (function-based indexes)
• PostgreSQL: supported (functional indexes) (e.g. CREATE INDEX test_idx ON test USING BTREE (cast(xpath('/book/title', xml_data) as text[])))
• DB2: supported (expression-based indexes)
• SQL Server: not really supported? (indexed computed columns)
• MySQL: supported (functional indexes) (do they work well with extractvalue(xpath)?)