Tuesday, March 15, 2011

You Cannot Analyze a Table Through a Synonym

This was tested on an Oracle 11 database. First I created a table and analyzed it: 

SQL> create table andrew1 (one_col number)
  2  /

Table created.

SQL> analyze table andrew1 compute statistics
  2  /

Table analyzed.

SQL>

Then I created a synonym for the table:

SQL> create synonym andrew2 for andrew1
  2  /

Synonym created.

SQL>

I found that I was able to describe the table via the synonym:

SQL> desc andrew2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ONE_COL                          NUMBER

SQL>

... but I could not analyze it:

SQL> analyze table andrew2 compute statistics
  2  /
analyze table andrew2 compute statistics
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

1 comment:

Nandkishor Wagh said...

attractive piece of information, I had come to know about your blog from my friend arjun, ahmedabad,i have read atleast eleven posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Synonyms In Oracle