Improving performance by creating indexes on a DGTT

If we are processing a large amount of data from a temporary table, then we can use indexes to get performance benefits. We can create indexes on regular columns as well as on XML columns. The indexes will also be stored in the same table space in which the temporary table is defined.

How to do it...

Index creation on a DGTT is very similar to regular tables. Let's see how to do that:

1. 1. Create a declared global temporary table:

   DECLARE GLOBAL TEMPORARY TABLE sample_xml (empno INT,
   sal_rise_date DATE,
   sal_dtls XML)
   ON COMMIT DELETE ROWS
   NOT LOGGED
   IN user_temp_tbsp;
   
   

2. 2. Create an index on a relational column: Just like a regular table, use the CREATE INDEX statement to create an index on a temporary table. The following command creates an index on a relational column of a temporary table:

   CREATE INDEX SESSION.TEMP_REL_IDX ON SESSION.SAMPLE_XML (EMPNO);
   
   

3. 3. Create an index on an XML column: We can also create indexes on XML columns. To create...