Oracle Multilayer nested synonyms

In Oracle's administrator's manual, you can establish synonyms for tables, views, materialized views, procedures, functions, packages, objects, and sequences. Although Oracle does not explicitly specify, it is also possible to create synonyms for synonyms.

Sometimes it is meaningful to establish synonyms that point to synonyms. Since one of the functions of the synonym itself is to achieve transparency in both physical and physical locations, the user does not have to relate to whether the object currently pointed to is a synonym or other object when creating a synonym.

Oracle processing of synonyms that point to synonyms only has a small price to pay for parsing objects, and there are few other effects.

Of course, if the synonyms are nested too many layers, nor is it a good way to design, the user is determined to access the real object is cumbersome, Oracle in the analysis of the object will also take time:

sql> create synonym S1 for t;

The synonym has been created.

sql> create synonym S2 for s1;

The synonym has been created.

For synonym S2, it is a nested synonym that points to the synonym S1. This nesting can also affect the performance of the analysis if too many layers are nested. For example, a synonym with 10000 layers is nested:

Sql> begin

2 for I in 2..10000 loop

3 Execute immediate ' create synonym S ' | | (i+1) | | ' For S ' | | I

4 End Loop;

5 end;

6/

The PL/SQL process has completed successfully.

Sql> Set Timing on

Sql> set Autot on

Sql> select * from T;

No rows selected

Time used: 00:00:00.02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Ptimizer=choose

1 0 TABLE ACCESS (full) ' T '

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

3 Consistent gets

0 physical Reads

0 Redo Size

267 bytes sent via sql*net to client

460 Bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed

Sql> select * from S1;

No rows selected

Time used: 00:00:00.02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Ptimizer=choose

1 0 TABLE ACCESS (full) ' T '

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

3 Consistent gets

0 physical Reads

0 Redo Size

267 bytes sent via sql*net to client

460 Bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed

Sql> select * from S2;

No rows selected

Time used: 00:00:00.02

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Ptimizer=choose

1 0 TABLE ACCESS (full) ' T '

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

3 Consistent gets

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45462.htm