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@xxxxxxxxxxxxxxxxxx> wrote: > What version of Oracle? > If 10gR2 onwards then you could maybe look at dbms_advanced_rewrite? > > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Leyi Zhang (Kamus) > *Sent:* 11 March 2010 03:50 > *To:* oracle-l@xxxxxxxxxxxxx > *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@xxxxxxxxx> > > 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@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13