IDMS/SQL - Sample Session II - Optimization

Recently I came across a Unix database where the optimizer behaved bizzarre and the programmers were forced to put index name in the SQL statements to force the optimizer to use the index. This violates the infamous '12 Rules' and '12+ Rules' of the relational purists. SQL DML should never be bothered about INDEX (which is purely a physical tuning option), at least index-name should never figure in SQL statements. Else what is the difference between the relational databases and non-relational ones.

The Unix db had many options to display the optimizer output... It produced a lot of junk which was unreadable. Then I came back to IDMS/SQL to see how the optimizer behaved here and what options are available for a programmer to see what his query is going to do.

The following session is on optimization in IDMS/SQL and the powerful but simple commands one has at his disposal to plan his SQL.

 

One Last Time!

First link to the first article in the series

V15 Enter Next Task Code:

OCF

OCF 15.0 ONLINE IDMS NO ERRORS DICT=TSTDICT

We need to create two tables for this example. ORDER_HEAD and TASK.
First we create them without any indexes (indices for purists). Later we add indexes.

CREATE TABLE IDMSSQL.ORDER_HEAD 
( ORDER_ID 	      NUMERIC(12) NOT NULL, 
  ORDER_TYPE_PRIORITY     SMALLINT NOT NULL, 
  ACTION_TYPE_ID 	CHARACTER(3), 
  ORDER_DATE 	         TIMESTAMP, 
  STATUS 		SMALLINT NOT NULL, 
  INFO_CHG_DATE 	  TIMESTAMP 
) ; 
CREATE UNIQUE INDEX IDX_STATUS_ID 
ON IDMSSQL.ORDER_HEAD 
( STATUS ASC, 
ORDER_ID ASC, 
ORDER_TYPE_PRIORITY ASC );
     CREATE TABLE IDMSSQL.TASK                                    
       ( ORDER_ID                         NUMERIC(12) NOT NULL,   
         SYSTEM_ID                        SMALLINT NOT NULL,      
         STATUS                           SMALLINT NOT NULL,      
         BLOCKING_ORDER_ID                NUMERIC(12),            
         RETRY_COUNT                      SMALLINT,               
         INFO_CHG_DATE                    TIMESTAMP NOT NULL,     
         INFO_CHG_MODULE_ID               SMALLINT NOT NULL       
       )      ; 
                                       
   CREATE UNIQUE INDEX IDX_ID_SYSTEMID                         
        ON IDMSSQL.TASK                                         
            (   ORDER_ID ASC,                                   
                SYSTEM_ID ASC )                                 
        UNCOMPRESSED                                            
        ;                                                       
                                                   
Populate the tables with minimum values
INSERT INTO ORDER_HEAD VALUES (360,6,'EAB','2006-09-08-16.43.17.123456', 
20, '2007-03-26-16.43.17.123456'); 
                                                               
EXPLAIN STATEMENT 'SELECT  * FROM ORDER_HEAD WHERE STATUS      
IN (0,20)                   ' STATEMENT NUMBER 22;             
*+ Status = 0        SQLSTATE = 00000                          
                                
SELECT  TSCHEMA, TABLE, ACMODE, ACNAME, LFS   FROM ACCESS_PLAN         
                                                               
*+                                                             
*+ TSCHEMA     TABLE           ACMODE  ACNAME          LFS     
*+ -------     -----           ------  ------          ---     
*+ IDMSSQL     ORDER_HEAD      S       ROWID01111      N       

Similarly for the task, we put 3 rows and they are
SELECT * FROM TASK 
  ORDER_ID  SYSTEM_ID  STATUS  BLOCKING_ORDER_ID  RETRY_COUNT  INFO_CHG_DATE
                                                                            
       360          1       0             <null>            0  2007-03-26-16
       360          2       0             <null>            0  2007-03-26-16
       360          3       0             <null>            0  2007-03-26-16
SELECT  ORDER_ID                                             
            FROM ORDER_HEAD OH                               
            WHERE                                            
                STATUS IN (20,50) -- NEEDED FOR OPTIMISER    
            AND                                              
                 (STATUS = 20 AND NOT EXISTS                 
(SELECT 1 FROM TASK T WHERE T.STATUS IN (5,20,30,35,40,60,80)
    AND T.ORDER_ID=OH.ORDER_ID))                             
*+                                                           
*+       ORDER_ID                                            
*+       --------                                            
*+            360                                            
*+                                                           
*+ 1 row processed                      
+ REP MOD EXPLAIN-BS1 
EXPLAIN STATEMENT                                                   
'SELECT  ORDER_ID                                                   
            FROM ORDER_HEAD OH                                      
            WHERE                                                   
                STATUS IN (20,50)                                   
            AND                                                     
                 (STATUS = 20 AND NOT EXISTS                        
(SELECT 1 FROM TASK T WHERE T.STATUS IN (5,20,30,35,40,60,80)       
    AND T.ORDER_ID=OH.ORDER_ID)) '  STATEMENT NUMBER 10             
INTO TABLE  IDMSSQL.ACCESS_PLAN;                                    
*+ Status = 0        SQLSTATE = 00000              


SELECT  TSCHEMA, TABLE, ACMODE, ACNAME, LFS, SECTION  FROM ACCESS_PLAN       
WHERE SECTION=10;                                                            
*+                                                                           
*+ TSCHEMA             TABLE               ACMODE  ACNAME              LFS   
*+ -------             -----               ------  ------              ---   
*+ IDMSSQL             ORDER_HEAD          S       ROWID01111          N     
*+ IDMSSQL             TASK                S       ROWID01112          N     
*+                                                                           
*+ SECTION                                                                   
*+ -------                                                                   
*+      10                                                                   
*+      10                                                                   
*+                                                                           
IDMS  will use row-id even though there are no indexes!                 
CREATE UNIQUE INDEX IDX_STATUS_ID                      
    ON ORDER_HEAD(STATUS,ORDER_ID,ORDER_TYPE_PRIORITY) 
;                                                      
*+ Status = 0        SQLSTATE = 00000                  
CREATE UNIQUE INDEX IDX_ID_SYSTEMID     
    ON TASK(ORDER_ID,SYSTEM_ID)         
;                                       
*+ Status = 0        SQLSTATE = 00000   
                                        

Now the scan is different

SELECT  TSCHEMA, TABLE, ACMODE, ACNAME, LFS, SECTION  FROM ACCESS_PLAN      
WHERE SECTION=11;                                                           
*+                                                                          
*+ TSCHEMA             TABLE               ACMODE  ACNAME              LFS  
*+ -------             -----               ------  ------              ---  
*+ IDMSSQL             ORDER_HEAD          I       IDX_STATUS_ID       Y    
*+ IDMSSQL             TASK                S       ROWID01112          N    
*+                                                                          
*+ SECTION                                                                  
*+ -------                                                                  
*+      11                                                                  
*+      11                                                                  
*+                                                                          
UPDATE STATISTICS FOR TABLE TASK         
*+ Status = 0        SQLSTATE = 00000    

Here the key information is the ACMODE. In the first case, index IDX_STATUS_ID is correctly used.
Second case is sequential scan of the default index - ROWID01111. IDMS/SQL always creates a default index for all tables and will utilize the same to avoid an area sweep. This feature is now explicitly and implicitly followed by the 'modern' Unix databases also.

In all these examples, one can see that AREA_SWEEP (or tablespace scan in reltional terms) is avoided. This being the case in IDMS, one wonders why the so-called wonderful databases which are more 'relational' than IDMS/SQL is having problems with their optimizers.