test/sql/Optimizer/exp.test016.ksh

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