test/sql/Optimizer/exp.test004.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 treetiny on t1(f1) tree;
   4 Statement Executed
   5 echo create index treesmall on t1(f2) tree;
   6 Statement Executed
   7 echo create index treeint on t1(f3) tree;
   8 Statement Executed
   9 echo create index treebig on t1(f4) tree;
  10 Statement Executed
  11 echo create index treechar on t1(f5) tree;
  12 Statement Executed
  13 echo create index treevarchar on t1(f6) tree;
  14 Statement Executed
  15 echo create index treedate on t1(f7) tree;
  16 Statement Executed
  17 echo create index treetime on t1(f8) tree;
  18 Statement Executed
  19 Statement Executed: Rows Affected = 1
  20 Statement Executed: Rows Affected = 1
  21 Statement Executed: Rows Affected = 1
  22 Statement Executed: Rows Affected = 1
  23 Statement Executed: Rows Affected = 1
  24 echo explain plan select * from t1;
  25  <TABLE-NODE>
  26  <NAME> t1 </NAME>
  27  <ScanType> TableScan </ScanType>
  28  </TABLE-NODE>
  29 echo explain plan select * from t1 where f1 = 2;
  30  <TABLE-NODE>
  31  <NAME> t1 </NAME>
  32  <ScanType> TreeScan </ScanType>
  33    <PREDICATE>
  34    <FieldName1> f1 </FieldName1>
  35    <CompOp> Equals </CompOp>
  36    <OperandPtr> VALUE </OperandPtr>
  37    </PREDICATE>
  38  </TABLE-NODE>
  39 echo explain plan select * from t1 where f1 < 3;
  40  <TABLE-NODE>
  41  <NAME> t1 </NAME>
  42  <ScanType> TreeScan </ScanType>
  43    <PREDICATE>
  44    <FieldName1> f1 </FieldName1>
  45    <CompOp> LessThan </CompOp>
  46    <OperandPtr> VALUE </OperandPtr>
  47    </PREDICATE>
  48  </TABLE-NODE>
  49 echo explain plan select * from t1 where f2 = 22;
  50  <TABLE-NODE>
  51  <NAME> t1 </NAME>
  52  <ScanType> TreeScan </ScanType>
  53    <PREDICATE>
  54    <FieldName1> f2 </FieldName1>
  55    <CompOp> Equals </CompOp>
  56    <OperandPtr> VALUE </OperandPtr>
  57    </PREDICATE>
  58  </TABLE-NODE>
  59 echo explain plan select * from t1 where f2 > 33;
  60  <TABLE-NODE>
  61  <NAME> t1 </NAME>
  62  <ScanType> TreeScan </ScanType>
  63    <PREDICATE>
  64    <FieldName1> f2 </FieldName1>
  65    <CompOp> GreaterThan </CompOp>
  66    <OperandPtr> VALUE </OperandPtr>
  67    </PREDICATE>
  68  </TABLE-NODE>
  69 echo explain plan select * from t1 where f3 = 333;
  70  <TABLE-NODE>
  71  <NAME> t1 </NAME>
  72  <ScanType> TreeScan </ScanType>
  73    <PREDICATE>
  74    <FieldName1> f3 </FieldName1>
  75    <CompOp> Equals </CompOp>
  76    <OperandPtr> VALUE </OperandPtr>
  77    </PREDICATE>
  78  </TABLE-NODE>
  79 echo explain plan select * from t1 where f3 <> 333;
  80  <TABLE-NODE>
  81  <NAME> t1 </NAME>
  82  <ScanType> TableScan </ScanType>
  83    <PREDICATE>
  84    <FieldName1> f3 </FieldName1>
  85    <CompOp> NotEquals </CompOp>
  86    <OperandPtr> VALUE </OperandPtr>
  87    </PREDICATE>
  88  </TABLE-NODE>
  89 echo explain plan select * from t1 where f4 = 4444;
  90  <TABLE-NODE>
  91  <NAME> t1 </NAME>
  92  <ScanType> TreeScan </ScanType>
  93    <PREDICATE>
  94    <FieldName1> f4 </FieldName1>
  95    <CompOp> Equals </CompOp>
  96    <OperandPtr> VALUE </OperandPtr>
  97    </PREDICATE>
  98  </TABLE-NODE>
  99 echo explain plan select * from t1 where f4 != 4444;
 100  <TABLE-NODE>
 101  <NAME> t1 </NAME>
 102  <ScanType> TableScan </ScanType>
 103    <PREDICATE>
 104    <FieldName1> f4 </FieldName1>
 105    <CompOp> NotEquals </CompOp>
 106    <OperandPtr> VALUE </OperandPtr>
 107    </PREDICATE>
 108  </TABLE-NODE>
 109 echo explain plan select * from t1 where f5 = 'CSQL3';
 110  <TABLE-NODE>
 111  <NAME> t1 </NAME>
 112  <ScanType> TreeScan </ScanType>
 113    <PREDICATE>
 114    <FieldName1> f5 </FieldName1>
 115    <CompOp> Equals </CompOp>
 116    <OperandPtr> VALUE </OperandPtr>
 117    </PREDICATE>
 118  </TABLE-NODE>
 119 echo explain plan select * from t1 where f5 <= 'CSQL3';
 120  <TABLE-NODE>
 121  <NAME> t1 </NAME>
 122  <ScanType> TreeScan </ScanType>
 123    <PREDICATE>
 124    <FieldName1> f5 </FieldName1>
 125    <CompOp> LessThanEquals </CompOp>
 126    <OperandPtr> VALUE </OperandPtr>
 127    </PREDICATE>
 128  </TABLE-NODE>
 129 echo explain plan select * from t1 where f6 = 'Lakshya3';
 130  <TABLE-NODE>
 131  <NAME> t1 </NAME>
 132  <ScanType> TreeScan </ScanType>
 133    <PREDICATE>
 134    <FieldName1> f6 </FieldName1>
 135    <CompOp> Equals </CompOp>
 136    <OperandPtr> VALUE </OperandPtr>
 137    </PREDICATE>
 138  </TABLE-NODE>
 139 echo explain plan select * from t1 where f6 >= 'Lakshya3';
 140  <TABLE-NODE>
 141  <NAME> t1 </NAME>
 142  <ScanType> TreeScan </ScanType>
 143    <PREDICATE>
 144    <FieldName1> f6 </FieldName1>
 145    <CompOp> GreaterThanEquals </CompOp>
 146    <OperandPtr> VALUE </OperandPtr>
 147    </PREDICATE>
 148  </TABLE-NODE>
 149 echo explain plan select * from t1 where f7 = '2003-11-12';
 150  <TABLE-NODE>
 151  <NAME> t1 </NAME>
 152  <ScanType> TreeScan </ScanType>
 153    <PREDICATE>
 154    <FieldName1> f7 </FieldName1>
 155    <CompOp> Equals </CompOp>
 156    <OperandPtr> VALUE </OperandPtr>
 157    </PREDICATE>
 158  </TABLE-NODE>
 159 echo explain plan select * from t1 where f7 > '2003-11-12';
 160  <TABLE-NODE>
 161  <NAME> t1 </NAME>
 162  <ScanType> TreeScan </ScanType>
 163    <PREDICATE>
 164    <FieldName1> f7 </FieldName1>
 165    <CompOp> GreaterThan </CompOp>
 166    <OperandPtr> VALUE </OperandPtr>
 167    </PREDICATE>
 168  </TABLE-NODE>
 169 echo explain plan select * from t1 where f8 = '03:21:00';
 170  <TABLE-NODE>
 171  <NAME> t1 </NAME>
 172  <ScanType> TreeScan </ScanType>
 173    <PREDICATE>
 174    <FieldName1> f8 </FieldName1>
 175    <CompOp> Equals </CompOp>
 176    <OperandPtr> VALUE </OperandPtr>
 177    </PREDICATE>
 178  </TABLE-NODE>
 179 echo explain plan select * from t1 where f8 < '03:21:00';
 180  <TABLE-NODE>
 181  <NAME> t1 </NAME>
 182  <ScanType> TreeScan </ScanType>
 183    <PREDICATE>
 184    <FieldName1> f8 </FieldName1>
 185    <CompOp> LessThan </CompOp>
 186    <OperandPtr> VALUE </OperandPtr>
 187    </PREDICATE>
 188  </TABLE-NODE>
 189 Statement Executed