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

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 10 Mar 2010 21:30:22 -0700
Message-ID: <4B9871DE.7030900_at_evdbt.com>

Kamus,

Can you create no-op views named ALL_TAB_COLUMNS and ALL_IND_COLUMNS in the schema to which this application connects, without breaking anything else?

Just something to consider *carefully*...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

Leyi Zhang (Kamus) wrote:

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 - 22:30:22 CST