Forum Stats

  • 3,851,600 Users
  • 2,264,001 Discussions
  • 7,904,786 Comments

Discussions

SQL puzzle :)

13»

Comments

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    Not the fastest method, but it works.
           select n1
           from ( select to_char( level ) n1
                       , length( to_char( level ) ) sl
                  from dual
                  where level > reverse( to_char( level ) ) 
                  connect by level < 100000
                )
           where 0 = ( select n1
                            - sum( substr( n1, level, 1 ) * case level
                                                              when 1 then 1
                                                              when 2 then 10
                                                              when 3 then 100
                                                              when 4 then 1000
                                                              when 5 then 10000
                                                              when 6 then 100000
                                                              when 7 then 1000000
                                                              when 8 then 10000000
                                                              when 9 then 100000000
                                                            end
                                 )
                            - sum( substr( n1, level, 1 ) )
                            - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )
                       from dual
                       connect by level <= sl
                     )
    Anton
  • 598210
    598210 Member Posts: 305
    Thanks to everyone who gave feedback to this thread, pipelined pl/sql solution until now was the fastest for me. But other solutions was great to know, each made me think how alternatives may vary for any kind of question.
  • 486393
    486393 Member Posts: 487
    I changed Anton's query a little bit.

    select n1
    from ( select to_char( level ) n1
    , length( to_char( level ) ) sl
    from dual
    where level > reverse( to_char( level ) )
    connect by level < 100000
    )
    where 0 = ( select n1
    - sum( substr( n1, level, 1 ) * power(10,level-1) )
    - sum( substr( n1, level, 1 ) )
    - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )
    from dual
    connect by level <= sl
    )
    /
  • 486393
    486393 Member Posts: 487
    I changed the case statement in power(10,level-1)
           select n1
           from ( select to_char( level ) n1
                       , length( to_char( level ) ) sl
                  from dual
                  where level > reverse( to_char( level ) ) 
                  connect by level < 100000
                )
           where 0 = ( select n1
                            - sum( substr( n1, level, 1 ) * power(10,level-1)  )
                            - sum( substr( n1, level, 1 ) )
                            - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )
                       from dual
                       connect by level <= sl
                     )
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    That's my original version.
    The power-function is as fast as the "case version"
    The method using "round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )"
    is, to my surprise, faster as using a user-defined aggregrate function.


    Anton
  • 486393
    486393 Member Posts: 487
    Interesting.

    What kind of user defined aggregate function did you built? With or without collect?
  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    create type myaggImpl as object
    (
      prod NUMBER,
      static function ODCIAggregateInitialize( sctx IN OUT myaggImpl ) return number,
      member function ODCIAggregateIterate( self IN OUT myaggImpl, value IN number ) return number,
      member function ODCIAggregateTerminate( self IN myaggImpl, returnValue OUT number, flags IN number ) return number,
      member function ODCIAggregateMerge( self IN OUT myaggImpl, ctx2 IN myaggImpl ) return number
    );
    /
    
    create or replace type body myaggImpl
    is 
    static function ODCIAggregateInitialize( sctx IN OUT myaggImpl ) 
    return number
    is 
    begin
      sctx := myaggImpl( 1 );
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateIterate( self IN OUT myaggImpl, value IN number )
    return number
    is
    begin
      self.prod := self.prod * value;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateTerminate( self IN myaggImpl, returnValue OUT number, flags IN number )
    return number
    is
    begin
      returnValue := self.prod;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateMerge(self IN OUT myaggImpl, ctx2 IN myaggImpl )
    return number
    is
    begin
      self.prod := self.prod * ctx2.prod;
      return ODCIConst.Success;
    end;
    end;
    /
    
    CREATE FUNCTION myagg( input NUMBER )
    RETURN NUMBER 
    PARALLEL_ENABLE AGGREGATE USING myaggImpl;
    /
  • 486393
    486393 Member Posts: 487
    Collect makes it faster, less context switching between sql and pl/sql . Collect works in Oracle 10 and 11.
    create  type number_table is table of number;
    /
    
    create or replace function product (l_numbers number_table )
    return number
    is
    l_result number := 1;
    begin
      for i in l_numbers.first..l_numbers.last loop
       l_result := l_result * l_numbers(i);
      end loop;
      return l_result;
    end;
    /
    
           select n1
           from ( select to_char( level ) n1
                       , length( to_char( level ) ) sl
                  from dual
                  where level > reverse( to_char( level ) ) 
                  connect by level < 100000
                )
           where 0 = ( select n1
                            - sum( substr( n1, level, 1 ) * power(10,level-1)  )
                            - sum( substr( n1, level, 1 ) )
                            - product(cast(collect(to_number(substr( n1, level, 1 ))) as number_table))
                       from dual
                       connect by level <= sl
                     )
    /
This discussion has been closed.