3 Replies Latest reply on Nov 19, 2012 9:40 PM by 896983

Non unique addressing in MODEL dimensions?

896983 Nov 15, 2012 5:05 PM

Hello all,

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Req:

I have a field with duplicate words like 'PRESIDENT;PRESIDENT-EXECUTIVE;PRESIDENT' ( seperated by semicolon) I need to remove duplicate words from a line. I have like 1Million rows to work on...

ORA-32638: Non unique addressing in MODEL dimensions
32638. 00000 -  "Non unique addressing in MODEL dimensions"
*Cause:    The address space defined for the MODEL (partition by and dimension by
           expressions) do not uniquely identify each cell.
*Action:   Rewrite the MODEL clause. Using UNIQUE SINGLE REFERENCE
           option might help.

First time i am using Model Dimension by

SELECT   BIO, RTRIM (str_new, ';') new_str
  FROM   db_temp
MODEL
   DIMENSION BY (0 dim)
   MEASURES (BIO, BIO || ';' STR_NEW)
   RULES
      ITERATE (1000) UNTIL (STR_NEW[0] = PREVIOUS (STR_NEW[0]))
      (str_new [0] =
            REGEXP_REPLACE (str_new[0], '(^|;)([^;]+;)(.*?;)?\2+', '\1\2\3'));

Any Better approach to handle this type of situation other than Model Dimension by

1. Re: Non unique addressing in MODEL dimensions?

odie_63 Nov 15, 2012 5:26 PM (in response to 896983)
Don't you have some unique ID column in the table?

In which case, all you have to do is add a PARTITION clause :

... MODEL PARTITION BY (id) DIMENSION BY (0 dim) ...
If you don't have such column, you can also compute one on-the-fly :
... MODEL PARTITION BY (rownum rn) DIMENSION BY (0 dim) ...
Like Show 0 Likes(0)

Actions
2. Re: Non unique addressing in MODEL dimensions?

chris227 Nov 16, 2012 11:51 AM (in response to 896983)

Another approch could be using recursive subquery.
See this thread for an idea of it
Re: duplicate numbers in the number string
1 person found this helpful
Like Show 0 Likes(0)

Actions

3. Re: Non unique addressing in MODEL dimensions?

896983 Nov 19, 2012 9:40 PM (in response to odie_63)

Hello Odie,

If i need to work on 2 columns ...how do i modify the below code...

SELECT   BIO, RTRIM (str_new, ';') new_str
  FROM   db_temp
MODEL
   partition by (rownum rn)
DIMENSION BY (0 dim)
   MEASURES (BIO, BIO || ';' STR_NEW)
   RULES
      ITERATE (1000) UNTIL (STR_NEW[0] = PREVIOUS (STR_NEW[0]))
      (str_new [0] =
            REGEXP_REPLACE (str_new[0], '(^|;)([^;]+;)(.*?;)?\2+', '\1\2\3'));

Edited by: Krux_rap on Nov 19, 2012 1:40 PM

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.