Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

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

asked 02 Apr '13, 02:32

Marsel's gravatar image

Marsel
185256
accept rate: 33%

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

(02 Apr '13, 03:28) Martin

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

permanent link

answered 02 Apr '13, 04:56

Volker%20Stoeffler's gravatar image

Volker Stoef...
106127
accept rate: 0%

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

(02 Apr '13, 05:01) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×260
×90
×32

question asked: 02 Apr '13, 02:32

question was seen: 2,141 times

last updated: 02 Apr '13, 05:01