[
https://issues.apache.org/jira/browse/DERBY-6226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13665771#comment-13665771
]
Mike Matrigali commented on DERBY-6226:
---------------------------------------
For a repro of this issue see the testDerby6045DeleteTest() in
InListMultiProbeTest.java. This test currently is incorrect. It assumes that
because it
found a index scan that there is no problem. But what is really happening is
that there is an index scan, but it is a full index scan with no start/stop
(ie. no key probe).
Our tools using junit to verify plans are pretty limited, hopefully we can fix
test to better about verifying that an index scan is used and it is not doing a
full index
scan.
Below is a print out of the full queryplan showing that the index scan has not
start/stop keys, see:
start position:
None
stop position:
None
Also note number of rows visited:
Number of rows visited=10000
Here is full query plan I got by adding in a System.out.println("rtsp = " +
rtsp); when the plan is gotten in the junit test:
.
(emb)lang.InListMultiProbeTest.testDerby6045DeleteTest rtsp = Statement Name:
null
Statement Text:
DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)
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:
Delete ResultSet using row locking:
deferred: false
Rows deleted = 2
Indexes updated = 3
Execute Time = 0
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 3916.00
optimizer estimated cost: 2847.96
Source result set:
Project-Restrict ResultSet (1):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 3916.00
optimizer estimated cost: 2847.96
Source result set:
Index Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS using
constraint KB_MT_GAF_TOP_LEVEL_TERM_COUNTS_PK at read committed isolation level
usin
g exclusive row locking chosen by the optimizer
Number of opens = 1
Rows seen = 2
Rows filtered = 0
Fetch Size = 1
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={0, 1, 2, 3}
Number of columns fetched=4
Number of deleted rows visited=1
Number of pages visited=107
Number of rows qualified=2
Number of rows visited=10000
Scan type=btree
Tree height=3
start position:
None
stop position:
None
qualifiers:
Column[1][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[1][1] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 3916.00
optimizer estimated cost: 2847.96
> enhance optmizer to use multiple probes into multiple indexes to satisfy OR
> queries on different columns.
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6226
> URL: https://issues.apache.org/jira/browse/DERBY-6226
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.3.1, 10.11.0.0
> Reporter: Mike Matrigali
>
> For queries of the type:
> select * from a where col1 = ? or col2 = ?
> and good indexes exist on col1 and col2 it would optimal if derby could
> execute a plan that
> did an index scan first on the col1 index and then an index scan on col2
> index.
> Currently it looks like derby will do a full index scan if col1 and col2 are
> in any index, or a full table
> scan otherwise.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
