SQL/MX 3.x Query Guide (H06.22+, J06.11+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—640323-001
1-18
Improving Query Performance
Example 1-2. EXPLAIN statement OPTIONS ‘f’ Output for Query Using
CHECK_CONSTRAINT_PRUNING
>>showddl t1;
CREATE TABLE CAT.SCH.T1
(
COL1 INT DEFAULT NULL
, COL2 INT DEFAULT NULL
)
LOCATION \DMR11.$DATA04.ZSDLPGGW.VBNG7V00
NAME DMR11_DATA04_ZSDLPGGW_VBNG7V00
;
ALTER TABLE CAT.SCH.T1
ADD CONSTRAINT CAT.SCH.CHK1 CHECK (CAT.SCH.T1.COL1 > 100)
DROPPABLE ;
--- SQL operation complete.
>>control query default check_constraint_pruning 'off';
--- SQL operation complete.
>>prepare xx from select * from t1 where col1 = 10;
--- SQL command prepared.
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
--- --- --- --------- ------ ----------- -------
2 . 3 root 1.00E+0
1 . 2 partition_access 1.00E+0
. . 1 file_scan fs fr T1 (s) 1.00E+0
--- SQL operation complete.
>>control query default check_constraint_pruning 'on';
--- SQL operation complete.
>>
>>prepare xx from select * from t1 where col1 = 10;
--- SQL command prepared.
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
--- --- --- --------- ------ ----------- -------
1 . 2 root 1.00E+0
. . 1 values 1.00E+0
--- SQL operation complete.