test/sql/Optimizer/exp.test007.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 = 2 and f1 in (0,2,4) ;
  25  <TABLE-NODE>
  26  <NAME> t1 </NAME>
  27  <ScanType> TableScan </ScanType>
  28    <PREDICATE>
  29    <LogOp> AND </LogOp>
  30    <PRED-LEFT>
  31      <PREDICATE>
  32      <FieldName1> f1 </FieldName1>
  33      <CompOp> Equals </CompOp>
  34      <OperandPtr> VALUE </OperandPtr>
  35      </PREDICATE>
  36    </PRED-LEFT>
  37    <PRED-RIGHT>
  38      <PREDICATE>
  39      <LogOp> OR </LogOp>
  40      <PRED-LEFT>
  41        <PREDICATE>
  42        <LogOp> OR </LogOp>
  43        <PRED-LEFT>
  44          <PREDICATE>
  45          <FieldName1> f1 </FieldName1>
  46          <CompOp> Equals </CompOp>
  47          <OperandPtr> VALUE </OperandPtr>
  48          </PREDICATE>
  49        </PRED-LEFT>
  50        <PRED-RIGHT>
  51          <PREDICATE>
  52          <FieldName1> f1 </FieldName1>
  53          <CompOp> Equals </CompOp>
  54          <OperandPtr> VALUE </OperandPtr>
  55          </PREDICATE>
  56        </PRED-RIGHT>
  57        </PREDICATE>
  58      </PRED-LEFT>
  59      <PRED-RIGHT>
  60        <PREDICATE>
  61        <FieldName1> f1 </FieldName1>
  62        <CompOp> Equals </CompOp>
  63        <OperandPtr> VALUE </OperandPtr>
  64        </PREDICATE>
  65      </PRED-RIGHT>
  66      </PREDICATE>
  67    </PRED-RIGHT>
  68    </PREDICATE>
  69  </TABLE-NODE>
  70 echo explain plan select * from t1 where f1 = 3 or (f1  between 2 and 4);
  71  <TABLE-NODE>
  72  <NAME> t1 </NAME>
  73  <ScanType> TableScan </ScanType>
  74    <PREDICATE>
  75    <LogOp> OR </LogOp>
  76    <PRED-LEFT>
  77      <PREDICATE>
  78      <FieldName1> f1 </FieldName1>
  79      <CompOp> Equals </CompOp>
  80      <OperandPtr> VALUE </OperandPtr>
  81      </PREDICATE>
  82    </PRED-LEFT>
  83    <PRED-RIGHT>
  84      <PREDICATE>
  85      <FieldName1> f1 </FieldName1>
  86      <CompOp> GreaterThanEquals </CompOp>
  87      <OperandPtr> VALUE </OperandPtr>
  88      <Comp2Op> LessThanEquals </Comp2Op>
  89      <Operand2Ptr> VALUE </Operand2Ptr>
  90      </PREDICATE>
  91    </PRED-RIGHT>
  92    </PREDICATE>
  93  </TABLE-NODE>
  94 echo explain plan select * from t1 where f2 in (0,2,4) and f2 = 2;
  95  <TABLE-NODE>
  96  <NAME> t1 </NAME>
  97  <ScanType> TableScan </ScanType>
  98    <PREDICATE>
  99    <LogOp> AND </LogOp>
 100    <PRED-LEFT>
 101      <PREDICATE>
 102      <LogOp> OR </LogOp>
 103      <PRED-LEFT>
 104        <PREDICATE>
 105        <LogOp> OR </LogOp>
 106        <PRED-LEFT>
 107          <PREDICATE>
 108          <FieldName1> f2 </FieldName1>
 109          <CompOp> Equals </CompOp>
 110          <OperandPtr> VALUE </OperandPtr>
 111          </PREDICATE>
 112        </PRED-LEFT>
 113        <PRED-RIGHT>
 114          <PREDICATE>
 115          <FieldName1> f2 </FieldName1>
 116          <CompOp> Equals </CompOp>
 117          <OperandPtr> VALUE </OperandPtr>
 118          </PREDICATE>
 119        </PRED-RIGHT>
 120        </PREDICATE>
 121      </PRED-LEFT>
 122      <PRED-RIGHT>
 123        <PREDICATE>
 124        <FieldName1> f2 </FieldName1>
 125        <CompOp> Equals </CompOp>
 126        <OperandPtr> VALUE </OperandPtr>
 127        </PREDICATE>
 128      </PRED-RIGHT>
 129      </PREDICATE>
 130    </PRED-LEFT>
 131    <PRED-RIGHT>
 132      <PREDICATE>
 133      <FieldName1> f2 </FieldName1>
 134      <CompOp> Equals </CompOp>
 135      <OperandPtr> VALUE </OperandPtr>
 136      </PREDICATE>
 137    </PRED-RIGHT>
 138    </PREDICATE>
 139  </TABLE-NODE>
 140 echo explain plan select * from t1 where (f2 between 2 and 4) or f2 = 2;
 141  <TABLE-NODE>
 142  <NAME> t1 </NAME>
 143  <ScanType> TableScan </ScanType>
 144    <PREDICATE>
 145    <LogOp> OR </LogOp>
 146    <PRED-LEFT>
 147      <PREDICATE>
 148      <FieldName1> f2 </FieldName1>
 149      <CompOp> GreaterThanEquals </CompOp>
 150      <OperandPtr> VALUE </OperandPtr>
 151      <Comp2Op> LessThanEquals </Comp2Op>
 152      <Operand2Ptr> VALUE </Operand2Ptr>
 153      </PREDICATE>
 154    </PRED-LEFT>
 155    <PRED-RIGHT>
 156      <PREDICATE>
 157      <FieldName1> f2 </FieldName1>
 158      <CompOp> Equals </CompOp>
 159      <OperandPtr> VALUE </OperandPtr>
 160      </PREDICATE>
 161    </PRED-RIGHT>
 162    </PREDICATE>
 163  </TABLE-NODE>
 164 echo explain plan select * from t1 where f3 = 2 or f3 in (1,2) ;
 165  <TABLE-NODE>
 166  <NAME> t1 </NAME>
 167  <ScanType> TableScan </ScanType>
 168    <PREDICATE>
 169    <LogOp> OR </LogOp>
 170    <PRED-LEFT>
 171      <PREDICATE>
 172      <FieldName1> f3 </FieldName1>
 173      <CompOp> Equals </CompOp>
 174      <OperandPtr> VALUE </OperandPtr>
 175      </PREDICATE>
 176    </PRED-LEFT>
 177    <PRED-RIGHT>
 178      <PREDICATE>
 179      <LogOp> OR </LogOp>
 180      <PRED-LEFT>
 181        <PREDICATE>
 182        <FieldName1> f3 </FieldName1>
 183        <CompOp> Equals </CompOp>
 184        <OperandPtr> VALUE </OperandPtr>
 185        </PREDICATE>
 186      </PRED-LEFT>
 187      <PRED-RIGHT>
 188        <PREDICATE>
 189        <FieldName1> f3 </FieldName1>
 190        <CompOp> Equals </CompOp>
 191        <OperandPtr> VALUE </OperandPtr>
 192        </PREDICATE>
 193      </PRED-RIGHT>
 194      </PREDICATE>
 195    </PRED-RIGHT>
 196    </PREDICATE>
 197  </TABLE-NODE>
 198 echo explain plan select * from t1 where f3 = 2 and f3 between 1 and 4;
 199  <TABLE-NODE>
 200  <NAME> t1 </NAME>
 201  <ScanType> HashScan </ScanType>
 202    <PREDICATE>
 203    <LogOp> AND </LogOp>
 204    <PRED-LEFT>
 205      <PREDICATE>
 206      <FieldName1> f3 </FieldName1>
 207      <CompOp> Equals </CompOp>
 208      <OperandPtr> VALUE </OperandPtr>
 209      </PREDICATE>
 210    </PRED-LEFT>
 211    <PRED-RIGHT>
 212      <PREDICATE>
 213      <FieldName1> f3 </FieldName1>
 214      <CompOp> GreaterThanEquals </CompOp>
 215      <OperandPtr> VALUE </OperandPtr>
 216      <Comp2Op> LessThanEquals </Comp2Op>
 217      <Operand2Ptr> VALUE </Operand2Ptr>
 218      </PREDICATE>
 219    </PRED-RIGHT>
 220    </PREDICATE>
 221  </TABLE-NODE>
 222 echo explain plan select * from t1 where f4 in (1,2) or f4 = 2;
 223  <TABLE-NODE>
 224  <NAME> t1 </NAME>
 225  <ScanType> TableScan </ScanType>
 226    <PREDICATE>
 227    <LogOp> OR </LogOp>
 228    <PRED-LEFT>
 229      <PREDICATE>
 230      <LogOp> OR </LogOp>
 231      <PRED-LEFT>
 232        <PREDICATE>
 233        <FieldName1> f4 </FieldName1>
 234        <CompOp> Equals </CompOp>
 235        <OperandPtr> VALUE </OperandPtr>
 236        </PREDICATE>
 237      </PRED-LEFT>
 238      <PRED-RIGHT>
 239        <PREDICATE>
 240        <FieldName1> f4 </FieldName1>
 241        <CompOp> Equals </CompOp>
 242        <OperandPtr> VALUE </OperandPtr>
 243        </PREDICATE>
 244      </PRED-RIGHT>
 245      </PREDICATE>
 246    </PRED-LEFT>
 247    <PRED-RIGHT>
 248      <PREDICATE>
 249      <FieldName1> f4 </FieldName1>
 250      <CompOp> Equals </CompOp>
 251      <OperandPtr> VALUE </OperandPtr>
 252      </PREDICATE>
 253    </PRED-RIGHT>
 254    </PREDICATE>
 255  </TABLE-NODE>
 256 echo explain plan select * from t1 where (f4 between 1 and 4) and f4 = 2;
 257  <TABLE-NODE>
 258  <NAME> t1 </NAME>
 259  <ScanType> HashScan </ScanType>
 260    <PREDICATE>
 261    <LogOp> AND </LogOp>
 262    <PRED-LEFT>
 263      <PREDICATE>
 264      <FieldName1> f4 </FieldName1>
 265      <CompOp> GreaterThanEquals </CompOp>
 266      <OperandPtr> VALUE </OperandPtr>
 267      <Comp2Op> LessThanEquals </Comp2Op>
 268      <Operand2Ptr> VALUE </Operand2Ptr>
 269      </PREDICATE>
 270    </PRED-LEFT>
 271    <PRED-RIGHT>
 272      <PREDICATE>
 273      <FieldName1> f4 </FieldName1>
 274      <CompOp> Equals </CompOp>
 275      <OperandPtr> VALUE </OperandPtr>
 276      </PREDICATE>
 277    </PRED-RIGHT>
 278    </PREDICATE>
 279  </TABLE-NODE>
 280 echo explain plan select * from t1 where f5 in ('1','2') and f5 = '1';
 281  <TABLE-NODE>
 282  <NAME> t1 </NAME>
 283  <ScanType> TableScan </ScanType>
 284    <PREDICATE>
 285    <LogOp> AND </LogOp>
 286    <PRED-LEFT>
 287      <PREDICATE>
 288      <LogOp> OR </LogOp>
 289      <PRED-LEFT>
 290        <PREDICATE>
 291        <FieldName1> f5 </FieldName1>
 292        <CompOp> Equals </CompOp>
 293        <OperandPtr> VALUE </OperandPtr>
 294        </PREDICATE>
 295      </PRED-LEFT>
 296      <PRED-RIGHT>
 297        <PREDICATE>
 298        <FieldName1> f5 </FieldName1>
 299        <CompOp> Equals </CompOp>
 300        <OperandPtr> VALUE </OperandPtr>
 301        </PREDICATE>
 302      </PRED-RIGHT>
 303      </PREDICATE>
 304    </PRED-LEFT>
 305    <PRED-RIGHT>
 306      <PREDICATE>
 307      <FieldName1> f5 </FieldName1>
 308      <CompOp> Equals </CompOp>
 309      <OperandPtr> VALUE </OperandPtr>
 310      </PREDICATE>
 311    </PRED-RIGHT>
 312    </PREDICATE>
 313  </TABLE-NODE>
 314 echo explain plan select * from t1 where f5 like 'N%' or f5 = 'NP';
 315  <TABLE-NODE>
 316  <NAME> t1 </NAME>
 317  <ScanType> TableScan </ScanType>
 318    <PREDICATE>
 319    <LogOp> OR </LogOp>
 320    <PRED-LEFT>
 321      <PREDICATE>
 322      <FieldName1> f5 </FieldName1>
 323      <CompOp> Like </CompOp>
 324      <OperandPtr> VALUE </OperandPtr>
 325      </PREDICATE>
 326    </PRED-LEFT>
 327    <PRED-RIGHT>
 328      <PREDICATE>
 329      <FieldName1> f5 </FieldName1>
 330      <CompOp> Equals </CompOp>
 331      <OperandPtr> VALUE </OperandPtr>
 332      </PREDICATE>
 333    </PRED-RIGHT>
 334    </PREDICATE>
 335  </TABLE-NODE>
 336 echo explain plan select * from t1 where f5 like 'N_h' and f6 = 'Nihar';
 337  <TABLE-NODE>
 338  <NAME> t1 </NAME>
 339  <ScanType> HashScan </ScanType>
 340    <PREDICATE>
 341    <LogOp> AND </LogOp>
 342    <PRED-LEFT>
 343      <PREDICATE>
 344      <FieldName1> f5 </FieldName1>
 345      <CompOp> Like </CompOp>
 346      <OperandPtr> VALUE </OperandPtr>
 347      </PREDICATE>
 348    </PRED-LEFT>
 349    <PRED-RIGHT>
 350      <PREDICATE>
 351      <FieldName1> f6 </FieldName1>
 352      <CompOp> Equals </CompOp>
 353      <OperandPtr> VALUE </OperandPtr>
 354      </PREDICATE>
 355    </PRED-RIGHT>
 356    </PREDICATE>
 357  </TABLE-NODE>
 358 echo explain plan select * from t1 where f6 = '1' or f6 in ('1','2');
 359  <TABLE-NODE>
 360  <NAME> t1 </NAME>
 361  <ScanType> TableScan </ScanType>
 362    <PREDICATE>
 363    <LogOp> OR </LogOp>
 364    <PRED-LEFT>
 365      <PREDICATE>
 366      <FieldName1> f6 </FieldName1>
 367      <CompOp> Equals </CompOp>
 368      <OperandPtr> VALUE </OperandPtr>
 369      </PREDICATE>
 370    </PRED-LEFT>
 371    <PRED-RIGHT>
 372      <PREDICATE>
 373      <LogOp> OR </LogOp>
 374      <PRED-LEFT>
 375        <PREDICATE>
 376        <FieldName1> f6 </FieldName1>
 377        <CompOp> Equals </CompOp>
 378        <OperandPtr> VALUE </OperandPtr>
 379        </PREDICATE>
 380      </PRED-LEFT>
 381      <PRED-RIGHT>
 382        <PREDICATE>
 383        <FieldName1> f6 </FieldName1>
 384        <CompOp> Equals </CompOp>
 385        <OperandPtr> VALUE </OperandPtr>
 386        </PREDICATE>
 387      </PRED-RIGHT>
 388      </PREDICATE>
 389    </PRED-RIGHT>
 390    </PREDICATE>
 391  </TABLE-NODE>
 392 echo explain plan select * from t1 where f6 = 'NP' or f6 like 'N%';
 393  <TABLE-NODE>
 394  <NAME> t1 </NAME>
 395  <ScanType> TableScan </ScanType>
 396    <PREDICATE>
 397    <LogOp> OR </LogOp>
 398    <PRED-LEFT>
 399      <PREDICATE>
 400      <FieldName1> f6 </FieldName1>
 401      <CompOp> Equals </CompOp>
 402      <OperandPtr> VALUE </OperandPtr>
 403      </PREDICATE>
 404    </PRED-LEFT>
 405    <PRED-RIGHT>
 406      <PREDICATE>
 407      <FieldName1> f6 </FieldName1>
 408      <CompOp> Like </CompOp>
 409      <OperandPtr> VALUE </OperandPtr>
 410      </PREDICATE>
 411    </PRED-RIGHT>
 412    </PREDICATE>
 413  </TABLE-NODE>
 414 echo explain plan select * from t1 where f6 like 'N_h' and f6 = 'NIHAR';
 415  <TABLE-NODE>
 416  <NAME> t1 </NAME>
 417  <ScanType> HashScan </ScanType>
 418    <PREDICATE>
 419    <LogOp> AND </LogOp>
 420    <PRED-LEFT>
 421      <PREDICATE>
 422      <FieldName1> f6 </FieldName1>
 423      <CompOp> Like </CompOp>
 424      <OperandPtr> VALUE </OperandPtr>
 425      </PREDICATE>
 426    </PRED-LEFT>
 427    <PRED-RIGHT>
 428      <PREDICATE>
 429      <FieldName1> f6 </FieldName1>
 430      <CompOp> Equals </CompOp>
 431      <OperandPtr> VALUE </OperandPtr>
 432      </PREDICATE>
 433    </PRED-RIGHT>
 434    </PREDICATE>
 435  </TABLE-NODE>
 436 echo explain plan select * from t1 where f7 = '2003-11-12' or f7 in ('2003-11-12','2003-11-15');
 437  <TABLE-NODE>
 438  <NAME> t1 </NAME>
 439  <ScanType> TableScan </ScanType>
 440    <PREDICATE>
 441    <LogOp> OR </LogOp>
 442    <PRED-LEFT>
 443      <PREDICATE>
 444      <FieldName1> f7 </FieldName1>
 445      <CompOp> Equals </CompOp>
 446      <OperandPtr> VALUE </OperandPtr>
 447      </PREDICATE>
 448    </PRED-LEFT>
 449    <PRED-RIGHT>
 450      <PREDICATE>
 451      <LogOp> OR </LogOp>
 452      <PRED-LEFT>
 453        <PREDICATE>
 454        <FieldName1> f7 </FieldName1>
 455        <CompOp> Equals </CompOp>
 456        <OperandPtr> VALUE </OperandPtr>
 457        </PREDICATE>
 458      </PRED-LEFT>
 459      <PRED-RIGHT>
 460        <PREDICATE>
 461        <FieldName1> f7 </FieldName1>
 462        <CompOp> Equals </CompOp>
 463        <OperandPtr> VALUE </OperandPtr>
 464        </PREDICATE>
 465      </PRED-RIGHT>
 466      </PREDICATE>
 467    </PRED-RIGHT>
 468    </PREDICATE>
 469  </TABLE-NODE>
 470 echo explain plan select * from t1 where f7 = '2003-11-15' and (f7 between '2003-11-12' and '2003-11-15');
 471  <TABLE-NODE>
 472  <NAME> t1 </NAME>
 473  <ScanType> HashScan </ScanType>
 474    <PREDICATE>
 475    <LogOp> AND </LogOp>
 476    <PRED-LEFT>
 477      <PREDICATE>
 478      <FieldName1> f7 </FieldName1>
 479      <CompOp> Equals </CompOp>
 480      <OperandPtr> VALUE </OperandPtr>
 481      </PREDICATE>
 482    </PRED-LEFT>
 483    <PRED-RIGHT>
 484      <PREDICATE>
 485      <FieldName1> f7 </FieldName1>
 486      <CompOp> GreaterThanEquals </CompOp>
 487      <OperandPtr> VALUE </OperandPtr>
 488      <Comp2Op> LessThanEquals </Comp2Op>
 489      <Operand2Ptr> VALUE </Operand2Ptr>
 490      </PREDICATE>
 491    </PRED-RIGHT>
 492    </PREDICATE>
 493  </TABLE-NODE>
 494 echo explain plan select * from t1 where f8 in ('03:21:00','03:21:08') and f8 = '03:21:08';
 495  <TABLE-NODE>
 496  <NAME> t1 </NAME>
 497  <ScanType> TableScan </ScanType>
 498    <PREDICATE>
 499    <LogOp> AND </LogOp>
 500    <PRED-LEFT>
 501      <PREDICATE>
 502      <LogOp> OR </LogOp>
 503      <PRED-LEFT>
 504        <PREDICATE>
 505        <FieldName1> f8 </FieldName1>
 506        <CompOp> Equals </CompOp>
 507        <OperandPtr> VALUE </OperandPtr>
 508        </PREDICATE>
 509      </PRED-LEFT>
 510      <PRED-RIGHT>
 511        <PREDICATE>
 512        <FieldName1> f8 </FieldName1>
 513        <CompOp> Equals </CompOp>
 514        <OperandPtr> VALUE </OperandPtr>
 515        </PREDICATE>
 516      </PRED-RIGHT>
 517      </PREDICATE>
 518    </PRED-LEFT>
 519    <PRED-RIGHT>
 520      <PREDICATE>
 521      <FieldName1> f8 </FieldName1>
 522      <CompOp> Equals </CompOp>
 523      <OperandPtr> VALUE </OperandPtr>
 524      </PREDICATE>
 525    </PRED-RIGHT>
 526    </PREDICATE>
 527  </TABLE-NODE>
 528 echo explain plan select * from t1 where (f8 between '03:21:00' and '03:21:08') or f8='03:21:00';
 529  <TABLE-NODE>
 530  <NAME> t1 </NAME>
 531  <ScanType> TableScan </ScanType>
 532    <PREDICATE>
 533    <LogOp> OR </LogOp>
 534    <PRED-LEFT>
 535      <PREDICATE>
 536      <FieldName1> f8 </FieldName1>
 537      <CompOp> GreaterThanEquals </CompOp>
 538      <OperandPtr> VALUE </OperandPtr>
 539      <Comp2Op> LessThanEquals </Comp2Op>
 540      <Operand2Ptr> VALUE </Operand2Ptr>
 541      </PREDICATE>
 542    </PRED-LEFT>
 543    <PRED-RIGHT>
 544      <PREDICATE>
 545      <FieldName1> f8 </FieldName1>
 546      <CompOp> Equals </CompOp>
 547      <OperandPtr> VALUE </OperandPtr>
 548      </PREDICATE>
 549    </PRED-RIGHT>
 550    </PREDICATE>
 551  </TABLE-NODE>
 552 Statement Executed