[
https://issues.apache.org/jira/browse/DERBY-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13803372#comment-13803372
]
Mike Matrigali commented on DERBY-6115:
---------------------------------------
not an optimizer expert, ...
With respect to the posted observation (it would always be best if you have a
real case to post the repro with a query plan):
5. Now use a predicates expression similar to a failure case described in this
defect, e.g: WHERE NAME > 'zzz' or ( NAME > 'Wz' and NAME < 'XA' )
Derby optimizer plans unfortunatley are limited to only one scan of a single
access path on a table (where access path is either
the base table or an index on the table), EXCEPT in the single special case of
an IN-LIST where the optmizer in a single execution node probes for equality on
each term to the index. This execution node
is referred to as a multi-probe.
I think the following feature would optimize this set of conditions, and maybe
it could leverage the IN-LIST code.
I think the optimal strategy for this query would be to do what I will call a
bounded multi-probe (which derby is not coded to do). So
it would first probe the index and return (name > 'zzz') and then it set up a
second scan (name > 'Wz' and Name < 'XA'). There may
also be algebraic stuff it could do on the conditions for extra credit. These
two probes can easily be supported by store interface.
Obviously as described duplicate rows might be returned, but that is ok I think
with the standard.
Did you happen to check if the predicates were pushed down to initScan in this
case. It would be obvious from the query plan. I understand that an index
was not used
but would have expected a single table scan.
If this were a specific problem in your application I would suggest seeing if
rewriting it as a union of the 2 or halfs of the query
helped.
> Certain OR expressions are not passed to Table indexes or Table Function
> initScan()
> -----------------------------------------------------------------------------------
>
> Key: DERBY-6115
> URL: https://issues.apache.org/jira/browse/DERBY-6115
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1,
> 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
> Reporter: David Vyvyan
> Labels: derby_triage10_11
>
> Issue originally posted here:
> ====================
> http://apache-database.10148.n7.nabble.com/RestrictedVTI-initScan-does-not-pass-certain-Table-Functions-predicate-expressions-td128229.html
> Note by Rick Hillegas:
> ================
> Hi David,
> I think it's worth filing a JIRA for this issue. If the defect is shared
> by VTIs and table functions then there's a possibility that ordinary
> table scans suffer from it too. That would raise the problem's urgency.
> Thanks,
> -Rick
> Summary Description:
> ================
> Basically some WHERE clause expressions do not get passed through via
> RestrictedVTI.initScan().
> This can have a severe impact on memory/performance.
> (I suspect the issue may be related to logic which tries to move AND nodes to
> the top of the tree...?)
> Examples (I have a few more here than in the post above):
> These get passed ok in the Restriction object:
> - C1>6
> - C1>1 AND C2<'d'
> - C1>6 OR C2<'d'
> - C1>1 AND (C1<6 OR C2<'d')
> This one gets passed partially by initScan():
> C1>1 AND (C1<6 OR (C2>'e' AND C2<'d')) ===> initScan() passes only:
> "C1" > 1
> These do not get passed at all (initScan() Restriction argument object is
> null):
> - C1>6 OR (C1>1 AND C2<'d')
> - C1>6 OR ((C1>1 AND C2<'d') AND C2>'b')
> - C1 in ( 1, 4 )
> - C1 in ( 1, 4 ) OR C2>'f' -- Can Derby resolve in() clauses to a list of '='
> conditions ? This would be useful!
> My table function is defined as follows:
> CREATE FUNCTION TF_TEST1() RETURNS TABLE(C1 INT, C2 VARCHAR(32672)) PARAMETER
> STYLE DERBY_JDBC_RESULT_SET LANGUAGE JAVA NOT DETERMINISTIC READS SQL DATA
> EXTERNAL NAME 'core.TestTableFunctions.TF_TEST1'
--
This message was sent by Atlassian JIRA
(v6.1#6144)
