Re: usage of calculated index with a synonym for the function

From: Lisa <lisaashleyrafter_at_gmail.com>
Date: Tue, 22 Dec 2009 11:09:30 -0800 (PST)
Message-ID: <6762c758-9c83-4adc-82e8-475bcfa8c04e_at_a21g2000yqc.googlegroups.com>

On Dec 14, 10:33 am, Norbert Winkler <norbert.winkl..._at_gmx.de> wrote:
> Hi,
>
> in a large table OUR_TABLE we have a calculated index for a persons-name
> with a simple soundex function OUR_SOUNDEX in the same schema (OUR_SCHEMA)
> like the table, thats different to my "select"-schema (WORK_SCHEMA):
> ...
> from OUR_SCHEMA.OUR_TABLE d
> where OUR_SCHEMA.OUR_SOUNDEX(d.PERSON_NAME) =
>       OUR_SCHEMA.OUR_SOUNDEX('Meier, Hans');
>
> But for building a testing environment schema-names are different
> (OUR_SCHEMA --> TEST_SCHEMA, WORK_SCHEMA --> WORK_TEST)
>
> Now I'm trying to create universal scripts with synonyms:
>
> in WORK_SCHEMA
> CREATE OR REPLACE SYNONYM OUR_SCHEMA_OUR_SOUNDEX
>   FOR OUR_SCHEMA.OUR_SOUNDEX;
>
> in WORK_TEST
> CREATE OR REPLACE SYNONYM OUR_SCHEMA_OUR_SOUNDEX
>   FOR TEST_SCHEMA.OUR_SOUNDEX;
>
> But
> ..
> from OUR_SCHEMA_OUR_TABLE d
> where OUR_SCHEMA_OUR_SOUNDEX(d.PERSON_NAME) =
>       OUR_SCHEMA_OUR_SOUNDEX('Meier, Hans');
>
> uses a full table scan in WORK_SCHEMA and TEST_SCHEMA.
>
> Is there another way to create universal scripts.
>
> --
> Norbert
> Oracle9i Enterprise Edition Release 11.2 64Bit
Wooooooooow nobody answered you!
You could use this SQL to find out what schema you're in: SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

(that'll tell if youre in work_schema or test_schema)

Then you can do an execute immediate and create the index. Here's the code:
declare

 v_name     varchar2(100);
 v_sql     varchar2(200);

begin
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') into v_name FROM dual; v_sql := 'CREATE OR REPLACE SYNONYM OUR_SCHEMA_OUR_SOUNDEX FOR ' || v_name || '.OUR_SOUNDEX;' ;
dbms_output.put_line (v_sql);
execute immediate v_sql;
end;

Hope that helps. Received on Tue Dec 22 2009 - 13:09:30 CST