on 2013 Apr 02 3:32 AM
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 |
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
For a great explanation on that topic, see Ivan's answer on that question...
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.