IDMS/SQL - Sample Session II - OptimizationRecently 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. 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. 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. |
|