1 2 3 Previous Next 37 Replies Latest reply: Jan 8, 2013 10:13 PM by 887479 Go to original post RSS
      • 30. Re: Remove repeating letters
        BrendanP
        BANNER
        --------------------------------------------------------------------------------

        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
        PL/SQL Release 11.2.0.1.0 - Production
        CORE 11.2.0.1.0 Production
        TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
        NLSRTL Version 11.2.0.1.0 - Production

        Edited by: BrendanP on 08-Jan-2013 09:29
        I'll try it on my XE system a bit later and report back.
        • 31. Re: Remove repeating letters
          887479
          padders wrote:
          Output should be abcdefghijkl
          Can you clarify the requirement please, is it merely coincidence that the output is in ascii order?
          Its mere coincidence.

          It can be in any order..

          Sorry, I dont have db with me..cannot test..
          • 32. Re: Remove repeating letters
            Solomon Yakobson
            Keep in mind XML solution will take longer first time when it loads all that java.

            SY.
            • 33. Re: Remove repeating letters
              odie_63
              Solomon Yakobson wrote:
              Keep in mind XML solution will take longer first time when it loads all that java.
              There's no Java involved in 11g.

              Whenever possible (and it's the case here) the CBO rewrites all the XQuery expression using kernel-based functions.
              When it cannot, a XQuery VM is used to resolve the expression (functional evaluation).

              Edited by: odie_63 on 8 janv. 2013 18:41
              • 34. Re: Remove repeating letters
                chris227
                Would you like to consider my rec subquery too? it takes less recursions than solomons if i am correct.
                • 35. Re: Remove repeating letters
                  BrendanP
                  I just got 72s for that - it seems my system is quite slow. It is multi-user but I doubt anything else is going on, will rerun on single-user XE later.
                  SQL> with t as (select 'abcdefghijklmonpqrstuvwxyz' alpha from dual)
                    2  select v.str, replace(
                    3                  translate(
                    4                          t.alpha,
                    5                          replace (
                    6                                  translate (t.alpha, v.str, '@'),
                    7                          '@', null),
                    8                       '@'),
                    9               '@', null) newstr
                   10  from t, temp_values v
                   11  /
                  
                  10000 rows selected.
                  
                  Elapsed: 00:01:11.63
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 682670495
                  
                  ----------------------------------------------------------------------------------
                  | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                  ----------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT   |             |  9886 |   289K|     8   (0)| 00:00:01 |
                  |   1 |  NESTED LOOPS      |             |  9886 |   289K|     8   (0)| 00:00:01 |
                  |   2 |   FAST DUAL        |             |     1 |       |     2   (0)| 00:00:01 |
                  |   3 |   TABLE ACCESS FULL| TEMP_VALUES |  9886 |   289K|     6   (0)| 00:00:01 |
                  ----------------------------------------------------------------------------------
                  
                  Note
                  -----
                     - SQL plan baseline "SQL_PLAN_3yxb1wsary61m183a0310" used for this statement
                  
                  
                  Statistics
                  ----------------------------------------------------------
                           25  recursive calls
                           67  db block gets
                          728  consistent gets
                           10  physical reads
                        22396  redo size
                       383788  bytes sent via SQL*Net to client
                         5021  bytes received via SQL*Net from client
                          668  SQL*Net roundtrips to/from client
                            0  sorts (memory)
                            0  sorts (disk)
                        10000  rows processed
                  Hope my tweak hasn't changed the logic, which I don't fully understand yet - I did test on a small example and it looked ok.
                  • 36. Re: Remove repeating letters
                    chris227
                    Hope my tweak hasn't changed the logic, which I don't fully understand yet - I did test on a small example and it looked ok.
                    I dont think so.
                    You could also have hard-code the alphabet instead.

                    First it deletes all characters occuring in the string from the alphabet.
                    Then it deletes all this characters (which are not in the string) from the (new) alphabet, so the remaining would be those from the string.
                    • 37. Re: Remove repeating letters
                      887479
                      Sorry for the late reply..

                      I will compare the performances and will get back to you ASAP.
                      1 2 3 Previous Next