test/coveridxscan.test

   1 # 2012 September 17
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # Tests for the optimization which attempts to use a covering index
  13 # for a full-table scan (under the theory that the index will be smaller
  14 # and require less I/O and hence will run faster.)
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 set testprefix coveridxscan
  21
  22 do_test 1.1 {
  23   db eval {
  24     CREATE TABLE t1(a,b,c);
  25     INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1);
  26     CREATE INDEX t1ab ON t1(a,b);
  27     CREATE INDEX t1b ON t1(b);
  28     SELECT a FROM t1;
  29   }
  30   # covering index used for the scan, hence values are increasing
  31 } {3 4 5}
  32
  33 do_test 1.2 {
  34   db eval {
  35     SELECT a, c FROM t1;
  36   }
  37   # There is no covering index, hence the values are in rowid order
  38 } {5 3 4 2 3 1}
  39
  40 do_test 1.3 {
  41   db eval {
  42     SELECT b FROM t1;
  43   }
  44   # Choice of two indices: use the one with fewest columns
  45 } {2 4 8}
  46
  47 do_test 2.1 {
  48   optimization_control db cover-idx-scan 0
  49   db eval {SELECT a FROM t1}
  50   # With the optimization turned off, output in rowid order
  51 } {5 4 3}
  52 do_test 2.2 {
  53   db eval {SELECT a, c FROM t1}
  54 } {5 3 4 2 3 1}
  55 do_test 2.3 {
  56   db eval {SELECT b FROM t1}
  57 } {4 8 2}
  58
  59 db close
  60 sqlite3_shutdown
  61 sqlite3_config_cis 0
  62 sqlite3 db test.db
  63
  64 do_test 3.1 {
  65   db eval {SELECT a FROM t1}
  66   # With the optimization configured off, output in rowid order
  67 } {5 4 3}
  68 do_test 3.2 {
  69   db eval {SELECT a, c FROM t1}
  70 } {5 3 4 2 3 1}
  71 do_test 3.3 {
  72   db eval {SELECT b FROM t1}
  73 } {4 8 2}
  74
  75 db close
  76 sqlite3_shutdown
  77 sqlite3_config_cis 1
  78 sqlite3 db test.db
  79
  80 # The CIS optimization is enabled again.  Covering indices are once again
  81 # used for all table scans.
  82 do_test 4.1 {
  83   db eval {SELECT a FROM t1}
  84 } {3 4 5}
  85 do_test 4.2 {
  86   db eval {SELECT a, c FROM t1}
  87 } {5 3 4 2 3 1}
  88 do_test 4.3 {
  89   db eval {SELECT b FROM t1}
  90 } {2 4 8}
  91
  92 #-------------------------------------------------------------------------
  93 # Test that indexes with large numbers of columns can be correctly
  94 # identified as covering indexes.
  95 reset_db
  96 set L [list]
  97 for {set i 1} {$i<120} {incr i} {
  98   lappend L "c$i"
  99 }
 100 set cols [join $L ,]
 101
 102 do_execsql_test 5.1.0 "
 103   CREATE TABLE t1(a, b, c, $cols, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
 104   CREATE INDEX i1 ON t1($cols);
 105
 106   CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols);
 107   CREATE INDEX i2 ON t2($cols);
 108 "
 109
 110 do_eqp_test 5.1.1 {
 111   SELECT * FROM t1 ORDER BY c1, c2;
 112 } {SCAN TABLE t1 USING COVERING INDEX i1}
 113
 114 do_eqp_test 5.1.2 {
 115   SELECT * FROM t2 ORDER BY c1, c2;
 116 } {SCAN TABLE t2 USING COVERING INDEX i2}
 117
 118
 119 finish_test