Let SQL Developer Normalize Your Tables For You

Tell Others About This Story:

SQL Developer is a pretty mouse-heavy tool. By that I mean that many of the functions are accessed via mouse-clicks. When browsing tables in the tree you may have noticed that you can expand a table node to display the table columns.

Tables in the Connections Tree

There are some pretty cool features available under the context menu when clicking on a table column:

Table Column Context Menu

Some of these actions are available under the table right-clicks, but a couple are not, ‘Encrypt’ and ‘Decrypt.’

One that you may find useful is a dynamic table refactoring feature called ‘Normalize.’

Normalize a Table Column

Sometimes you find a table that has all the data in it. Like a column called ‘Country.’ And instead of having a countryID which points to a child country table record via a foreign key constraint, they just have the text value as part of the ‘master’ record. This isn’t ideal for many situations but it can be a pain to fix after-the-fact.

This SQL Developer feature will ask you a few questions then generate a dynamic script to split that data out to a new table, and populate new IDs in the look-up table.

Supply sequence and trigger name, we do the rest!

Wait, this won’t work! I’m trying to do this on a partitioned table. Lots of things you can’t do column wise when a table is partitioned. So let’s try again on another table – my iTunes table I created in my earlier ‘Free Beer’ post.

Here’s the script that SQL Developer generates:

  l_sql VARCHAR2(32767);
  l_col_size NUMBER;
  FOR r IN ( SELECT data_type FROM sys.all_tab_cols WHERE owner = 'HR' AND table_name = 'ITUNES_MUSIC' AND column_name = 'ARTIST' ) LOOP
      raise_application_error (-20001,'Normalize only supported on VARCHAR,VARCHAR2,CHAR,NCHAR,NVARCHAR,NVARCHAR2');
   END IF;
  SELECT data_length INTO l_col_size FROM sys.all_tab_cols WHERE owner = 'HR' AND table_name = 'ITUNES_MUSIC' AND column_name = 'ARTIST';
  run('create table MUSIC_ARTISTS (ARTIST_ID number not null primary key, ARTIST varchar2('||l_col_size||') not null)');
  run('create sequence ARTIST_SEQ');
  run('create or replace trigger "T_ARTIST_ID_SEQ"'||
           ' before insert or update on '||
           ' MUSIC_ARTISTS for each row '||
           ' begin '||
           ' if inserting and :new.ARTIST_ID is null then '||
           '  for c1 in (select ARTIST_SEQ.nextval nv from dual) loop '||
           '     :new.ARTIST_ID := c1.nv; '||
           '  end loop; '||
           'end if; '||
   run('insert into MUSIC_ARTISTS( ARTIST ) select distinct ARTIST from "HR"."ITUNES_MUSIC" where ARTIST is not null');
   run('alter table "ITUNES_MUSIC" add ARTIST_ID number');
   run('update "ITUNES_MUSIC" x set ARTIST_ID = (select ARTIST_ID from MUSIC_ARTISTS where ARTIST = x.ARTIST)');
   run('alter table "ITUNES_MUSIC" drop column ARTIST');
   run('alter table "ITUNES_MUSIC" rename column ARTIST_ID  to ARTIST');
   run('alter table "ITUNES_MUSIC" add foreign key (ARTIST) references MUSIC_ARTISTS(ARTIST_ID)');

Once I click ‘OK’ on the dialog, the script will run and I’ll have:

  • A new table with the Artist column refactored and linked to the original table
  • The existing table data has changed with the old column rebuilt and pointing to the new table
  • A new sequence and trigger setup to handle new records on an INSERT

Here’s a picture of what happened (you can build your own model using the ctrl-drag-and-drop trick.)

From one table to two in just a couple of clicks!

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , ,