test/sql/Optimizer/exp.test005.ksh

   1 echo create table t1(f1 tinyint, f2 smallint, f3 int, f4 bigint, f5 char(10), f6 varchar(10), f7 date, f8 time);
   2 Statement Executed
   3 echo create index hashtiny on t1(f1) hash;
   4 Statement Executed
   5 echo create index hashsmall on t1(f2) hash;
   6 Statement Executed
   7 echo create index hashint on t1(f3) hash;
   8 Statement Executed
   9 echo create index hashbig on t1(f4) hash;
  10 Statement Executed
  11 echo create index hashchar on t1(f5) hash;
  12 Statement Executed
  13 echo create index hashvarchar on t1(f6) hash;
  14 Statement Executed
  15 echo create index hashdate on t1(f7) hash;
  16 Statement Executed
  17 echo create index hashtime on t1(f8) hash;
  18 Statement Executed
  19 echo explain plan select * from t1;
  20  <TABLE-NODE>
  21  <NAME> t1 </NAME>
  22  <ScanType> TableScan </ScanType>
  23  </TABLE-NODE>
  24 echo explain plan select * from t1 where f1 in (0,2,4);
  25  <TABLE-NODE>
  26  <NAME> t1 </NAME>
  27  <ScanType> TableScan </ScanType>
  28    <PREDICATE>
  29    <LogOp> OR </LogOp>
  30    <PRED-LEFT>
  31      <PREDICATE>
  32      <LogOp> OR </LogOp>
  33      <PRED-LEFT>
  34        <PREDICATE>
  35        <FieldName1> f1 </FieldName1>
  36        <CompOp> Equals </CompOp>
  37        <OperandPtr> VALUE </OperandPtr>
  38        </PREDICATE>
  39      </PRED-LEFT>
  40      <PRED-RIGHT>
  41        <PREDICATE>
  42        <FieldName1> f1 </FieldName1>
  43        <CompOp> Equals </CompOp>
  44        <OperandPtr> VALUE </OperandPtr>
  45        </PREDICATE>
  46      </PRED-RIGHT>
  47      </PREDICATE>
  48    </PRED-LEFT>
  49    <PRED-RIGHT>
  50      <PREDICATE>
  51      <FieldName1> f1 </FieldName1>
  52      <CompOp> Equals </CompOp>
  53      <OperandPtr> VALUE </OperandPtr>
  54      </PREDICATE>
  55    </PRED-RIGHT>
  56    </PREDICATE>
  57  </TABLE-NODE>
  58 echo explain plan select * from t1 where f1 between 2 and 4;
  59  <TABLE-NODE>
  60  <NAME> t1 </NAME>
  61  <ScanType> TableScan </ScanType>
  62    <PREDICATE>
  63    <FieldName1> f1 </FieldName1>
  64    <CompOp> GreaterThanEquals </CompOp>
  65    <OperandPtr> VALUE </OperandPtr>
  66    <Comp2Op> LessThanEquals </Comp2Op>
  67    <Operand2Ptr> VALUE </Operand2Ptr>
  68    </PREDICATE>
  69  </TABLE-NODE>
  70 echo explain plan select * from t1 where f2 in (0,2,4);
  71  <TABLE-NODE>
  72  <NAME> t1 </NAME>
  73  <ScanType> TableScan </ScanType>
  74    <PREDICATE>
  75    <LogOp> OR </LogOp>
  76    <PRED-LEFT>
  77      <PREDICATE>
  78      <LogOp> OR </LogOp>
  79      <PRED-LEFT>
  80        <PREDICATE>
  81        <FieldName1> f2 </FieldName1>
  82        <CompOp> Equals </CompOp>
  83        <OperandPtr> VALUE </OperandPtr>
  84        </PREDICATE>
  85      </PRED-LEFT>
  86      <PRED-RIGHT>
  87        <PREDICATE>
  88        <FieldName1> f2 </FieldName1>
  89        <CompOp> Equals </CompOp>
  90        <OperandPtr> VALUE </OperandPtr>
  91        </PREDICATE>
  92      </PRED-RIGHT>
  93      </PREDICATE>
  94    </PRED-LEFT>
  95    <PRED-RIGHT>
  96      <PREDICATE>
  97      <FieldName1> f2 </FieldName1>
  98      <CompOp> Equals </CompOp>
  99      <OperandPtr> VALUE </OperandPtr>
 100      </PREDICATE>
 101    </PRED-RIGHT>
 102    </PREDICATE>
 103  </TABLE-NODE>
 104 echo explain plan select * from t1 where f2 between 2 and 4;
 105  <TABLE-NODE>
 106  <NAME> t1 </NAME>
 107  <ScanType> TableScan </ScanType>
 108    <PREDICATE>
 109    <FieldName1> f2 </FieldName1>
 110    <CompOp> GreaterThanEquals </CompOp>
 111    <OperandPtr> VALUE </OperandPtr>
 112    <Comp2Op> LessThanEquals </Comp2Op>
 113    <Operand2Ptr> VALUE </Operand2Ptr>
 114    </PREDICATE>
 115  </TABLE-NODE>
 116 echo explain plan select * from t1 where f3 in (1,2);
 117  <TABLE-NODE>
 118  <NAME> t1 </NAME>
 119  <ScanType> TableScan </ScanType>
 120    <PREDICATE>
 121    <LogOp> OR </LogOp>
 122    <PRED-LEFT>
 123      <PREDICATE>
 124      <FieldName1> f3 </FieldName1>
 125      <CompOp> Equals </CompOp>
 126      <OperandPtr> VALUE </OperandPtr>
 127      </PREDICATE>
 128    </PRED-LEFT>
 129    <PRED-RIGHT>
 130      <PREDICATE>
 131      <FieldName1> f3 </FieldName1>
 132      <CompOp> Equals </CompOp>
 133      <OperandPtr> VALUE </OperandPtr>
 134      </PREDICATE>
 135    </PRED-RIGHT>
 136    </PREDICATE>
 137  </TABLE-NODE>
 138 echo explain plan select * from t1 where f3 between 1 and 4;
 139  <TABLE-NODE>
 140  <NAME> t1 </NAME>
 141  <ScanType> TableScan </ScanType>
 142    <PREDICATE>
 143    <FieldName1> f3 </FieldName1>
 144    <CompOp> GreaterThanEquals </CompOp>
 145    <OperandPtr> VALUE </OperandPtr>
 146    <Comp2Op> LessThanEquals </Comp2Op>
 147    <Operand2Ptr> VALUE </Operand2Ptr>
 148    </PREDICATE>
 149  </TABLE-NODE>
 150 echo explain plan select * from t1 where f4 in (1,2);
 151  <TABLE-NODE>
 152  <NAME> t1 </NAME>
 153  <ScanType> TableScan </ScanType>
 154    <PREDICATE>
 155    <LogOp> OR </LogOp>
 156    <PRED-LEFT>
 157      <PREDICATE>
 158      <FieldName1> f4 </FieldName1>
 159      <CompOp> Equals </CompOp>
 160      <OperandPtr> VALUE </OperandPtr>
 161      </PREDICATE>
 162    </PRED-LEFT>
 163    <PRED-RIGHT>
 164      <PREDICATE>
 165      <FieldName1> f4 </FieldName1>
 166      <CompOp> Equals </CompOp>
 167      <OperandPtr> VALUE </OperandPtr>
 168      </PREDICATE>
 169    </PRED-RIGHT>
 170    </PREDICATE>
 171  </TABLE-NODE>
 172 echo explain plan select * from t1 where f4 between 1 and 4;
 173  <TABLE-NODE>
 174  <NAME> t1 </NAME>
 175  <ScanType> TableScan </ScanType>
 176    <PREDICATE>
 177    <FieldName1> f4 </FieldName1>
 178    <CompOp> GreaterThanEquals </CompOp>
 179    <OperandPtr> VALUE </OperandPtr>
 180    <Comp2Op> LessThanEquals </Comp2Op>
 181    <Operand2Ptr> VALUE </Operand2Ptr>
 182    </PREDICATE>
 183  </TABLE-NODE>
 184 echo explain plan select * from t1 where f5 in ('1','2');
 185  <TABLE-NODE>
 186  <NAME> t1 </NAME>
 187  <ScanType> TableScan </ScanType>
 188    <PREDICATE>
 189    <LogOp> OR </LogOp>
 190    <PRED-LEFT>
 191      <PREDICATE>
 192      <FieldName1> f5 </FieldName1>
 193      <CompOp> Equals </CompOp>
 194      <OperandPtr> VALUE </OperandPtr>
 195      </PREDICATE>
 196    </PRED-LEFT>
 197    <PRED-RIGHT>
 198      <PREDICATE>
 199      <FieldName1> f5 </FieldName1>
 200      <CompOp> Equals </CompOp>
 201      <OperandPtr> VALUE </OperandPtr>
 202      </PREDICATE>
 203    </PRED-RIGHT>
 204    </PREDICATE>
 205  </TABLE-NODE>
 206 echo explain plan select * from t1 where f5 like 'N%';
 207  <TABLE-NODE>
 208  <NAME> t1 </NAME>
 209  <ScanType> TableScan </ScanType>
 210    <PREDICATE>
 211    <FieldName1> f5 </FieldName1>
 212    <CompOp> Like </CompOp>
 213    <OperandPtr> VALUE </OperandPtr>
 214    </PREDICATE>
 215  </TABLE-NODE>
 216 echo explain plan select * from t1 where f5 like 'N_h';
 217  <TABLE-NODE>
 218  <NAME> t1 </NAME>
 219  <ScanType> TableScan </ScanType>
 220    <PREDICATE>
 221    <FieldName1> f5 </FieldName1>
 222    <CompOp> Like </CompOp>
 223    <OperandPtr> VALUE </OperandPtr>
 224    </PREDICATE>
 225  </TABLE-NODE>
 226 echo explain plan select * from t1 where f6 in ('1','2');
 227  <TABLE-NODE>
 228  <NAME> t1 </NAME>
 229  <ScanType> TableScan </ScanType>
 230    <PREDICATE>
 231    <LogOp> OR </LogOp>
 232    <PRED-LEFT>
 233      <PREDICATE>
 234      <FieldName1> f6 </FieldName1>
 235      <CompOp> Equals </CompOp>
 236      <OperandPtr> VALUE </OperandPtr>
 237      </PREDICATE>
 238    </PRED-LEFT>
 239    <PRED-RIGHT>
 240      <PREDICATE>
 241      <FieldName1> f6 </FieldName1>
 242      <CompOp> Equals </CompOp>
 243      <OperandPtr> VALUE </OperandPtr>
 244      </PREDICATE>
 245    </PRED-RIGHT>
 246    </PREDICATE>
 247  </TABLE-NODE>
 248 echo explain plan select * from t1 where f6 like 'N%';
 249  <TABLE-NODE>
 250  <NAME> t1 </NAME>
 251  <ScanType> TableScan </ScanType>
 252    <PREDICATE>
 253    <FieldName1> f6 </FieldName1>
 254    <CompOp> Like </CompOp>
 255    <OperandPtr> VALUE </OperandPtr>
 256    </PREDICATE>
 257  </TABLE-NODE>
 258 echo explain plan select * from t1 where f6 like 'N_h';
 259  <TABLE-NODE>
 260  <NAME> t1 </NAME>
 261  <ScanType> TableScan </ScanType>
 262    <PREDICATE>
 263    <FieldName1> f6 </FieldName1>
 264    <CompOp> Like </CompOp>
 265    <OperandPtr> VALUE </OperandPtr>
 266    </PREDICATE>
 267  </TABLE-NODE>
 268 echo explain plan select * from t1 where f7 in ('2003-11-12','2003-11-15');
 269  <TABLE-NODE>
 270  <NAME> t1 </NAME>
 271  <ScanType> TableScan </ScanType>
 272    <PREDICATE>
 273    <LogOp> OR </LogOp>
 274    <PRED-LEFT>
 275      <PREDICATE>
 276      <FieldName1> f7 </FieldName1>
 277      <CompOp> Equals </CompOp>
 278      <OperandPtr> VALUE </OperandPtr>
 279      </PREDICATE>
 280    </PRED-LEFT>
 281    <PRED-RIGHT>
 282      <PREDICATE>
 283      <FieldName1> f7 </FieldName1>
 284      <CompOp> Equals </CompOp>
 285      <OperandPtr> VALUE </OperandPtr>
 286      </PREDICATE>
 287    </PRED-RIGHT>
 288    </PREDICATE>
 289  </TABLE-NODE>
 290 echo explain plan select * from t1 where f7 between '2003-11-12' and '2003-11-15';
 291  <TABLE-NODE>
 292  <NAME> t1 </NAME>
 293  <ScanType> TableScan </ScanType>
 294    <PREDICATE>
 295    <FieldName1> f7 </FieldName1>
 296    <CompOp> GreaterThanEquals </CompOp>
 297    <OperandPtr> VALUE </OperandPtr>
 298    <Comp2Op> LessThanEquals </Comp2Op>
 299    <Operand2Ptr> VALUE </Operand2Ptr>
 300    </PREDICATE>
 301  </TABLE-NODE>
 302 echo explain plan select * from t1 where f8 in ('03:21:00','03:21:08');
 303  <TABLE-NODE>
 304  <NAME> t1 </NAME>
 305  <ScanType> TableScan </ScanType>
 306    <PREDICATE>
 307    <LogOp> OR </LogOp>
 308    <PRED-LEFT>
 309      <PREDICATE>
 310      <FieldName1> f8 </FieldName1>
 311      <CompOp> Equals </CompOp>
 312      <OperandPtr> VALUE </OperandPtr>
 313      </PREDICATE>
 314    </PRED-LEFT>
 315    <PRED-RIGHT>
 316      <PREDICATE>
 317      <FieldName1> f8 </FieldName1>
 318      <CompOp> Equals </CompOp>
 319      <OperandPtr> VALUE </OperandPtr>
 320      </PREDICATE>
 321    </PRED-RIGHT>
 322    </PREDICATE>
 323  </TABLE-NODE>
 324 echo explain plan select * from t1 where f8 between '03:21:00' and '03:21:08';
 325  <TABLE-NODE>
 326  <NAME> t1 </NAME>
 327  <ScanType> TableScan </ScanType>
 328    <PREDICATE>
 329    <FieldName1> f8 </FieldName1>
 330    <CompOp> GreaterThanEquals </CompOp>
 331    <OperandPtr> VALUE </OperandPtr>
 332    <Comp2Op> LessThanEquals </Comp2Op>
 333    <Operand2Ptr> VALUE </Operand2Ptr>
 334    </PREDICATE>
 335  </TABLE-NODE>
 336 Statement Executed