test/sql/Optimizer/exp.test006.ksh

   1 echo create table t1(f1 tinyint, f2 smallint, f3 int, f4 bigint, f5 char(10), f6 varchar(10), f7 date, f8 time, f9 float, f10 double, f11 timestamp);
   2 Statement Executed
   3 echo create index treetiny on t1(f1) tree;
   4 Statement Executed
   5 echo create index treesmall on t1(f2) tree;
   6 Statement Executed
   7 echo create index treeint on t1(f3) tree;
   8 Statement Executed
   9 echo create index treebig on t1(f4) tree;
  10 Statement Executed
  11 echo create index treechar on t1(f5) tree;
  12 Statement Executed
  13 echo create index treevarchar on t1(f6) tree;
  14 Statement Executed
  15 echo create index treedate on t1(f7) tree;
  16 Statement Executed
  17 echo create index treetime on t1(f8) tree;
  18 Statement Executed
  19 echo create index treefloat on t1(f9) tree;
  20 Statement Executed
  21 echo create index treedouble on t1(f10) tree;
  22 Statement Executed
  23 echo create index treetimestamp on t1(f11) tree;
  24 Statement Executed
  25 echo explain plan select * from t1;
  26  <TABLE-NODE>
  27  <NAME> t1 </NAME>
  28  <ScanType> TableScan </ScanType>
  29  </TABLE-NODE>
  30 echo explain plan select * from t1 where f1 in (0,2,4);
  31  <TABLE-NODE>
  32  <NAME> t1 </NAME>
  33  <ScanType> TableScan </ScanType>
  34    <PREDICATE>
  35    <LogOp> OR </LogOp>
  36    <PRED-LEFT>
  37      <PREDICATE>
  38      <LogOp> OR </LogOp>
  39      <PRED-LEFT>
  40        <PREDICATE>
  41        <FieldName1> f1 </FieldName1>
  42        <CompOp> Equals </CompOp>
  43        <OperandPtr> VALUE </OperandPtr>
  44        </PREDICATE>
  45      </PRED-LEFT>
  46      <PRED-RIGHT>
  47        <PREDICATE>
  48        <FieldName1> f1 </FieldName1>
  49        <CompOp> Equals </CompOp>
  50        <OperandPtr> VALUE </OperandPtr>
  51        </PREDICATE>
  52      </PRED-RIGHT>
  53      </PREDICATE>
  54    </PRED-LEFT>
  55    <PRED-RIGHT>
  56      <PREDICATE>
  57      <FieldName1> f1 </FieldName1>
  58      <CompOp> Equals </CompOp>
  59      <OperandPtr> VALUE </OperandPtr>
  60      </PREDICATE>
  61    </PRED-RIGHT>
  62    </PREDICATE>
  63  </TABLE-NODE>
  64 echo explain plan select * from t1 where f1 between 2 and 4;
  65  <TABLE-NODE>
  66  <NAME> t1 </NAME>
  67  <ScanType> TreeScan </ScanType>
  68    <PREDICATE>
  69    <FieldName1> f1 </FieldName1>
  70    <CompOp> GreaterThanEquals </CompOp>
  71    <OperandPtr> VALUE </OperandPtr>
  72    <Comp2Op> LessThanEquals </Comp2Op>
  73    <Operand2Ptr> VALUE </Operand2Ptr>
  74    </PREDICATE>
  75  </TABLE-NODE>
  76 echo explain plan select * from t1 where f2 in (0,2,4);
  77  <TABLE-NODE>
  78  <NAME> t1 </NAME>
  79  <ScanType> TableScan </ScanType>
  80    <PREDICATE>
  81    <LogOp> OR </LogOp>
  82    <PRED-LEFT>
  83      <PREDICATE>
  84      <LogOp> OR </LogOp>
  85      <PRED-LEFT>
  86        <PREDICATE>
  87        <FieldName1> f2 </FieldName1>
  88        <CompOp> Equals </CompOp>
  89        <OperandPtr> VALUE </OperandPtr>
  90        </PREDICATE>
  91      </PRED-LEFT>
  92      <PRED-RIGHT>
  93        <PREDICATE>
  94        <FieldName1> f2 </FieldName1>
  95        <CompOp> Equals </CompOp>
  96        <OperandPtr> VALUE </OperandPtr>
  97        </PREDICATE>
  98      </PRED-RIGHT>
  99      </PREDICATE>
 100    </PRED-LEFT>
 101    <PRED-RIGHT>
 102      <PREDICATE>
 103      <FieldName1> f2 </FieldName1>
 104      <CompOp> Equals </CompOp>
 105      <OperandPtr> VALUE </OperandPtr>
 106      </PREDICATE>
 107    </PRED-RIGHT>
 108    </PREDICATE>
 109  </TABLE-NODE>
 110 echo explain plan select * from t1 where f2 between 2 and 4;
 111  <TABLE-NODE>
 112  <NAME> t1 </NAME>
 113  <ScanType> TreeScan </ScanType>
 114    <PREDICATE>
 115    <FieldName1> f2 </FieldName1>
 116    <CompOp> GreaterThanEquals </CompOp>
 117    <OperandPtr> VALUE </OperandPtr>
 118    <Comp2Op> LessThanEquals </Comp2Op>
 119    <Operand2Ptr> VALUE </Operand2Ptr>
 120    </PREDICATE>
 121  </TABLE-NODE>
 122 echo explain plan select * from t1 where f3 in (1,2);
 123  <TABLE-NODE>
 124  <NAME> t1 </NAME>
 125  <ScanType> TableScan </ScanType>
 126    <PREDICATE>
 127    <LogOp> OR </LogOp>
 128    <PRED-LEFT>
 129      <PREDICATE>
 130      <FieldName1> f3 </FieldName1>
 131      <CompOp> Equals </CompOp>
 132      <OperandPtr> VALUE </OperandPtr>
 133      </PREDICATE>
 134    </PRED-LEFT>
 135    <PRED-RIGHT>
 136      <PREDICATE>
 137      <FieldName1> f3 </FieldName1>
 138      <CompOp> Equals </CompOp>
 139      <OperandPtr> VALUE </OperandPtr>
 140      </PREDICATE>
 141    </PRED-RIGHT>
 142    </PREDICATE>
 143  </TABLE-NODE>
 144 echo explain plan select * from t1 where f3 between 1 and 4;
 145  <TABLE-NODE>
 146  <NAME> t1 </NAME>
 147  <ScanType> TreeScan </ScanType>
 148    <PREDICATE>
 149    <FieldName1> f3 </FieldName1>
 150    <CompOp> GreaterThanEquals </CompOp>
 151    <OperandPtr> VALUE </OperandPtr>
 152    <Comp2Op> LessThanEquals </Comp2Op>
 153    <Operand2Ptr> VALUE </Operand2Ptr>
 154    </PREDICATE>
 155  </TABLE-NODE>
 156 echo explain plan select * from t1 where f4 in (1,2);
 157  <TABLE-NODE>
 158  <NAME> t1 </NAME>
 159  <ScanType> TableScan </ScanType>
 160    <PREDICATE>
 161    <LogOp> OR </LogOp>
 162    <PRED-LEFT>
 163      <PREDICATE>
 164      <FieldName1> f4 </FieldName1>
 165      <CompOp> Equals </CompOp>
 166      <OperandPtr> VALUE </OperandPtr>
 167      </PREDICATE>
 168    </PRED-LEFT>
 169    <PRED-RIGHT>
 170      <PREDICATE>
 171      <FieldName1> f4 </FieldName1>
 172      <CompOp> Equals </CompOp>
 173      <OperandPtr> VALUE </OperandPtr>
 174      </PREDICATE>
 175    </PRED-RIGHT>
 176    </PREDICATE>
 177  </TABLE-NODE>
 178 echo explain plan select * from t1 where f4 between 1 and 4;
 179  <TABLE-NODE>
 180  <NAME> t1 </NAME>
 181  <ScanType> TreeScan </ScanType>
 182    <PREDICATE>
 183    <FieldName1> f4 </FieldName1>
 184    <CompOp> GreaterThanEquals </CompOp>
 185    <OperandPtr> VALUE </OperandPtr>
 186    <Comp2Op> LessThanEquals </Comp2Op>
 187    <Operand2Ptr> VALUE </Operand2Ptr>
 188    </PREDICATE>
 189  </TABLE-NODE>
 190 echo explain plan select * from t1 where f5 in ('1','2');
 191  <TABLE-NODE>
 192  <NAME> t1 </NAME>
 193  <ScanType> TableScan </ScanType>
 194    <PREDICATE>
 195    <LogOp> OR </LogOp>
 196    <PRED-LEFT>
 197      <PREDICATE>
 198      <FieldName1> f5 </FieldName1>
 199      <CompOp> Equals </CompOp>
 200      <OperandPtr> VALUE </OperandPtr>
 201      </PREDICATE>
 202    </PRED-LEFT>
 203    <PRED-RIGHT>
 204      <PREDICATE>
 205      <FieldName1> f5 </FieldName1>
 206      <CompOp> Equals </CompOp>
 207      <OperandPtr> VALUE </OperandPtr>
 208      </PREDICATE>
 209    </PRED-RIGHT>
 210    </PREDICATE>
 211  </TABLE-NODE>
 212 echo explain plan select * from t1 where f5 like 'N%';
 213  <TABLE-NODE>
 214  <NAME> t1 </NAME>
 215  <ScanType> TableScan </ScanType>
 216    <PREDICATE>
 217    <FieldName1> f5 </FieldName1>
 218    <CompOp> Like </CompOp>
 219    <OperandPtr> VALUE </OperandPtr>
 220    </PREDICATE>
 221  </TABLE-NODE>
 222 echo explain plan select * from t1 where f5 like 'N_h';
 223  <TABLE-NODE>
 224  <NAME> t1 </NAME>
 225  <ScanType> TableScan </ScanType>
 226    <PREDICATE>
 227    <FieldName1> f5 </FieldName1>
 228    <CompOp> Like </CompOp>
 229    <OperandPtr> VALUE </OperandPtr>
 230    </PREDICATE>
 231  </TABLE-NODE>
 232 echo explain plan select * from t1 where f6 in ('1','2');
 233  <TABLE-NODE>
 234  <NAME> t1 </NAME>
 235  <ScanType> TableScan </ScanType>
 236    <PREDICATE>
 237    <LogOp> OR </LogOp>
 238    <PRED-LEFT>
 239      <PREDICATE>
 240      <FieldName1> f6 </FieldName1>
 241      <CompOp> Equals </CompOp>
 242      <OperandPtr> VALUE </OperandPtr>
 243      </PREDICATE>
 244    </PRED-LEFT>
 245    <PRED-RIGHT>
 246      <PREDICATE>
 247      <FieldName1> f6 </FieldName1>
 248      <CompOp> Equals </CompOp>
 249      <OperandPtr> VALUE </OperandPtr>
 250      </PREDICATE>
 251    </PRED-RIGHT>
 252    </PREDICATE>
 253  </TABLE-NODE>
 254 echo explain plan select * from t1 where f6 like 'N%';
 255  <TABLE-NODE>
 256  <NAME> t1 </NAME>
 257  <ScanType> TableScan </ScanType>
 258    <PREDICATE>
 259    <FieldName1> f6 </FieldName1>
 260    <CompOp> Like </CompOp>
 261    <OperandPtr> VALUE </OperandPtr>
 262    </PREDICATE>
 263  </TABLE-NODE>
 264 echo explain plan select * from t1 where f6 like 'N_h';
 265  <TABLE-NODE>
 266  <NAME> t1 </NAME>
 267  <ScanType> TableScan </ScanType>
 268    <PREDICATE>
 269    <FieldName1> f6 </FieldName1>
 270    <CompOp> Like </CompOp>
 271    <OperandPtr> VALUE </OperandPtr>
 272    </PREDICATE>
 273  </TABLE-NODE>
 274 echo explain plan select * from t1 where f7 in ('2003-11-12','2003-11-15');
 275  <TABLE-NODE>
 276  <NAME> t1 </NAME>
 277  <ScanType> TableScan </ScanType>
 278    <PREDICATE>
 279    <LogOp> OR </LogOp>
 280    <PRED-LEFT>
 281      <PREDICATE>
 282      <FieldName1> f7 </FieldName1>
 283      <CompOp> Equals </CompOp>
 284      <OperandPtr> VALUE </OperandPtr>
 285      </PREDICATE>
 286    </PRED-LEFT>
 287    <PRED-RIGHT>
 288      <PREDICATE>
 289      <FieldName1> f7 </FieldName1>
 290      <CompOp> Equals </CompOp>
 291      <OperandPtr> VALUE </OperandPtr>
 292      </PREDICATE>
 293    </PRED-RIGHT>
 294    </PREDICATE>
 295  </TABLE-NODE>
 296 echo explain plan select * from t1 where f7 between '2003-11-12' and '2003-11-15';
 297  <TABLE-NODE>
 298  <NAME> t1 </NAME>
 299  <ScanType> TreeScan </ScanType>
 300    <PREDICATE>
 301    <FieldName1> f7 </FieldName1>
 302    <CompOp> GreaterThanEquals </CompOp>
 303    <OperandPtr> VALUE </OperandPtr>
 304    <Comp2Op> LessThanEquals </Comp2Op>
 305    <Operand2Ptr> VALUE </Operand2Ptr>
 306    </PREDICATE>
 307  </TABLE-NODE>
 308 echo explain plan select * from t1 where f8 in ('03:21:00','03:21:08');
 309  <TABLE-NODE>
 310  <NAME> t1 </NAME>
 311  <ScanType> TableScan </ScanType>
 312    <PREDICATE>
 313    <LogOp> OR </LogOp>
 314    <PRED-LEFT>
 315      <PREDICATE>
 316      <FieldName1> f8 </FieldName1>
 317      <CompOp> Equals </CompOp>
 318      <OperandPtr> VALUE </OperandPtr>
 319      </PREDICATE>
 320    </PRED-LEFT>
 321    <PRED-RIGHT>
 322      <PREDICATE>
 323      <FieldName1> f8 </FieldName1>
 324      <CompOp> Equals </CompOp>
 325      <OperandPtr> VALUE </OperandPtr>
 326      </PREDICATE>
 327    </PRED-RIGHT>
 328    </PREDICATE>
 329  </TABLE-NODE>
 330 echo explain plan select * from t1 where f8 between '03:21:00' and '03:21:08';
 331  <TABLE-NODE>
 332  <NAME> t1 </NAME>
 333  <ScanType> TreeScan </ScanType>
 334    <PREDICATE>
 335    <FieldName1> f8 </FieldName1>
 336    <CompOp> GreaterThanEquals </CompOp>
 337    <OperandPtr> VALUE </OperandPtr>
 338    <Comp2Op> LessThanEquals </Comp2Op>
 339    <Operand2Ptr> VALUE </Operand2Ptr>
 340    </PREDICATE>
 341  </TABLE-NODE>
 342 echo explain plan select * from t1 where f9 in ('2.7','3.7');
 343  <TABLE-NODE>
 344  <NAME> t1 </NAME>
 345  <ScanType> TableScan </ScanType>
 346    <PREDICATE>
 347    <LogOp> OR </LogOp>
 348    <PRED-LEFT>
 349      <PREDICATE>
 350      <FieldName1> f9 </FieldName1>
 351      <CompOp> Equals </CompOp>
 352      <OperandPtr> VALUE </OperandPtr>
 353      </PREDICATE>
 354    </PRED-LEFT>
 355    <PRED-RIGHT>
 356      <PREDICATE>
 357      <FieldName1> f9 </FieldName1>
 358      <CompOp> Equals </CompOp>
 359      <OperandPtr> VALUE </OperandPtr>
 360      </PREDICATE>
 361    </PRED-RIGHT>
 362    </PREDICATE>
 363  </TABLE-NODE>
 364 echo explain plan select * from t1 where f9 between '2.7' and '3.7';
 365  <TABLE-NODE>
 366  <NAME> t1 </NAME>
 367  <ScanType> TreeScan </ScanType>
 368    <PREDICATE>
 369    <FieldName1> f9 </FieldName1>
 370    <CompOp> GreaterThanEquals </CompOp>
 371    <OperandPtr> VALUE </OperandPtr>
 372    <Comp2Op> LessThanEquals </Comp2Op>
 373    <Operand2Ptr> VALUE </Operand2Ptr>
 374    </PREDICATE>
 375  </TABLE-NODE>
 376 echo explain plan select * from t1 where f10 in ('2.7','3.7');
 377  <TABLE-NODE>
 378  <NAME> t1 </NAME>
 379  <ScanType> TableScan </ScanType>
 380    <PREDICATE>
 381    <LogOp> OR </LogOp>
 382    <PRED-LEFT>
 383      <PREDICATE>
 384      <FieldName1> f10 </FieldName1>
 385      <CompOp> Equals </CompOp>
 386      <OperandPtr> VALUE </OperandPtr>
 387      </PREDICATE>
 388    </PRED-LEFT>
 389    <PRED-RIGHT>
 390      <PREDICATE>
 391      <FieldName1> f10 </FieldName1>
 392      <CompOp> Equals </CompOp>
 393      <OperandPtr> VALUE </OperandPtr>
 394      </PREDICATE>
 395    </PRED-RIGHT>
 396    </PREDICATE>
 397  </TABLE-NODE>
 398 echo explain plan select * from t1 where f10 between '2.7' and '3.7';
 399  <TABLE-NODE>
 400  <NAME> t1 </NAME>
 401  <ScanType> TreeScan </ScanType>
 402    <PREDICATE>
 403    <FieldName1> f10 </FieldName1>
 404    <CompOp> GreaterThanEquals </CompOp>
 405    <OperandPtr> VALUE </OperandPtr>
 406    <Comp2Op> LessThanEquals </Comp2Op>
 407    <Operand2Ptr> VALUE </Operand2Ptr>
 408    </PREDICATE>
 409  </TABLE-NODE>
 410 echo explain plan select * from t1 where f11 in ('2003-11-12 01:01:01','2003-11-18 01:01:01');
 411  <TABLE-NODE>
 412  <NAME> t1 </NAME>
 413  <ScanType> TableScan </ScanType>
 414    <PREDICATE>
 415    <LogOp> OR </LogOp>
 416    <PRED-LEFT>
 417      <PREDICATE>
 418      <FieldName1> f11 </FieldName1>
 419      <CompOp> Equals </CompOp>
 420      <OperandPtr> VALUE </OperandPtr>
 421      </PREDICATE>
 422    </PRED-LEFT>
 423    <PRED-RIGHT>
 424      <PREDICATE>
 425      <FieldName1> f11 </FieldName1>
 426      <CompOp> Equals </CompOp>
 427      <OperandPtr> VALUE </OperandPtr>
 428      </PREDICATE>
 429    </PRED-RIGHT>
 430    </PREDICATE>
 431  </TABLE-NODE>
 432 echo explain plan select * from t1 where f11 between '2003-11-12 01:01:01' and '2003-11-18 01:01:01';
 433  <TABLE-NODE>
 434  <NAME> t1 </NAME>
 435  <ScanType> TreeScan </ScanType>
 436    <PREDICATE>
 437    <FieldName1> f11 </FieldName1>
 438    <CompOp> GreaterThanEquals </CompOp>
 439    <OperandPtr> VALUE </OperandPtr>
 440    <Comp2Op> LessThanEquals </Comp2Op>
 441    <Operand2Ptr> VALUE </Operand2Ptr>
 442    </PREDICATE>
 443  </TABLE-NODE>
 444 Statement Executed