Materialized View as wrapper for LONG data dictionary columns

In an Apex application I recently run into the following performance issue. Opening a form in EDIT mode was fast, but opening it in CREATE mode took a long time (several seconds). The reason could be traced down quickly to a function call that provided a default value for a column.

The idea of this was a pretty nice one. Instead of hardcoding the default value for the APEX item a lookup into the data dictionary was made and the default value from the column definition was used. So whenever the default value on the column changes, the default in Apex would match that.

The intention was very good. However the implementation was as poor as it can get.

The default value for a column is in the user_tab_cols data dictionary view.

desc user_tab_columns
Name                 Null     Typ           
-------------------- -------- ------------- 
TABLE_NAME           NOT NULL VARCHAR2(30)  
COLUMN_NAME          NOT NULL VARCHAR2(30)  
DATA_TYPE                     VARCHAR2(106) 
DATA_TYPE_MOD                 VARCHAR2(3)   
DATA_TYPE_OWNER               VARCHAR2(120) 
DATA_LENGTH          NOT NULL NUMBER        
DATA_PRECISION                NUMBER        
DATA_SCALE                    NUMBER        
NULLABLE                      VARCHAR2(1)   
COLUMN_ID                     NUMBER        
DEFAULT_LENGTH                NUMBER        
DATA_DEFAULT                  LONG()        
NUM_DISTINCT                  NUMBER        
LOW_VALUE                     RAW(32 BYTE)  
HIGH_VALUE                    RAW(32 BYTE)  
DENSITY                       NUMBER        
NUM_NULLS                     NUMBER        
NUM_BUCKETS                   NUMBER        
LAST_ANALYZED                 DATE          
SAMPLE_SIZE                   NUMBER        
CHARACTER_SET_NAME            VARCHAR2(44)  
CHAR_COL_DECL_LENGTH          NUMBER        
GLOBAL_STATS                  VARCHAR2(3)   
USER_STATS                    VARCHAR2(3)   
AVG_COL_LEN                   NUMBER        
CHAR_LENGTH                   NUMBER        
CHAR_USED                     VARCHAR2(1)   
V80_FMT_IMAGE                 VARCHAR2(3)   
DATA_UPGRADED                 VARCHAR2(3)   
HISTOGRAM                     VARCHAR2(15)

The problem is this column has the deprecated datatype LONG. Unfortunately Oracle still uses this datatype internally. For virtual columns the default value will be the expression that is used to calculate the virtual column value. This can explain why a large type is needed. However a CLOB should do the job too.

There are only a few ways to convert a LONG datatype into a string (VARCHAR2 or CLOB). Here is a nice overview about possible solutions and workarounds:  http://www.oracle-developer.net/display.php?id=430 . I strongly suggest to go through that article and read up upon the possibilities.

The solution that was implemented used a view v_db_defaults. The creator of that view probably had read the previous link and used method three: dbms_xmlgen.

This is the view:


WITH
t_sql
AS (SELECT    'SELECT utc.table_name AS tabelle_name'
|| ', utc.column_name AS spalte_name'
|| ', utc.data_type AS datentyp'
|| ', utc.data_default AS default_wert'
|| ', DECODE(utc.virtual_column, ''YES'', ''J'', ''N'') AS spalte_virtuell'
|| ' FROM user_tab_cols utc'
|| ' INNER JOIN user_tables ut'
|| ' ON ('
|| 'ut.table_name = utc.table_name'
|| ')'
AS db_befehl
FROM dual
)
-- Defaultwerte aus Datenbank LONG in das XML Format umwandeln
,t_xml
AS (SELECT XMLTYPE(DBMS_XMLGEN.getxml(sql.db_befehl)) AS db_defaults
FROM t_sql sql
)
-- XML-Daten in tabelarische Form umwandlen (inkl. der Defaultwerte im Textformat)
SELECT
CAST(extractValue(def.object_value, '/ROW/TABELLE_NAME') AS VARCHAR2(30)) AS tabelle
,CAST(extractValue(def.object_value, '/ROW/SPALTE_NAME') AS VARCHAR2(30))  AS spalte
--
-- Sofern ein Defaultwert in einer Tabelle gesetzt und anschließend gelöscht wurde
-- (auf NULL gesetzt) kann es vorkommen, dass als Defaultwert der String NULL in der
-- Datenbank abgelegt ist. Diesen String fangen wir ab und wandeln ihn in das echte NULL.
-- TRIM stellt sicher, dass die Leerzeichen, die manachmal im Wert
--    extractValue(def.object_value, '/ROW/DEFAULT_WERT')
-- auftreten entfernt werden.
,CAST(extractValue(def.object_value, '/ROW/DATENTYP') AS VARCHAR2(64)) AS datentyp
,TRIM(BOTH CHR(13)
FROM TRIM(BOTH CHR(10)
FROM CASE UPPER(TRIM(extractValue(def.object_value, '/ROW/DEFAULT_WERT')))
WHEN 'NULL'
THEN
NULL
ELSE
TRIM(extractValue(def.object_value, '/ROW/DEFAULT_WERT'))
END
)
)                                                                       AS default_wert
,CAST(extractValue(def.object_value, '/ROW/SPALTE_VIRTUELL') AS VARCHAR2(1)) AS virtuell
FROM t_xml xml
,TABLE(XMLSEQUENCE(EXTRACT(xml.db_defaults, '/ROWSET/ROW'))) def

;

The view then was used in a function to fetch the default value from it by such a select

SELECT TRIM(BOTH CHR(13) FROM TRIM(BOTH CHR(10) FROM TRIM(default_wert)))
--INTO l_default_wert
FROM v_db_default
WHERE tabelle = 'MYTABLE'
AND spalte  = 'MYCOLUMN';

The performance issue is obvious. The full data dictionary is read and converted into XML. Then the columns are extracted using the extractvalue function. Which is deprecated since 11.2.0.1 (see https://docs.oracle.com/database/121/ADXDB/changes.htm#ADXDB5978). The filter on the table and column name can’t easily be included into the data dictionary call. So I decided to change it.

The quick solution would have been to simply add the default value as a literal in APEX. But there can be other areas where this function and the view might be used. I wanted to improve those areas too.

The oak table has a nice solution using a pipelined table function. But this requires some setter methods to push the filter criteria to the functions. This is cumbersome to develop.

Another way to handle a LONG datatype is to convert it with to_lob into a clob. Unfortunately to_lob is limited. We can’t use it in a normal select statement. A simple select statement such as this


select table_name, column_name, to_lob(DATA_DEFAULT)
from user_tab_columns
where data_default is not null;

results in an error ORA-00932: Inkonsistente Datentypen: – erwartet, LONG erhalten 00932. 00000 –  “inconsistent datatypes: expected %s got %s”

The documentation states that to_lob works in a CREATE TABLE statement. But the good news is, it also works in a materialized view (insert /*+ append */.. select). This can be used to our advantage.


CREATE MATERIALIZED VIEW MV_DB_DEFAULT (TABELLE, SPALTE, DATENTYP, DEFAULT_WERT, SPALTE_VIRTUELL)
NOLOGGING
TABLESPACE 'TBS_NOT_BACKUPED'
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND 
AS SELECT  utc.table_name AS tabelle
,utc.column_name AS spalte
,utc.data_type AS datentyp
,to_lob(utc.data_default) AS default_wert
--,utc.virtual_column
,DECODE(utc.virtual_column, 'YES', 'J', 'N') AS spalte_virtuell
FROM user_tab_cols utc
JOIN user_tables ut ON ut.table_name = utc.table_name
;

CREATE UNIQUE INDEX MV_DB_DEFAULT_TABCOL_IX ON MV_DB_DEFAULT (TABELLE, SPALTE)
TABLESPACE 'TBS_NOT_BACKUPED';

-- add a job to refresh the view using atomic_refresh=FALSE

Declare
  v_jobname varchar2(61) := 'REFRESH_MV';
BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => v_jobname,
            job_type => 'PLSQL_BLOCK',
            job_action => 'dbms_mview.refresh(''MV_DB_DEFAULT'',atomic_refresh=>false);',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=12;BYMINUTE=0;BYSECOND=0',
            end_date => NULL,
            job_class => '"SYS"."DEFAULT_JOB_CLASS"',
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'refresh Materialized Views'
            );
 
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => v_jobname, 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
  
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => v_jobname, 
             attribute => 'max_run_duration', value => INTERVAL '1' HOUR);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => v_jobname, 
             attribute => 'schedule_limit', value => INTERVAL '1' HOUR);    
END; 
/

The view will refresh every week. Data dictionary changes, especially to default values, should be fairly rare. So one could consider to not automatically refresh the view. Just on demand whenever there is a code change written into the database. I feel a weekly refresh is a solid compromise. The access to the view itself is fast, since I added a unique index on table and column name. Performance comparison between the original view and the MV:

SELECT TRIM(BOTH CHR(13) FROM TRIM(BOTH CHR(10) FROM TRIM(default_wert)))
FROM v_db_default
WHERE tabelle = 'MYTABLE'
AND spalte  = 'MYCOLUMN';
Elapsed: 00:00:01.373
SELECT TRIM(BOTH CHR(13) FROM TRIM(BOTH CHR(10) FROM TRIM(default_wert)))
FROM mv_db_default
WHERE tabelle = 'MYTABLE'
AND spalte  = 'MYCOLUMN';
Elapsed: 00:00:00.015

Problem solved.

Side note: The old view returned varchar2(4000), the new materialized view returns a clob. However this is called from a pl/sql api function and therefore doesn’t need to be considered. Additionally the original view had some special consideration for columns where the default value has ‘NULL’ in the dictionary. This can be handled on the MV level, but I added some additional check inside the function that does the select on the MV.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s