# 2012 September 17 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Tests for the optimization which attempts to use a covering index # for a full-table scan (under the theory that the index will be smaller # and require less I/O and hence will run faster.) # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix coveridxscan do_test 1.1 { db eval { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1); CREATE INDEX t1ab ON t1(a,b); CREATE INDEX t1b ON t1(b); SELECT a FROM t1; } # covering index used for the scan, hence values are increasing } {3 4 5} do_test 1.2 { db eval { SELECT a, c FROM t1; } # There is no covering index, hence the values are in rowid order } {5 3 4 2 3 1} do_test 1.3 { db eval { SELECT b FROM t1; } # Choice of two indices: use the one with fewest columns } {2 4 8} do_test 2.1 { optimization_control db cover-idx-scan 0 db eval {SELECT a FROM t1} # With the optimization turned off, output in rowid order } {5 4 3} do_test 2.2 { db eval {SELECT a, c FROM t1} } {5 3 4 2 3 1} do_test 2.3 { db eval {SELECT b FROM t1} } {4 8 2} db close sqlite3_shutdown sqlite3_config_cis 0 sqlite3 db test.db do_test 3.1 { db eval {SELECT a FROM t1} # With the optimization configured off, output in rowid order } {5 4 3} do_test 3.2 { db eval {SELECT a, c FROM t1} } {5 3 4 2 3 1} do_test 3.3 { db eval {SELECT b FROM t1} } {4 8 2} db close sqlite3_shutdown sqlite3_config_cis 1 sqlite3 db test.db # The CIS optimization is enabled again. Covering indices are once again # used for all table scans. do_test 4.1 { db eval {SELECT a FROM t1} } {3 4 5} do_test 4.2 { db eval {SELECT a, c FROM t1} } {5 3 4 2 3 1} do_test 4.3 { db eval {SELECT b FROM t1} } {2 4 8} #------------------------------------------------------------------------- # Test that indexes with large numbers of columns can be correctly # identified as covering indexes. reset_db set L [list] for {set i 1} {$i<120} {incr i} { lappend L "c$i" } set cols [join $L ,] do_execsql_test 5.1.0 " CREATE TABLE t1(a, b, c, $cols, PRIMARY KEY(a, b, c)) WITHOUT ROWID; CREATE INDEX i1 ON t1($cols); CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols); CREATE INDEX i2 ON t2($cols); " do_eqp_test 5.1.1 { SELECT * FROM t1 ORDER BY c1, c2; } {SCAN TABLE t1 USING COVERING INDEX i1} do_eqp_test 5.1.2 { SELECT * FROM t2 ORDER BY c1, c2; } {SCAN TABLE t2 USING COVERING INDEX i2} finish_test