test/sql/Optimizer/exp.test017.ksh

   1 echo create table t1(f1 int, f2 int, f3 varchar(20),f4 date);
   2 Statement Executed
   3 echo create index idx on t1(f3,f4,f2) hash;
   4 Statement Executed
   5 echo explain plan select * from t1 where f2 is null;
   6  <TABLE-NODE>
   7  <NAME> t1 </NAME>
   8  <ScanType> TableScan </ScanType>
   9    <PREDICATE>
  10    <FieldName1> f2 </FieldName1>
  11    <CompOp> isNULL </CompOp>
  12    </PREDICATE>
  13  </TABLE-NODE>
  14 echo explain plan select * from t1 where f2 is not null;
  15  <TABLE-NODE>
  16  <NAME> t1 </NAME>
  17  <ScanType> TableScan </ScanType>
  18    <PREDICATE>
  19    <FieldName1> f2 </FieldName1>
  20    <CompOp> isNULL </CompOp>
  21    </PREDICATE>
  22  </TABLE-NODE>
  23 echo explain plan select * from t1 where f2 is null and f3 is not null;
  24  <TABLE-NODE>
  25  <NAME> t1 </NAME>
  26  <ScanType> TableScan </ScanType>
  27    <PREDICATE>
  28    <LogOp> AND </LogOp>
  29    <PRED-LEFT>
  30      <PREDICATE>
  31      <FieldName1> f2 </FieldName1>
  32      <CompOp> isNULL </CompOp>
  33      </PREDICATE>
  34    </PRED-LEFT>
  35    <PRED-RIGHT>
  36      <PREDICATE>
  37      <FieldName1> f3 </FieldName1>
  38      <CompOp> isNULL </CompOp>
  39      </PREDICATE>
  40    </PRED-RIGHT>
  41    </PREDICATE>
  42  </TABLE-NODE>
  43 echo explain plan select * from t1 where f1 is null or f2 is not null or f3 is null or f4 is not null;
  44  <TABLE-NODE>
  45  <NAME> t1 </NAME>
  46  <ScanType> TableScan </ScanType>
  47    <PREDICATE>
  48    <LogOp> OR </LogOp>
  49    <PRED-LEFT>
  50      <PREDICATE>
  51      <LogOp> OR </LogOp>
  52      <PRED-LEFT>
  53        <PREDICATE>
  54        <LogOp> OR </LogOp>
  55        <PRED-LEFT>
  56          <PREDICATE>
  57          <FieldName1> f1 </FieldName1>
  58          <CompOp> isNULL </CompOp>
  59          </PREDICATE>
  60        </PRED-LEFT>
  61        <PRED-RIGHT>
  62          <PREDICATE>
  63          <FieldName1> f2 </FieldName1>
  64          <CompOp> isNULL </CompOp>
  65          </PREDICATE>
  66        </PRED-RIGHT>
  67        </PREDICATE>
  68      </PRED-LEFT>
  69      <PRED-RIGHT>
  70        <PREDICATE>
  71        <FieldName1> f3 </FieldName1>
  72        <CompOp> isNULL </CompOp>
  73        </PREDICATE>
  74      </PRED-RIGHT>
  75      </PREDICATE>
  76    </PRED-LEFT>
  77    <PRED-RIGHT>
  78      <PREDICATE>
  79      <FieldName1> f4 </FieldName1>
  80      <CompOp> isNULL </CompOp>
  81      </PREDICATE>
  82    </PRED-RIGHT>
  83    </PREDICATE>
  84  </TABLE-NODE>
  85 echo explain plan select * from t1 where f1 is null and f2 is not null and f3 is null and f4 is not null;
  86  <TABLE-NODE>
  87  <NAME> t1 </NAME>
  88  <ScanType> TableScan </ScanType>
  89    <PREDICATE>
  90    <LogOp> AND </LogOp>
  91    <PRED-LEFT>
  92      <PREDICATE>
  93      <LogOp> AND </LogOp>
  94      <PRED-LEFT>
  95        <PREDICATE>
  96        <LogOp> AND </LogOp>
  97        <PRED-LEFT>
  98          <PREDICATE>
  99          <FieldName1> f1 </FieldName1>
 100          <CompOp> isNULL </CompOp>
 101          </PREDICATE>
 102        </PRED-LEFT>
 103        <PRED-RIGHT>
 104          <PREDICATE>
 105          <FieldName1> f2 </FieldName1>
 106          <CompOp> isNULL </CompOp>
 107          </PREDICATE>
 108        </PRED-RIGHT>
 109        </PREDICATE>
 110      </PRED-LEFT>
 111      <PRED-RIGHT>
 112        <PREDICATE>
 113        <FieldName1> f3 </FieldName1>
 114        <CompOp> isNULL </CompOp>
 115        </PREDICATE>
 116      </PRED-RIGHT>
 117      </PREDICATE>
 118    </PRED-LEFT>
 119    <PRED-RIGHT>
 120      <PREDICATE>
 121      <FieldName1> f4 </FieldName1>
 122      <CompOp> isNULL </CompOp>
 123      </PREDICATE>
 124    </PRED-RIGHT>
 125    </PREDICATE>
 126  </TABLE-NODE>
 127 Statement Executed