Hi reader,

if you have to deal with datawarehouses you need to implement dimension tables. A dimension could be the time dimension - a table of month Id’s for example to aggregate data per month. How to generate  such month id’s (YYYYMM)?

Simply use the model clause - some Oracle 10g spreadsheet functionality and generate a bunch of cells with specified rules.

To get a stream of years execute following SQL :

SELECT Integer_Value AS YEAR

 FROM Dual

 WHERE 1=2

 MODEL DIMENSION BY(0 AS Key)

       MEASURES(0 AS Integer_Value)

       RULES UPSERT(Integer_Value [ FOR KEY FROM 1900 TO 2100 INCREMENT 1 ] = Cv(KEY))

SQL> SELECT Integer_Value AS YEAR
  2    FROM Dual
  3   WHERE 1 = 2
  4   MODEL DIMENSION BY(0 AS Key)
  5         MEASURES(0 AS Integer_Value)
  6         RULES UPSERT(Integer_Value [ FOR KEY FROM 1900 TO 2100 INCREMENT 1 ] = Cv(KEY))
  7  ;
 
      YEAR
———-
      1900
      1901
      1902
      1903
      1904
      1905
      1906
      1907

To get a stream of months execute following statement

SELECT Integer_Value AS MONTH

  FROM Dual

 WHERE 1 = 2

 MODEL DIMENSION BY(0 AS Key)

       MEASURES(0 AS Integer_Value)

       RULES Upsert(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key))

SQL> SELECT Integer_Value AS MONTH
  2    FROM Dual
  3   WHERE 1 = 2
  4   MODEL DIMENSION BY(0 AS Key)
  5         MEASURES(0 AS Integer_Value)
  6         RULES Upsert(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key))
  7  ;
 
     MONTH
———-
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
 
12 rows selected
 

To get the combination of both use cartesian product of month and year stream

SELECT YEAR , MONTH, (YEAR * 100) + MONTH AS MONTHID FROM (

  SELECT Integer_Value AS YEAR

    FROM Dual

   WHERE 1 = 2

   MODEL DIMENSION BY(0 AS Key)

         MEASURES(0 AS Integer_Value)

         RULES UPSERT(Integer_Value [ FOR Key FROM 1900 TO 2100 Increment 1 ] = Cv(Key))),

(

  SELECT Integer_Value AS MONTH

    FROM Dual

   WHERE 1 = 2

   MODEL DIMENSION BY(0 AS Key)

         MEASURES(0 AS Integer_Value)

         RULES UPSERT(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key))) 

SQL> SELECT YEAR , MONTH, (YEAR * 100) + MONTH AS  MONTHID FROM (
  2    SELECT Integer_Value AS YEAR
  3      FROM Dual
  4     WHERE 1 = 2
  5     MODEL DIMENSION BY(0 AS Key)
  6           MEASURES(0 AS Integer_Value)
  7           RULES UPSERT(Integer_Value [ FOR Key FROM 1900 TO 2100 Increment 1 ] = Cv(Key))),
  8  (
  9    SELECT Integer_Value AS MONTH
 10      FROM Dual
 11     WHERE 1 = 2
 12     MODEL DIMENSION BY(0 AS Key)
 13           MEASURES(0 AS Integer_Value)
 14           RULES UPSERT(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key)))
 15  ;
 
      YEAR      MONTH    MONTHID
———- ———- ———-
      1900          1     190001
      1900          2     190002
      1900          3     190003
      1900          4     190004
      1900          5     190005
      1900          6     190006
      1900          7     190007
      1900          8     190008
      1900          9     190009
      1900         10     190010
      1900         11     190011
      1900         12     190012
      1901          1     190101

Now you get your monthid without writing one line of PL/SQL or the need of base data.

HTH Karl Reitschuster

Hi reader,

today i imported a database dump (Wow!!).  When all tables were imported i waited for a while. Usually after Import of the tables all procedural objects and views are created. Now after some minutes i got impatient and i looked into the current session SQL of imp and recognized following CREATE INDEX Statement :

CREATE INDEX "CUSTOMER_FX04" ON "CUSTOMER" (UPPER(TRIM("SHORTNAME")) , "CUSTOMERTYPE" ) 
       PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 52428800 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
       TABLESPACE "APPTSIXL" LOGGING

What??? An Index Create still after all Tables are already imported? The thing behind is that the index is a function based index. And a function indeed could be not only a built in but also a stored function. So Index creation of function based indexes starts after the procedureal objects were created. That’s the reason too for some import delays.

Karl Reitschuster