OSDir


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Oracle ROWNUM


I’m curious how you represented ROWNUM. Did you make it a hidden field in your table? That approach has problems, because in, say, a join query, ROWNUM is a property of the row, not of either of the source tables.

I think I would represent it as a zero-argument function (like CURRENT_DATE) and mark it non-deterministic so that it cannot be pushed down.

“WHERE ROWNUM < constant” can be converted to a LIMIT.

“SELECT ROWNUM” can be converted to “SELECT RANK() OVER ()” or something similar.

Please log a JIRA case for this, and we’d be happy to accept it as a contribution.

Julian


> On Jun 8, 2018, at 6:37 AM, Michael Mior <mmior@xxxxxxxxxx> wrote:
> 
> Unfortunately Calcite doesn't currently identify the fact that ROWNUM
> refers to the row number and not just some field in the table. One approach
> would be to write a rule which matches filters on ROWNUM and converts them
> to sorts (with no ordering) and the proper value of fetch and offset.
> --
> Michael Mior
> mmior@xxxxxxxxxx
> 
> 
> Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <post@xxxxxxxx> a écrit :
> 
>> Hi,
>> 
>> I am trying to convert Oracle SQL to Redshift with some optimizations.
>> Everything works good except ROWNUMs. Parser accept it as a filed and
>> convert it to the same statement. But I want to get limit statement. Does
>> anybody had similar problem? Or can point me how to transform statement or
>> change rel algebra?
>> 
>> *Example:*
>> Oracle: SELECT NAME FROM USERS WHERE ROWNUM <= 5;
>> Redshift: SELECT NAME FROM USERS LIMIT 5;
>> 
>> *Code sample:*
>> String sql = “select name form users where rownum <= 5”;
>> SqlParser.Config config = SqlParser.configBuilder()
>>                .setLex(Lex.ORACLE)
>>                .setConformance(SqlConformanceEnum.ORACLE_12)
>>                .build();
>> DataSource dataSource = JdbcSchema.dataSource("jdbc:oracle:thin:….",
>>                "oracle.jdbc.OracleDriver", “user", “pass”);
>> SchemaPlus rootSchema = Frameworks.createRootSchema(false);
>> 
>> JdbcSchema schema =  JdbcSchema.create(rootSchema, “o", dataSource, null,
>> “my") ;
>> SchemaPlus instrumentation = rootSchema.add(“my", schema);
>> final FrameworkConfig config = Frameworks.newConfigBuilder()
>>                .parserConfig(parserConfig)
>>                .defaultSchema(instrumentation)
>>                .traitDefs(null)
>>                .costFactory(null)
>>                .context(Contexts.EMPTY_CONTEXT)
>>                .ruleSets(RuleSets.ofList())
>>                .programs(programs)
>>                .typeSystem(RelDataTypeSystem.DEFAULT)
>>                .build();
>> Planner planner = Frameworks.getPlanner(config);
>> SqlNode sqlNode = planner.parse(sql);
>> SqlNode validatedNode = planner.validate(sqlNode);
>> SqlDialect sqlDialect = SqlDialect.DatabaseProduct.REDSHIFT.getDialect();
>> String convertedSql = parse.toSqlString(sqlDialect).getSql();  //  will do
>> the same SELECT NAME as name FROM USERS WHERE ROWNUM <= 5;
>> 
>> Thanks,
>> -Kiril
>> 
>>