SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

Compiling and Executing a Query
HP NonStop SQL/MX Release 3.1 Query Guide—663851-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.
TP663851.fm Page 18 Monday, October 17, 2011 11:48 AM