test/sql/Optimizer/exp.test013.ksh

   1 echo create table t1(f1 varchar, f2 varchar, f3 varchar, f4 varchar);
   2 Statement Executed
   3 echo create index hashvarcharf1f2f3 on t1(f2,f3,f1) 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 f1 = 'a';
  11  <TABLE-NODE>
  12  <NAME> t1 </NAME>
  13  <ScanType> TableScan </ScanType>
  14    <PREDICATE>
  15    <FieldName1> f1 </FieldName1>
  16    <CompOp> Equals </CompOp>
  17    <OperandPtr> VALUE </OperandPtr>
  18    </PREDICATE>
  19  </TABLE-NODE>
  20 echo explain plan select * from t1 where f1 = 'a' and f2='a';
  21  <TABLE-NODE>
  22  <NAME> t1 </NAME>
  23  <ScanType> TableScan </ScanType>
  24    <PREDICATE>
  25    <LogOp> AND </LogOp>
  26    <PRED-LEFT>
  27      <PREDICATE>
  28      <FieldName1> f1 </FieldName1>
  29      <CompOp> Equals </CompOp>
  30      <OperandPtr> VALUE </OperandPtr>
  31      </PREDICATE>
  32    </PRED-LEFT>
  33    <PRED-RIGHT>
  34      <PREDICATE>
  35      <FieldName1> f2 </FieldName1>
  36      <CompOp> Equals </CompOp>
  37      <OperandPtr> VALUE </OperandPtr>
  38      </PREDICATE>
  39    </PRED-RIGHT>
  40    </PREDICATE>
  41  </TABLE-NODE>
  42 echo explain plan select * from t1 where f1 = 'a' and f2='a' and f3='a';
  43  <TABLE-NODE>
  44  <NAME> t1 </NAME>
  45  <ScanType> TableScan </ScanType>
  46    <PREDICATE>
  47    <LogOp> AND </LogOp>
  48    <PRED-LEFT>
  49      <PREDICATE>
  50      <LogOp> AND </LogOp>
  51      <PRED-LEFT>
  52        <PREDICATE>
  53        <FieldName1> f1 </FieldName1>
  54        <CompOp> Equals </CompOp>
  55        <OperandPtr> VALUE </OperandPtr>
  56        </PREDICATE>
  57      </PRED-LEFT>
  58      <PRED-RIGHT>
  59        <PREDICATE>
  60        <FieldName1> f2 </FieldName1>
  61        <CompOp> Equals </CompOp>
  62        <OperandPtr> VALUE </OperandPtr>
  63        </PREDICATE>
  64      </PRED-RIGHT>
  65      </PREDICATE>
  66    </PRED-LEFT>
  67    <PRED-RIGHT>
  68      <PREDICATE>
  69      <FieldName1> f3 </FieldName1>
  70      <CompOp> Equals </CompOp>
  71      <OperandPtr> VALUE </OperandPtr>
  72      </PREDICATE>
  73    </PRED-RIGHT>
  74    </PREDICATE>
  75  </TABLE-NODE>
  76 echo explain plan select * from t1 where f1 = 'a' or f2='a' or f3='a';
  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> f1 </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> f3 </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 f1 = 'a' and f2='a' or f3='a';
 111  <TABLE-NODE>
 112  <NAME> t1 </NAME>
 113  <ScanType> TableScan </ScanType>
 114    <PREDICATE>
 115    <LogOp> OR </LogOp>
 116    <PRED-LEFT>
 117      <PREDICATE>
 118      <LogOp> AND </LogOp>
 119      <PRED-LEFT>
 120        <PREDICATE>
 121        <FieldName1> f1 </FieldName1>
 122        <CompOp> Equals </CompOp>
 123        <OperandPtr> VALUE </OperandPtr>
 124        </PREDICATE>
 125      </PRED-LEFT>
 126      <PRED-RIGHT>
 127        <PREDICATE>
 128        <FieldName1> f2 </FieldName1>
 129        <CompOp> Equals </CompOp>
 130        <OperandPtr> VALUE </OperandPtr>
 131        </PREDICATE>
 132      </PRED-RIGHT>
 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 f1 = 'a' and f2='a' and not(f3='a');
 145  <TABLE-NODE>
 146  <NAME> t1 </NAME>
 147  <ScanType> TableScan </ScanType>
 148    <PREDICATE>
 149    <LogOp> AND </LogOp>
 150    <PRED-LEFT>
 151      <PREDICATE>
 152      <LogOp> AND </LogOp>
 153      <PRED-LEFT>
 154        <PREDICATE>
 155        <FieldName1> f1 </FieldName1>
 156        <CompOp> Equals </CompOp>
 157        <OperandPtr> VALUE </OperandPtr>
 158        </PREDICATE>
 159      </PRED-LEFT>
 160      <PRED-RIGHT>
 161        <PREDICATE>
 162        <FieldName1> f2 </FieldName1>
 163        <CompOp> Equals </CompOp>
 164        <OperandPtr> VALUE </OperandPtr>
 165        </PREDICATE>
 166      </PRED-RIGHT>
 167      </PREDICATE>
 168    </PRED-LEFT>
 169    <PRED-RIGHT>
 170      <PREDICATE>
 171      <LogOp> NOT </LogOp>
 172      <PRED-LEFT>
 173        <PREDICATE>
 174        <FieldName1> f3 </FieldName1>
 175        <CompOp> Equals </CompOp>
 176        <OperandPtr> VALUE </OperandPtr>
 177        </PREDICATE>
 178      </PRED-LEFT>
 179      </PREDICATE>
 180    </PRED-RIGHT>
 181    </PREDICATE>
 182  </TABLE-NODE>
 183 echo explain plan select * from t1 where f1 in('a','a','a') and f2='a' and not(f3='a');
 184  <TABLE-NODE>
 185  <NAME> t1 </NAME>
 186  <ScanType> TableScan </ScanType>
 187    <PREDICATE>
 188    <LogOp> AND </LogOp>
 189    <PRED-LEFT>
 190      <PREDICATE>
 191      <LogOp> AND </LogOp>
 192      <PRED-LEFT>
 193        <PREDICATE>
 194        <LogOp> OR </LogOp>
 195        <PRED-LEFT>
 196          <PREDICATE>
 197          <LogOp> OR </LogOp>
 198          <PRED-LEFT>
 199            <PREDICATE>
 200            <FieldName1> f1 </FieldName1>
 201            <CompOp> Equals </CompOp>
 202            <OperandPtr> VALUE </OperandPtr>
 203            </PREDICATE>
 204          </PRED-LEFT>
 205          <PRED-RIGHT>
 206            <PREDICATE>
 207            <FieldName1> f1 </FieldName1>
 208            <CompOp> Equals </CompOp>
 209            <OperandPtr> VALUE </OperandPtr>
 210            </PREDICATE>
 211          </PRED-RIGHT>
 212          </PREDICATE>
 213        </PRED-LEFT>
 214        <PRED-RIGHT>
 215          <PREDICATE>
 216          <FieldName1> f1 </FieldName1>
 217          <CompOp> Equals </CompOp>
 218          <OperandPtr> VALUE </OperandPtr>
 219          </PREDICATE>
 220        </PRED-RIGHT>
 221        </PREDICATE>
 222      </PRED-LEFT>
 223      <PRED-RIGHT>
 224        <PREDICATE>
 225        <FieldName1> f2 </FieldName1>
 226        <CompOp> Equals </CompOp>
 227        <OperandPtr> VALUE </OperandPtr>
 228        </PREDICATE>
 229      </PRED-RIGHT>
 230      </PREDICATE>
 231    </PRED-LEFT>
 232    <PRED-RIGHT>
 233      <PREDICATE>
 234      <LogOp> NOT </LogOp>
 235      <PRED-LEFT>
 236        <PREDICATE>
 237        <FieldName1> f3 </FieldName1>
 238        <CompOp> Equals </CompOp>
 239        <OperandPtr> VALUE </OperandPtr>
 240        </PREDICATE>
 241      </PRED-LEFT>
 242      </PREDICATE>
 243    </PRED-RIGHT>
 244    </PREDICATE>
 245  </TABLE-NODE>
 246 echo explain plan select * from t1 where f1 in('a','b','c') and f2 like 'a%' and not(f3='d');
 247  <TABLE-NODE>
 248  <NAME> t1 </NAME>
 249  <ScanType> TableScan </ScanType>
 250    <PREDICATE>
 251    <LogOp> AND </LogOp>
 252    <PRED-LEFT>
 253      <PREDICATE>
 254      <LogOp> AND </LogOp>
 255      <PRED-LEFT>
 256        <PREDICATE>
 257        <LogOp> OR </LogOp>
 258        <PRED-LEFT>
 259          <PREDICATE>
 260          <LogOp> OR </LogOp>
 261          <PRED-LEFT>
 262            <PREDICATE>
 263            <FieldName1> f1 </FieldName1>
 264            <CompOp> Equals </CompOp>
 265            <OperandPtr> VALUE </OperandPtr>
 266            </PREDICATE>
 267          </PRED-LEFT>
 268          <PRED-RIGHT>
 269            <PREDICATE>
 270            <FieldName1> f1 </FieldName1>
 271            <CompOp> Equals </CompOp>
 272            <OperandPtr> VALUE </OperandPtr>
 273            </PREDICATE>
 274          </PRED-RIGHT>
 275          </PREDICATE>
 276        </PRED-LEFT>
 277        <PRED-RIGHT>
 278          <PREDICATE>
 279          <FieldName1> f1 </FieldName1>
 280          <CompOp> Equals </CompOp>
 281          <OperandPtr> VALUE </OperandPtr>
 282          </PREDICATE>
 283        </PRED-RIGHT>
 284        </PREDICATE>
 285      </PRED-LEFT>
 286      <PRED-RIGHT>
 287        <PREDICATE>
 288        <FieldName1> f2 </FieldName1>
 289        <CompOp> Like </CompOp>
 290        <OperandPtr> VALUE </OperandPtr>
 291        </PREDICATE>
 292      </PRED-RIGHT>
 293      </PREDICATE>
 294    </PRED-LEFT>
 295    <PRED-RIGHT>
 296      <PREDICATE>
 297      <LogOp> NOT </LogOp>
 298      <PRED-LEFT>
 299        <PREDICATE>
 300        <FieldName1> f3 </FieldName1>
 301        <CompOp> Equals </CompOp>
 302        <OperandPtr> VALUE </OperandPtr>
 303        </PREDICATE>
 304      </PRED-LEFT>
 305      </PREDICATE>
 306    </PRED-RIGHT>
 307    </PREDICATE>
 308  </TABLE-NODE>
 309 echo explain plan select * from t1 where f1 in('b','c','a') or f2 like 'a%' or not(f3='d');
 310  <TABLE-NODE>
 311  <NAME> t1 </NAME>
 312  <ScanType> TableScan </ScanType>
 313    <PREDICATE>
 314    <LogOp> OR </LogOp>
 315    <PRED-LEFT>
 316      <PREDICATE>
 317      <LogOp> OR </LogOp>
 318      <PRED-LEFT>
 319        <PREDICATE>
 320        <LogOp> OR </LogOp>
 321        <PRED-LEFT>
 322          <PREDICATE>
 323          <LogOp> OR </LogOp>
 324          <PRED-LEFT>
 325            <PREDICATE>
 326            <FieldName1> f1 </FieldName1>
 327            <CompOp> Equals </CompOp>
 328            <OperandPtr> VALUE </OperandPtr>
 329            </PREDICATE>
 330          </PRED-LEFT>
 331          <PRED-RIGHT>
 332            <PREDICATE>
 333            <FieldName1> f1 </FieldName1>
 334            <CompOp> Equals </CompOp>
 335            <OperandPtr> VALUE </OperandPtr>
 336            </PREDICATE>
 337          </PRED-RIGHT>
 338          </PREDICATE>
 339        </PRED-LEFT>
 340        <PRED-RIGHT>
 341          <PREDICATE>
 342          <FieldName1> f1 </FieldName1>
 343          <CompOp> Equals </CompOp>
 344          <OperandPtr> VALUE </OperandPtr>
 345          </PREDICATE>
 346        </PRED-RIGHT>
 347        </PREDICATE>
 348      </PRED-LEFT>
 349      <PRED-RIGHT>
 350        <PREDICATE>
 351        <FieldName1> f2 </FieldName1>
 352        <CompOp> Like </CompOp>
 353        <OperandPtr> VALUE </OperandPtr>
 354        </PREDICATE>
 355      </PRED-RIGHT>
 356      </PREDICATE>
 357    </PRED-LEFT>
 358    <PRED-RIGHT>
 359      <PREDICATE>
 360      <LogOp> NOT </LogOp>
 361      <PRED-LEFT>
 362        <PREDICATE>
 363        <FieldName1> f3 </FieldName1>
 364        <CompOp> Equals </CompOp>
 365        <OperandPtr> VALUE </OperandPtr>
 366        </PREDICATE>
 367      </PRED-LEFT>
 368      </PREDICATE>
 369    </PRED-RIGHT>
 370    </PREDICATE>
 371  </TABLE-NODE>
 372 echo explain plan select * from t1 where f1 in('a','c','b') or f2 like 'a%' and not(f3='d');
 373  <TABLE-NODE>
 374  <NAME> t1 </NAME>
 375  <ScanType> TableScan </ScanType>
 376    <PREDICATE>
 377    <LogOp> OR </LogOp>
 378    <PRED-LEFT>
 379      <PREDICATE>
 380      <LogOp> OR </LogOp>
 381      <PRED-LEFT>
 382        <PREDICATE>
 383        <LogOp> OR </LogOp>
 384        <PRED-LEFT>
 385          <PREDICATE>
 386          <FieldName1> f1 </FieldName1>
 387          <CompOp> Equals </CompOp>
 388          <OperandPtr> VALUE </OperandPtr>
 389          </PREDICATE>
 390        </PRED-LEFT>
 391        <PRED-RIGHT>
 392          <PREDICATE>
 393          <FieldName1> f1 </FieldName1>
 394          <CompOp> Equals </CompOp>
 395          <OperandPtr> VALUE </OperandPtr>
 396          </PREDICATE>
 397        </PRED-RIGHT>
 398        </PREDICATE>
 399      </PRED-LEFT>
 400      <PRED-RIGHT>
 401        <PREDICATE>
 402        <FieldName1> f1 </FieldName1>
 403        <CompOp> Equals </CompOp>
 404        <OperandPtr> VALUE </OperandPtr>
 405        </PREDICATE>
 406      </PRED-RIGHT>
 407      </PREDICATE>
 408    </PRED-LEFT>
 409    <PRED-RIGHT>
 410      <PREDICATE>
 411      <LogOp> AND </LogOp>
 412      <PRED-LEFT>
 413        <PREDICATE>
 414        <FieldName1> f2 </FieldName1>
 415        <CompOp> Like </CompOp>
 416        <OperandPtr> VALUE </OperandPtr>
 417        </PREDICATE>
 418      </PRED-LEFT>
 419      <PRED-RIGHT>
 420        <PREDICATE>
 421        <LogOp> NOT </LogOp>
 422        <PRED-LEFT>
 423          <PREDICATE>
 424          <FieldName1> f3 </FieldName1>
 425          <CompOp> Equals </CompOp>
 426          <OperandPtr> VALUE </OperandPtr>
 427          </PREDICATE>
 428        </PRED-LEFT>
 429        </PREDICATE>
 430      </PRED-RIGHT>
 431      </PREDICATE>
 432    </PRED-RIGHT>
 433    </PREDICATE>
 434  </TABLE-NODE>
 435 Statement Executed