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

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Thu, 11 Mar 2010 10:43:36 +0100
Message-ID: <ecf3dae71003110143j33f72551t1124c36661e9d05f_at_mail.gmail.com>

If I recall correctly, dbms_advanced_rewrite is not allowed when SYS objects are involved.

On Thu, Mar 11, 2010 at 10:19 AM, Brooks, Dominic (London)(c) < dbrooks_at_maninvestments.com> wrote:

> What version of Oracle?
> If 10gR2 onwards then you could maybe look at dbms_advanced_rewrite?
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Leyi Zhang (Kamus)
> *Sent:* 11 March 2010 03:50
> *To:* oracle-l_at_freelists.org
> *Subject:* Is possible to return the SQL result but not query the table?
>
> 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
>
> **********************************************************************
>
> Please consider the environment before printing this email or its
> attachments.
>
> The contents of this email are for the named addressees only. It contains
> information which may be confidential and privileged. If you are not the
> intended recipient, please notify the sender immediately, destroy this email
> and any attachments and do not otherwise disclose or use them. Email
> transmission is not a secure method of communication and Man Investments
> cannot accept responsibility for the completeness or accuracy of this email
> or any attachments. Whilst Man Investments makes every effort to keep its
> network free from viruses, it does not accept responsibility for any
> computer virus which might be transferred by way of this email or any
> attachments. This email does not constitute a request, offer, recommendation
> or solicitation of any kind to buy, subscribe, sell or redeem any investment
> instruments or to perform other such transactions of any kind. Man
> Investments reserves the right to monitor, record and retain all electronic
> communications through its network to ensure the integrity of its systems,
> for record keeping and regulatory purposes.
>
> Visit us at: www.maninvestments.com
>
> TG0908
>
> **********************************************************************
>
>
>

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

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

Received on Thu Mar 11 2010 - 03:43:36 CST