Ability to create indexes on temporary tablesAnswered
I'd like to request a new feature that allows creating indexes on temporary tables. There are times when data needs to be cached into a temp table within a procedure and being able to apply indexes on them provide for more options in tuning performance.
Un Sang Oum
Comment actionsOfficial comment
Hello Un Sang,
thank you for your request. This feature is already supported by Data Virtuality: it is possible to define primary key on the temporary table during its creation, with one or several columns.
For the primary key, also a clustered index will be created on the temporary table, which will speed up the operations on this table.
For more details, please see DV documentation at:
Please note, that creating more than one index is not supported, and would not be easy to implement due to the internal implementation of temp tables. However, you can create more than one temp table with different indexes, or you can also create a temp table in your analytical storage. In the analytical storage you can also create several indexes on a table, if the underlying database supports it, by using either SYSADMIN.createIndex or dwh.native() calls.
I hope this information helps.
Please sign in to leave a comment.