多層巢狀同義詞

在Oracle的管理員手冊上，說明可以為表、檢視、物化檢視、過程、函式、包、物件和序列建立同義詞。雖然Oracle並沒有明確說明，但是同樣可以為同義詞建立同義詞。

 

 

有些時候建立指向同義詞的同義詞是有意義的。由於同義詞本身功能之一就是實現物理結構和物理位置的透明性，因此使用者在建立同義詞的時候並不會太關係當前指向的物件是否是同義詞還是其他物件。

Oracle處理指向同義詞的同義詞只是在解析物件的時候存在很小的代價，其他並沒有太多的影響。

當然如果同義詞巢狀的層數過多，也不是很好的設計方式，使用者在確定要訪問真正的物件是會很麻煩，Oracle在分析物件的時候同樣也要花費時間：

SQL> create synonym s1 for t;

同義詞已建立。

SQL> create synonym s2 for s1;

同義詞已建立。

對於同義詞S2來說，就是一個巢狀同義詞，指向了同義詞S1。如果這種巢狀的層數太多，也會影響分析的效能。比如，一個巢狀了10000層的同義詞：

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  /

PL/SQL 過程已成功完成。

SQL> set timing on
SQL> set autot on
SQL> select * from t;

未選定行

已用時間:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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;

未選定行

已用時間:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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;

未選定行

已用時間:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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 s100;

未選定行

已用時間:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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 s1000;

未選定行

已用時間:  00: 00: 00.11

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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 s10000;

未選定行

已用時間:  00: 00: 01.20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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 s10000;

未選定行

已用時間:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF '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

可以看到，同義詞巢狀層數越多，Oracle就要花費越多的分析的時間來確定要訪問的物件。當然這個分析的結果是儲存在共享池內的，下次再次執行相同的查詢，就不需要重新分析了。