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

From: <japplewhite_at_austinisd.org>
Date: Thu, 11 Mar 2010 08:55:29 -0600
Message-ID: <OFE32FB748.D78C5809-ON862576E3.005102EB-862576E3.0052021F_at_austinisd.org>

Yes, that's what I did for an App we have that repeatedly queried All_Tables with predicates Upper(Table_Name) and Upper(Owner). That App did use the results so I couldn't provide an empty substitute. That database has over 200,000 tables, so, the query was slower than in other databases and, because it was issued so many times, really chewed up CPU.

I created an All_Tables Index-Organized Table (on Table_Name,Owner) in the App's schema, plus a Function-Based Index on Upper(Table_Name), Upper(Owner). The private All_Tables table is truncated and refreshed nightly from Sys.All_Tables.

Made a huge difference for that App and didn't disturb other Apps.

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9715 (wk) / 512.935.5929 (pager)

From: "Leyi Zhang (Kamus)" <kamusis_at_gmail.com>

To:     tim_at_evdbt.com
Cc:     oracle-l_at_freelists.org
Date:   03/11/2010 08:40 AM
Subject:        Re: Is possible to return the SQL result but not query the 
table?
Sent by:        oracle-l-bounce_at_freelists.org

Thanks Tim, I'm always find useful resources from your EvDBT.com :-) I'll try to create these 2 tables in the application user schema and make a test if it will harm to the other function.

create table app.all_tab_columns as SELECT * from sys.all_tab_columns where 1=0;
create table app.all_ind_columns as SELECT * from sys.all_ind_columns where 1=0;

Thanks for your advice.  

--
Kamus <kamusis_at_gmail.com>

Oracle8i & 9i Certified DBA from China
Oracle ACE
Visit my blog for more article: http://www.dbform.com


On Thu, Mar 11, 2010 at 12:30 PM, Tim Gorman <tim_at_evdbt.com> wrote:
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 Thu Mar 11 2010 - 08:55:29 CST