Synonyms in Oracle.

Synonyms are aliases for objects in database. Synonyms make it easy for a schema to access objects in other schema. Normally objects in other schema can be accessed by prefixing schema name to the object, but with the use of synonym there is no need to use schema name while accessing the object.

Synonyms can be public as well as private.

Public Synonym : Public synonyms are objects of public schema and can be accessed by any schema in the same or different database(assuming databases are connected by a link).

Let's see how to create a public synonym.

CREATE PUBLIC SYNONYM tab1 FOR sch1.tab1;

Now the table tab1 can be accessed by any schema using name tab1.

Private Synonym : Private synonyms are private to a schema. They can be referenced only by schema which owns the object. Private synonym can be created by removing the private keyword from the above CREATE statement.

CREATE SYNONYM tab1 FOR sch1.tab1;

Note :

1) Knowing a public synonym for an object doesn't mean that a schema can access the object as it wants. Access policies to the object will always be determined by access permissions granted to the schema for the object.

2) Permission for creation of a public synonym has to be granted by DBA.

For more info visit.

Oracle WITH Clause.

For more info visit

WITH clause is also known as Subquery factoring clause. It is used to simplify the structure of complex queries, and in some cases, optimize the queries.

With clause works in the same way as a Temporary table or an inline view, but the main difference between With clause and, an inline view or a Temporary table is that, a query written using With clause is less complex and easier to understand.

When we use a With clause, it depends on the optimizer whether it will make temporary table or an inline view, corresponding to the subquery in the With clause. This also depends on the subqueries used in With clause, if the queries are too complex then the optimizer may create Temporary tables ,else an inline view is sufficient.

We can also pass Optimizer hints in the subqueries written in with clause, to request the optimizer for an inline view or a Temporary table.

Select /*+ MATERIALIZE */ .... For Temporary table

Select /*+ INLINE */ ..... For Inline view

Now let's see an example, with and without With clause.

Suppose I want to find out the names of the employees having salary greater than the average salary of the department in which they work.

I can write this query in 3 ways.

1) Using Corelated Subqueries.

select ed1.name from employee_details ed1
where salary>(select avg(salary) from
employee_details ed2 where
ed1.department=ed2.department)

Writing corelated subquery is fine when data in the tables is less, but when there is a large amount of data in tables then this approach may not be suitable. For each employee in the table, the subquery will execute. So, we discard this approach in case of huge amount of data in tables.

2) The second approach is to create Inline view.

select ed1.name from employee_details ed1,
(Select avg(salary) sal,department
from employee_details group by
department) ed2 where ed2.department=ed1.department
and ed1.salary > ed2.sal

Here, an inline view is created, which is more efficient in case of huge amount of data. The query given above contains only one inline view, but what about the queries containing many inline views. It will become very complex to understand.

3) Using With Clause.

There is not much difference between aWith clause and an inline view, except the way of writing queries becomes more understandable.

Now, let's see the same query using a With Clause.

With ed2 as(Select avg(salary) sal,department
from employee_details group by
department)
select ed1.name from employee_details ed1,
ed2
where ed1.department=ed2.department and
ed1.salary>ed2.sal

The queries using With clause starts with With instead of Select, which may seem wierd at first, but as you start using With Clause, it will become inseparable part of  writing simple and understandable queries.

With clause may come handy in a lot of other situations(demanding subqueries), the only limit is how efficiently we can reduce the complexity of our queries using With Clause.