Noetix: Identifying Un-used Columns For Removal

This blog post covers a description of an application (source code provided) that identifies columns which are not required (i.e. all values of the column in the view are the same) and generates a script that somone who has completed the NoetixViews Customization Certification course can include in the Generation Scripts.


The first thing to note is how this works; it opens every view doing a MIN/MAX on every column. It has been tested on a 16GB database and works fine - of course the main requirement of an application like this is to have enough TEMP tablespace in order to work. This is the reason the columns are dealt with individually rather than just opening each view and doing a MIN/MAX on every column in the view in a single hit - this was blowing out the 5GB of TEMP tablespace we had available on the test system.

The application is a simple console application:

All it does is process the views in the database using the SQL:

SELECT DISTINCT UPPER(v.VIEW_NAME) VIEWNAME
  FROM n_views v
 WHERE EXISTS
 (SELECT 'x'
          FROM all_views av
         WHERE av.OWNER = USER
           AND av.VIEW_NAME = UPPER(v.VIEW_NAME))
   AND NVL(v.OMIT_FLAG, 'N') = 'N'
   AND NVL(v.Special_Process_Code, 'X') NOT IN ('LOV', 'BASEVIEW')
   AND (UPPER(v.VIEW_NAME) = '%VIEWNAME%' OR '%VIEWNAME%' = 'ALL')
ORDER BY UPPER(v.VIEW_NAME)

This gives a list of all the views in the database. The %VIEWNAME% is replaced with the first argument on the command line (optional). If nothing is specified then it is replaced with ALL.

Views which are omitted by default are automatically excluded as are all views which are of type LOV (list of values) or BASEVIEW.

Once the list of views has been obtained then the list of columns in each view is retrieved using the SQL:

 SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
  FROM ALL_TAB_COLUMNS ATC
 WHERE ATC.OWNER = USER
   AND ATC.TABLE_NAME = '%VIEWNAME%'
   AND ATC.COLUMN_NAME NOT LIKE 'A$%'
   AND ATC.COLUMN_NAME NOT LIKE 'Z$%'
   AND ATC.DATA_TYPE NOT IN ('ROWID', 'LONG')
   AND INSTR(ATC.COLUMN_NAME, '$') = 0

The list of  columns excludes the A$ and Z$ columns that are managed by Noetix and columns which contain a "$" symbol (flexfields) as if you want to remove these you can disable them in the e-Business Suite itself.

Once the appliction has the list of columns it looks through them building the SQL:

SELECT 1 AS IGNORE
   ,TO_CHAR(MIN()) AS MIN
   ,TO_CHAR(MAX()) AS MAX
FROM

The MIN and MAX values are then compared, if they are the same when an SQL file is created for the suppression of the field (for future reference the MIN and MAX values returned are stored in the file).

As you can imagine when accessing the GL views this query can take quite a while - for our 16GB database processing all the views in our relatively simple configuration will take about a week (running multiple processes has gotten this down to 2-3 days).

This isn't quick - but it is effective - we are removing around 3,000 columns from the views. That's 3,000 less columns in the help and for our end users to "accidentally" pick the wrong one from!

Source Code: here (Visual Studio 2010, 20kb Zip Archive)
Build Environment: VS2010 + Oracle Client