RE: Is possible to return the SQL result but not query the table?
- From: "Brooks, Dominic (London)(c)" <dbrooks@xxxxxxxxxxxxxxxxxx>
- To: <toon.koppelaars@xxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Mar 2010 10:22:35 -0000
You're right.
Just did a test:
ORA-30354: Query rewrite not allowed on SYS relations
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Toon Koppelaars
Sent: 11 March 2010 09:44
To: Brooks, Dominic (London)(c)
Cc: kamusis; oracle-l
Subject: Re: Is possible to return the SQL result but not query the
table?
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
Other related posts:
