Re: Difference between oracle Synonyms and Views.

> True to form, that wanker Dan Morgan doesn't know the answer and once
> again sends someone to an unsupported reference site.

Sorry, but your information is incorrect, as I'll get to below. So I wouldn't go around calling someone a "wanker" and spouting off that someone doesn't know the answer, when your own words clearly show that you do not know the answers as well.  

> Quite simply:
>
> A Synonym creates a copy of the data but under a different name. You
> see, you don't want people having to remember difficult names. Plus it
> allows for data redundancy which cuts down on having to buy more kit.
> This may influence the backup schedule but, who knows, give 'er a try.

A synonym does not "create a copy of the data but under a different name". It never has. There is no data copying going on when you create a synonym. This is completely false. Since this is false, the rest of the paragraph which discusses data redundancy and backup schedules is also inaccurate. To show you what I mean, look at the following:

In an Oracle database, data is stored in a "segment". So I'll query the total number of segments in my system:

ORA9I SQL> select count(*) as num_segments from dba_segments;

NUM_SEGMENTS

        1134

Now, I'll create a synonym:

ORA9I SQL> create synonym test_synonym for peasland.test;

Synonym created.

Finally, I'll query for the total number of segments in the system again:

ORA9I SQL> select count(*) as num_segments from dba_segments;

NUM_SEGMENTS

        1134

Notice that no new segments were created. If no new segments were created, then no new structure *that holds data* was created!!! This is but one piece of proof to show that synonyms do not copy data. Other proofs could easily be constructed.

> A View, on the other hand, is sort of like a clustered index.

Totally inaccurate. As has been stated by Tanel in this thread, a view is a stored SQL statement. A clustered index is a physical storage structure. A view does not take any physical storage apart from storing the *definition* of the view in the data dictionary. When one queries from a view, the system merges the view's SQL statement with the rest of the query to obtain the result set. If a view was anything sort of like a clustered index, then it would require physical storage space. And taking my proof one step further, we can see that it does not:

ORA9I SQL> create view test_view as select * from peasland.test;

View created.

ORA9I SQL> select count(*) as num_segments from dba_segments;

NUM_SEGMENTS

        1134

> Simon Adebisi, OCP
> Sr. Oracle Architect

OCP? I know that many people don't like the OCP program, and this type of stuff shows why. "Senior" Oracle Architect? Synonyms and Views are Oracle DBA 101 type of stuff. It shouldn't be that hard to keep these concepts straight.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"