Is possible to return the SQL result but not query the table?

From: Leyi Zhang (Kamus) <"Leyi>
Date: Thu, 11 Mar 2010 11:50:29 +0800
Message-ID: <94b9f9d01003101950s342eb5e1x60cd304a7bdfe748_at_mail.gmail.com>

Hi, gurus

In our application, there is a SQL we can't modify, can't remove:

SELECT 1 AS scope,

       'ROWID' AS column_name,
       -8 AS data_type,
       'ROWID' AS type_name,
       0 AS column_size,
       0 AS buffer_length,
       0 AS decimal_digits,
       2 AS pseudo_column

  FROM DUAL
 WHERE :1 = 1
UNION
SELECT 2 AS scope,

       t.column_name,
       DECODE(t.data_type,


              'CHAR',

              1,


              'VARCHAR2',

              12,


              'NUMBER',

              3,


              'LONG',

              -1,


              'DATE',

              91,


              'RAW',

              -3,


              'LONG RAW',

              -4,


              'TIMESTAMP(6)',

              93,


              'TIMESTAMP(6) WITH TIME ZONE',

              -101,


              'TIMESTAMP(6) WITH LOCAL TIME ZONE',

              -102,


              'INTERVAL YEAR(2) TO MONTH',

              -103,


              'INTERVAL DAY(2) TO SECOND(6)',

              -104,


              'BINARY_FLOAT',

              100,


              'BINARY_DOUBLE',

              101,
              1111) AS data_type,
       t.data_type AS type_name,
       DECODE(t.data_precision, null, t.data_length, t.data_precision) AS
column_size,
       0 AS buffer_length,
       t.data_scale AS decimal_digits,
       1 AS pseudo_column

  FROM all_tab_columns t, all_ind_columns i  WHERE :2 = 1

   AND t.table_name = :3
   AND t.owner like :4 escape '/'
   AND t.nullable != :5
   AND t.owner = i.table_owner
   AND t.table_name = i.table_name
   AND t.column_name = i.column_name

But this SQL always consume a lot CPU, a lot of Gets, always be the Top 1 SQL. This SQL's result is not been used anymore, but I can't access the program code, so my question is:
Can I do something in the database side, and tell the database when this SQL runs, just return a static result, avoid to querying all_tab_columns and all_ind_columns views?

--
Kamus <kamusis_at_gmail.com>

Oracle8i & 9i Certified DBA from China
Visit my blog for more article: http://www.dbform.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 10 2010 - 21:50:29 CST