Prev: Basic Tables in Oracle Financials
Next: what book for 9i or 10g forms?
From: Norbert Winkler on 14 Dec 2009 10:33
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
From: Lisa on 22 Dec 2009 14:09
On Dec 14, 10:33 am, Norbert Winkler <norbert.winkl...(a)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.


 | 
Pages: 1
Prev: Basic Tables in Oracle Financials
Next: what book for 9i or 10g forms?