Strange behavior for a simple query

[Former Member]

Hi,
Strange behavior for a simple query
SQL1: Scan table1 using index table1 (all rows)
SQL2: Scan table1 using index table1_test_index
I think that the same behavior in SA11.

Why SA16 not use index table1_test_index for SQL1?
(yes, I can fix it with index hints)

create test database:
------------------
"%SQLANY16%\\bin32\\dbinit" -i -s -p 4096 -z 1251CYR test.db
"%SQLANY16%\\bin32\\dbisql" -c "uid=dba;pwd=sql;dbf=test.db" reload.sql
------------------
reload.sql:
------------------
CREATE TABLE "DBA"."table1" (
  "ID"   INTEGER NOT NULL DEFAULT AUTOINCREMENT,
  "col1" INTEGER NOT NULL,
  "col2" INTEGER NULL,
  "col3" INTEGER NULL,
  "col4" INTEGER NULL,
  "col5" INTEGER NULL,
  "col6" NUMERIC(10,2) NULL,
  "col7" NUMERIC(10,2) NULL,
  "col8" NUMERIC(10,2) NULL,
  PRIMARY KEY ( "ID" ASC )
);

CREATE INDEX "table1_test_index" ON "DBA"."table1" ( "col1" ASC, "col2" ASC );

// loads 5 000 000 records
LOAD TABLE "DBA"."table1"  
  FROM '723.dat';
commit;
------------------

plan for SQL1:
------------------
select min(id) from table1 where col1=1 and col2=1

Subtree Statistics
		Estimates		Actual		Description
Invocations		-		1		Number of times the result was computed
RowsReturned		2		1		Number of rows returned
PercentTotalCost		100		100		Run time as a percent of total query time
RunTime		0.0002235		3.4438		Time to compute the results
QueryMemMaxUseful		0		0		Pages of query memory that are useful to this request
QueryMemLikelyGrant		100		0		Memory pages likely to be granted to query if it were run now
CPUTime		0.0002235		-		Time required by CPU
DiskReadTime		0		-		Time to perform reads from disk
DiskWriteTime		0		-		Time to perform writes to disk
CacheHits		-		65542		Cache Hits
CacheRead		-		65542		Cache reads
CacheReadIndInt		-		14		Cache index interior reads
CacheReadIndLeaf		-		548		Cache index leaf reads
CacheReadTable		-		64973		Cache table reads
DiskRead		0		0		Disk reads
DiskWrite		0		0		Disk writes

---

plan for SQL2:
------------------
select min(id) from table1 where col1=1 and col2=1
union all
select min(id) from table1 where col1=1 and col2=1

Subtree Statistics
		Estimates		Actual		Description
Invocations		-		1		Number of times the result was computed
RowsReturned		2		2		Number of rows returned
PercentTotalCost		100		100		Run time as a percent of total query time
RunTime		0.00068205		7.4396e-005		Time to compute the results
QueryMemMaxUseful		0		0		Pages of query memory that are useful to this request
QueryMemLikelyGrant		100		0		Memory pages likely to be granted to query if it were run now
CPUTime		0.000444		-		Time required by CPU
DiskReadTime		0.00023805		-		Time to perform reads from disk
DiskWriteTime		0		-		Time to perform writes to disk
CacheHits		-		44		Cache Hits
CacheRead		-		44		Cache reads
CacheReadIndInt		-		18		Cache index interior reads
CacheReadIndLeaf		-		9		Cache index leaf reads
DiskRead		0.023272		0		Disk reads
DiskWrite		0		0		Disk writes

------------------

[MCMartin]

stays the plan the same if you repeat SQL1 Otherwise it is the load of the cache which might bear the difference

[Former Member]

Does the behavior change if you load the data first and create the index afterwards? I'd expect this approach to be faster (for loading) anyway and it also is the way a reload.SQL is created by DBUnload (for just that reason).

Wrt your request, I wonder whether this also creates better optimizer statistics.

Cheers - Volker

[VolkerBarth]

For a great explanation on that topic, see Ivan's answer on that question...