Thanks a lot.
Now I've got the following result :
Statement Name:
SQL_CURLH000C7
Statement Text:
SELECT * FROM CALLCDRACD WHERE clientId='xxxx' ORDER BY CALLSTART OFFSET 10
ROWS FETCH NEXT 50 ROWS ONLY
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Row Count (1):
Number of opens = 1
Rows seen = 50
Rows filtered = 10
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 45816.40
optimizer estimated cost: 873660.23
Source result set:
Sort ResultSet:
Number of opens = 1
Rows input = 460886
Rows returned = 60
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of merge runs=50
Number of rows input=460886
Number of rows output=460886
Size of merge runs=[9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055,
9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055, 9055,
9055, 9055, 9055, 9055, 9055]
Sort type=external
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 45816.40
optimizer estimated cost: 873660.23
Source result set:
Table Scan ResultSet for CALLCDRACD at read committed isolation
level
using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 460886
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=28
Number of pages visited=46106
Number of rows qualified=460886
Number of rows visited=460886
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 45816.40
optimizer estimated cost: 873660.23
I'm pretty sure that my index is not used. But I wonder if my index and its
usage is correct.
In fact this DB is filled with a special use case : All 460 000 entries have
the same ClientId.
(This customer is deployed on a dedicated server, while smaller customers
are shared in an other DB => ClientId field used to identify them)
I was expecting that index would be usefull to avoid sorting results with
the request :
SELECT * FROM CALLCDRACD WHERE clientId='xxxx' ORDER BY CALLSTART OFFSET 10
ROWS FETCH NEXT 50 ROWS ONLY
but it seems that I'm wrong. Can you confirm me if my index can be used in
that way, or if there is a way to have better performance ?
bbergquist wrote:
Do it this way:
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
SELECT * FROM ....;
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
Replace the "SELECT * FROM ..." with your query. The "VALUES SYS..." will
return one row, one column that contains the statement execution plan.
I use SquirrelSQL client and run the first statement, run the query, run
the "values". Look at the query plan, and then turn the statistics off.
