Ability to create indexes on temporary tables

Answered

Follow

0

Un Sang Oum

• December 20, 2021 23:02

Hello,

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.

Thank you,

Un Sang Oum

• Comment actions Permalink

  Official comment

  

  Nick Golovin

  • December 22, 2021 11:27

  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:
  https://documentation.datavirtuality.com/24/reference-guide/ddl-support/temp-tables

  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. 

  best regards,
  Nick

   

   

   

   

   

   

• 0

  Comment actions Permalink

  

  Un Sang Oum

  • December 22, 2021 15:13

  Hi Nick,

  Thank you for the info.  This helps.

  Un Sang Oum

Please sign in to leave a comment.