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

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@xxxxxxxxx>

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@xxxxxxxxx> 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@xxxxxxxxx
> 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@xxxxxxxxx>
>
> Oracle8i & 9i Certified DBA from China
> Visit my blog for more article: http://www.dbform.com
>
>

Other related posts: