test/sql/Optimizer/exp.test020.ksh

   1 echo create table t1(f1 int, f2 int);
   2 Statement Executed
   3 echo create index hashidxt1f1 on t1(f1) hash;
   4 Statement Executed
   5 echo create index hashidxt1f2 on t1(f2) hash;
   6 Statement Executed
   7 echo create table t2(f1 int, f2 int);
   8 Statement Executed
   9 echo create index hashidxt2f1 on t2(f1) hash;
  10 Statement Executed
  11 echo create index hashidxt2f2 on t2(f2) hash;
  12 Statement Executed
  13 echo explain plan select * from t1,t2;
  14  <JOIN-NODE>
  15  <TYPE> INNER_JOIN </TYPE>
  16  <LEFT>
  17    <TABLE-NODE>
  18    <NAME> t1 </NAME>
  19    <ScanType> TableScan </ScanType>
  20    </TABLE-NODE>
  21  </LEFT>
  22  <RIGHT>
  23    <TABLE-NODE>
  24    <NAME> t2 </NAME>
  25    <ScanType> TableScan </ScanType>
  26    </TABLE-NODE>
  27  </RIGHT>
  28  </JOIN-NODE>
  29 echo explain plan select * from t1 left join t2 on t1.f1=t2.f1;
  30  <JOIN-NODE>
  31  <TYPE> LEFT_JOIN </TYPE>
  32  <PREDICATE>
  33  <FieldName1> t1.f1 </FieldName1>
  34  <FieldName2> t2.f1 </FieldName2>
  35  <CompOp> Equals </CompOp>
  36  </PREDICATE>
  37  <LEFT>
  38    <TABLE-NODE>
  39    <NAME> t1 </NAME>
  40    <ScanType> TableScan </ScanType>
  41    </TABLE-NODE>
  42  </LEFT>
  43  <RIGHT>
  44    <TABLE-NODE>
  45    <NAME> t2 </NAME>
  46    <ScanType> TableScan </ScanType>
  47    </TABLE-NODE>
  48  </RIGHT>
  49  </JOIN-NODE>
  50 echo explain plan select * from t1 left join t2 on t1.f1<=t2.f1;
  51  <JOIN-NODE>
  52  <TYPE> LEFT_JOIN </TYPE>
  53  <PREDICATE>
  54  <FieldName1> t1.f1 </FieldName1>
  55  <FieldName2> t2.f1 </FieldName2>
  56  <CompOp> LessThanEquals </CompOp>
  57  </PREDICATE>
  58  <LEFT>
  59    <TABLE-NODE>
  60    <NAME> t1 </NAME>
  61    <ScanType> TableScan </ScanType>
  62    </TABLE-NODE>
  63  </LEFT>
  64  <RIGHT>
  65    <TABLE-NODE>
  66    <NAME> t2 </NAME>
  67    <ScanType> TableScan </ScanType>
  68    </TABLE-NODE>
  69  </RIGHT>
  70  </JOIN-NODE>
  71 echo explain plan select * from t1 left join t2 on t1.f1!=t2.f1;
  72  <JOIN-NODE>
  73  <TYPE> LEFT_JOIN </TYPE>
  74  <PREDICATE>
  75  <FieldName1> t1.f1 </FieldName1>
  76  <FieldName2> t2.f1 </FieldName2>
  77  <CompOp> NotEquals </CompOp>
  78  </PREDICATE>
  79  <LEFT>
  80    <TABLE-NODE>
  81    <NAME> t1 </NAME>
  82    <ScanType> TableScan </ScanType>
  83    </TABLE-NODE>
  84  </LEFT>
  85  <RIGHT>
  86    <TABLE-NODE>
  87    <NAME> t2 </NAME>
  88    <ScanType> TableScan </ScanType>
  89    </TABLE-NODE>
  90  </RIGHT>
  91  </JOIN-NODE>
  92 Statement Executed
  93 Statement Executed