cancel
Showing results for 
Search instead for 
Did you mean: 

Strange behavior for a simple query

Former Member
2,452
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-1Number of times the result was computed
RowsReturned21Number of rows returned
PercentTotalCost100100Run time as a percent of total query time
RunTime0.00022353.4438Time to compute the results
QueryMemMaxUseful00Pages of query memory that are useful to this request
QueryMemLikelyGrant1000Memory pages likely to be granted to query if it were run now
CPUTime0.0002235-Time required by CPU
DiskReadTime0-Time to perform reads from disk
DiskWriteTime0-Time to perform writes to disk
CacheHits-65542Cache Hits
CacheRead-65542Cache reads
CacheReadIndInt-14Cache index interior reads
CacheReadIndLeaf-548Cache index leaf reads
CacheReadTable-64973Cache table reads
DiskRead00Disk reads
DiskWrite00Disk 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-1Number of times the result was computed
RowsReturned22Number of rows returned
PercentTotalCost100100Run time as a percent of total query time
RunTime0.000682057.4396e-005Time to compute the results
QueryMemMaxUseful00Pages of query memory that are useful to this request
QueryMemLikelyGrant1000Memory pages likely to be granted to query if it were run now
CPUTime0.000444-Time required by CPU
DiskReadTime0.00023805-Time to perform reads from disk
DiskWriteTime0-Time to perform writes to disk
CacheHits-44Cache Hits
CacheRead-44Cache reads
CacheReadIndInt-18Cache index interior reads
CacheReadIndLeaf-9Cache index leaf reads
DiskRead0.0232720Disk reads
DiskWrite00Disk writes

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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
Contributor
0 Kudos

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