Ian_Hill

CA Tuesday Tip: (CA IDMS) SQL Default Index

Discussion created by Ian_Hill Employee on Nov 27, 2012
CA IDMS Tuesday Tip by Ian Hill, Principal Support Engineer for November 27, 2012.

When you create an SQL table, a "default index" is automatically created on the rowid (dbkey) of the table rows. The purpose of this index is to ensure that the table can at least be swept efficiently in case no other indexes are defined on it. If you do create at least one other user-defined index on the table, you can delete the default index. To see the impact, run the following through OCF:

CREATE SCHEMA SQL1 DEFAULT AREA PROJSEG.PROJAREA;
CREATE TABLE SQL1.T1 ( C1 SMALLINT NOT NULL );
DISPLAY TABLE SQL1.T1;
SELECT NAME, SCHEMA, TABLE FROM SYSTEM.INDEX WHERE SCHEMA='SQL1';
ALTER TABLE SQL1.T1 DROP DEFAULT INDEX;
DISPLAY TABLE SQL1.T1;
SELECT NAME, SCHEMA, TABLE FROM SYSTEM.INDEX WHERE SCHEMA='SQL1';

The output is as follows. Note the DISPLAY and SELECT before and after the ALTER TABLE, which reflect the presence and absence of the default index.

CREATE SCHEMA SQL1 DEFAULT AREA PROJSEG.PROJAREA;
*+ Status = 0        SQLSTATE = 00000                               
CREATE TABLE SQL1.T1 ( C1 SMALLINT NOT NULL );
*+ Status = 0        SQLSTATE = 00000                               
DISPLAY TABLE SQL1.T1;
*+ Status = 0        SQLSTATE = 00000                               
*+   CREATE TABLE SQL1.T1                                           
*+       DATE CREATED 2012-11-27-02.50.57.079433   BY HILIA03       
*+       DATE LAST UPDATED 2012-11-27-02.50.57.079433   BY HILIA03  
*+     ( C1                               SMALLINT NOT NULL         
*+     )                                                            
*+       IN PROJSEG.PROJAREA                                        
*+       DEFAULT INDEX ON DBKEY                                     
*+       TABLE ID 1029                                              
*+       ;                                                          
SELECT NAME, SCHEMA, TABLE FROM SYSTEM.INDEX WHERE SCHEMA='SQL1';
*+                                                                  
*+ NAME                SCHEMA              TABLE                    
*+ ----                ------              -----                    
*+ ROWID01029          SQL1                T1                       
*+                                                                  
*+ 1 row processed                                                  
ALTER TABLE SQL1.T1 DROP DEFAULT INDEX;
*+ Status = 0        SQLSTATE = 00000                               
DISPLAY TABLE SQL1.T1;
*+ Status = 0        SQLSTATE = 00000                               
*+   CREATE TABLE SQL1.T1                                           
*+       DATE CREATED 2012-11-27-02.50.57.079433   BY HILIA03       
*+       DATE LAST UPDATED 2012-11-27-02.50.57.086301   BY HILIA03  
*+     ( C1                               SMALLINT NOT NULL         
*+     )                                                            
*+       IN PROJSEG.PROJAREA
*+       NO DEFAULT INDEX
*+       TABLE ID 1029
*+       ;
SELECT NAME, SCHEMA, TABLE FROM SYSTEM.INDEX WHERE SCHEMA='SQL1';
*+
*+ No qualifying rows found

Outcomes