You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Anghel Botos (JIRA)" <ji...@apache.org> on 2016/11/04 07:07:58 UTC
[jira] [Comment Edited] (IGNITE-4163) Wrong SQL generated by
org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
[ https://issues.apache.org/jira/browse/IGNITE-4163?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15635499#comment-15635499 ]
Anghel Botos edited comment on IGNITE-4163 at 11/4/16 7:07 AM:
---------------------------------------------------------------
Additional to my above comment, since having a select that is so nested didn't look quite nice to me, I also looked at Oracle's {{ROW_NUMBER()}} function. I can get the same results using a query like this:
{{SELECT KC1, KC2 FROM (SELECT KC1, KC2, ROW_NUMBER() OVER (ORDER BY KC1, KC2) RN FROM MY_TABLE) WHERE mod(RN, 2)=0;}} (I used {{parallelLoadCacheMinThreshold=2}} just as an example).
Regarding which of the two approaches is better (i.e. more performant) I cannot really say, as I'm not an Oracle expert, but I found some articles dealing with the topic (e.g. https://explainextended.com/2009/05/06/oracle-row_number-vs-rownum/). In case you have an Oracle expert in amongst your contributors, it might be a good idea to check with him.
was (Author: abotos):
Additional to my above comment, since having a select that is so nested I also looked at Oracle's {{ROW_NUMBER()}} function. I can get the same results using a query like this:
{{SELECT KC1, KC2 FROM (SELECT KC1, KC2, ROW_NUMBER() OVER (ORDER BY KC1, KC2) RN FROM MY_TABLE) WHERE mod(RN, 2)=0;}} (I used {{parallelLoadCacheMinThreshold=2}} just as an example).
Regarding which of the two approaches is better (i.e. more performant) I cannot really say, as I'm not an Oracle expert, but I found some articles dealing with the topic (e.g. https://explainextended.com/2009/05/06/oracle-row_number-vs-rownum/). In case you have an Oracle expert in amongst your contributors, it might be a good idea to check with him.
> Wrong SQL generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery
> ------------------------------------------------------------------------------------------------------------
>
> Key: IGNITE-4163
> URL: https://issues.apache.org/jira/browse/IGNITE-4163
> Project: Ignite
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.7
> Reporter: Anghel Botos
> Assignee: Alexey Kuznetsov
> Attachments: IGNITE_4163_Oracle_specific_for_load_range_query_.patch
>
>
> The SQL statement generated by org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect#loadCacheSelectRangeQuery looks like this:
> {{SELECT KEY_COLUMN_1,KEY_COLUMN_2 FROM (SELECT KEY_COLUMN_1,KEY_COLUMN_2, ROWNUM() AS rn FROM SOME_TABLE ORDER BY KEY_COLUMN_1,KEY_COLUMN_2) WHERE mod(rn, ?) = 0}}
> For Oracle this is incorrect, as Oracle does not have a {{ROWNUM()}} function. For the above query the following error is thrown: {{ORA-00923: FROM keyword not found where expected}}
> Regarding row numbering Oracle has:
> * a {{ROWNUM}} pseudocolumn, in which case the query should have {{ROWNUM AS rn}}
> * a {{ROW_NUMBER()}} function, in which case the query would become more complicated. See https://docs.oracle.com/database/121/SQLRF/functions170.htm#SQLRF06100 for more details about {{ROW_NUMBER()}}
> Please make the neccessary adjustments to either {{BasicJdbcDialect}} or {{OracleDialect}} so that a correct query is produced.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)