[
https://issues.apache.org/jira/browse/DERBY-2641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-2641:
----------------------------------
The intent in derby is definitely to try to pick index scan over table scan for
tables with small numbers of rows - EVEN if the performance might be better
doing the table scan. The actual use problems were:
1) when you pick heap vs. index scan depending on isolation level often one had
to set a TABLE lock rather then individual row locks,
this is a particular problem with serializable isolation.
2) more subtle is that if the index scan eliminates rows then we lock less rows
than the heap case where minimally we will row lock
every row of the heap, this is especially problem with repeatable read and
serializable.
Especially in case where derby needed to pick table locking to do correct
isolation level customers were seeing applications fail
on derby which would run otherwise unchanged. Also this behavior met what most
customers expected. They created an index,
the query used the index.
This is a case where we choose to weigh concurrency over execution cost in the
cost based optimizer. The hope is:
1) usually small number of row table performance does not matter.
2) often the small number of rows is actually growing but we happen to compile
at app startup.
> Convert lang/staleplans.sql to JUnit
> ------------------------------------
>
> Key: DERBY-2641
> URL: https://issues.apache.org/jira/browse/DERBY-2641
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-2641-1.diff, derby-2641-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
