Yii 1.1: Increasing AR performance in connections with Oracle || Insert queries for Managing caching tables

Hi,

You can use the following query to insert the into the caching tables. Just change the <YOUR_TABLE_NAME> into your table. This SQL Script will automatically insert all the data needed for the caching tables.

--INSERT INTO TABLES
INSERT INTO WWW_TABLES (TABLE_NAME) VALUES ('<YOUR_TABLE_NAME>');
 
--INSERT QUERY FOR ORACLE SCHEMA CATCHING
INSERT INTO WWW_TAB_COLUMNS
  (COLUMN_ID,TABLE_NAME, COLUMN_NAME, DATA_TYPE, NULLABLE, "KEY")
 
  SELECT A.COLUMN_ID,'<YOUR_TABLE_NAME>' AS TABLE_NAME,
         a.column_name,
         a.data_type || case
           when data_precision is not null then
            '(' || a.data_precision || case
              when a.data_scale > 0 then
               ',' || a.data_scale
              else
               ''
            end || ')'
           when data_type = 'DATE' then
            ''
           else
            '(' || to_char(a.data_length) || ')'
         end as data_type,
         a.nullable,
         (SELECT D.constraint_type
            FROM user_CONS_COLUMNS C
           inner join user_constraints D
              On D.constraint_name = C.constraint_name
           Where C.table_name = A.TABLE_NAME
             and C.column_name = A.column_name
             and D.constraint_type = 'P') as Key
    FROM user_TAB_COLUMNS A
   WHERE A.TABLE_NAME = '<YOUR_TABLE_NAME>'
   ORDER by a.column_id;
 
-- INSERT IF ANY RELATION AVAILABLE BETWEEN THE TABLES
INSERT INTO WWW_TAB_CONS (TABLE_NAME,COLUMN_NAME,POSITION,R_CONSTRAINT_NAME,TABLE_REF,COLUMN_REF) 
SELECT '<YOUR_TABLE_NAME>' AS TABLE_NAME,
       C.COLUMN_NAME,
       C.position,
       D.r_constraint_name,
       E.table_name        as table_ref,
       f.column_name       as column_ref
  FROM ALL_CONS_COLUMNS C
 inner join ALL_constraints D
    on D.OWNER = C.OWNER
   and D.constraint_name = C.constraint_name
  left join ALL_constraints E
    on E.OWNER = D.r_OWNER
   and E.constraint_name = D.r_constraint_name
  left join ALL_cons_columns F
    on F.OWNER = E.OWNER
   and F.constraint_name = E.constraint_name
   and F.position = c.position
 WHERE C.table_name = '<YOUR_TABLE_NAME>'
   and D.constraint_type = 'R'
 order by d.constraint_name, c.position;

Hope this will help you all!

Thanks in advance

Cheers!

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