Strange behavior for a simple query

Former Member — Tue, 02 Apr 2013 02:32:06 GMT

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

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

Re: Strange behavior for a simple query

MCMartin — Tue, 02 Apr 2013 03:28:05 GMT

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

Re: Strange behavior for a simple query

Former Member — Tue, 02 Apr 2013 04:56:12 GMT

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

Re: Strange behavior for a simple query

VolkerBarth — Tue, 02 Apr 2013 05:01:34 GMT

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

Question Re: Strange behavior for a simple query in Technology Q&A

Strange behavior for a simple query

Re: Strange behavior for a simple query

Re: Strange behavior for a simple query

Re: Strange behavior for a simple query