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

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Thu, 11 Mar 2010 08:17:28 -0800
Message-ID: <26fdee6e1003110817n90150eas3938247b99b5472c_at_mail.gmail.com>

How about a Stored Outline, For example, tune statement using hints on one db, then move the Stored Outline to target db. Not sure if this would work.

Mike

On Wed, Mar 10, 2010 at 7:50 PM, Leyi Zhang (Kamus) <kamusis_at_gmail.com>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 Thu Mar 11 2010 - 10:17:28 CST