What is Hints and Why hints Require?

It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all.

Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.

You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ hints can take a wild optimizer and give you optimal performance

Tables analyze and update Analyze Statement

The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE employees COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

Automatic Optimizer Statistics Collection

By default Oracle 10g automatically gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB. By default this job runs within maintenance windows between 10 P.M. to 6 A.M. week nights and all day on weekends. The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that the internal job prioritizes the work such that tables most urgently requiring statistics updates are processed first.

Hint categories:

Hints can be categorized as follows:

• ALL_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  ALL_ROWS is usually used for batch processing or data warehousing systems.

(/*+ ALL_ROWS */) 

• FIRST_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  FIRST_ROWS is usually used for OLTP systems.

(/*+ FIRST_ROWS */) 

• CHOOSE
  One of the hints that 'invokes' the Cost based optimizer
  This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.

• Hints for Join Orders,
• Hints for Join Operations,
• Hints for Parallel Execution, (/*+ parallel(a,4) */)  specify degree either 2 or 4 or 16
• Additional Hints
• HASH
  Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

/*+ use_hash */

Use Hint to force using index

SELECT /*+INDEX (TABLE_NAME INDEX_NAME) */ COL1,COL2 FROM TABLE_NAME

 Select  ( /*+ hash  */ ) empno from

ORDERED-à This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

PARALLEL (table, instances)àThis specifies the operation is to be done in parallel.

If index is not able to create then will go for  /*+ parallel(table, 8)*/-----For select and update example---in where clase like st,not in ,>,< ,<> then we will use.

Explain plan

Explain plan will tell us whether the query properly using indexes or not.whatis the cost of the table whether it is doing full table scan or not, based on these statistics we can tune the query.

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:

SQL> CONN sys/password AS SYSDBA

Connected

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

SQL> GRANT ALL ON sys.plan_table TO public;

SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

What is your tuning approach if SQL query taking long time? Or how do u tune SQL query?

If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.

 it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.

If joining columns doesn’t have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query  it should give  better performance and also  needs to analyze the tables if analyzation happened long back. The ANALYZE statement can be used to gather statistics for a specific table, index or cluster using

ANALYZE TABLE employees COMPUTE STATISTICS;

If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like

• ALL_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  ALL_ROWS is usually used for batch processing or data warehousing systems.

(/*+ ALL_ROWS */) 

• FIRST_ROWS
  One of the hints that 'invokes' the Cost based optimizer
  FIRST_ROWS is usually used for OLTP systems.

(/*+ FIRST_ROWS */) 

• CHOOSE
  One of the hints that 'invokes' the Cost based optimizer
  This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
• HASH
  Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.

/*+ use_hash */

Hints are most useful to optimize the query performance.

What is the difference between sub-query & co-related sub query?

A sub query is executed once for the parent statement

whereas the correlated sub query is executed once for each

row of the parent query.

Sub Query:

Example:

 Select deptno, ename, sal from emp a  where sal  in (select sal from Grade  where sal_grade=’A’ or  sal_grade=’B’)

Co-Related Sun query:

Example:

Find all employees who earn more than the average salary in their department.

SELECT last-named, salary, department_id  FROM employees A

WHERE salary > (SELECT AVG (salary)

FROM employees B WHERE B.department_id =A.department_id

Group by B.department_id)

Sub-query	Co-related sub-query
A sub-query is executed once for the parent Query	Where as co-related sub-query is executed once for each row of the parent query.
Example: Select * from emp where deptno in (select deptno from dept);	Example: Select a.* from emp e where sal >= (select avg(sal) from emp a where a.deptno=e.deptno group by  a.deptno);

View & Materialized View & Inline View

View:

Why Use Views?

• To restrict data access

• To make complex queries easy

• To provide data independence

  A simple view is one that:

– Derives data from only one table

– Contains no functions or groups of data

– Can perform DML operations through the view.

 A complex view is one that:

– Derives data from many tables

– Contains functions or groups of data

– Does not always allow DML operations through the view

A view has a logical existence but a materialized view has 

a physical existence.Moreover a materialized view can be 

Indexed, analysed and so on....that is all the things that 

we can do with a table can also be done with a materialized 

view.

We can keep aggregated data into materialized view. we can schedule the MV to refresh but table can’t.MV can be created based on multiple tables.

Materialized View:

In DWH materialized views are very essential because in reporting side if we do aggregate calculations as per the business requirement report performance would be de graded. So to improve report performance rather than doing report calculations and joins at reporting side if we put same logic in the MV then we can directly select the data from MV without any joins and aggregations. We can also schedule MV (Materialize View).

Inline view:

If we write a select statement in from clause that is nothing but inline view.

Ex:

Get dept wise max sal along with empname and emp no.

Select a.empname, a.empno, b.sal, b.deptno 

From EMP a, (Select max (sal) sal, deptno from EMP group by deptno) b

Where

a.sal=b.sal and

a.deptno=b.deptno

Differences between where clause and having clause

Where clause	Having clause
Both where and having clause can be used to filter the data.
Where as in where clause it is not mandatory.	But having clause we need to use it with the group by.
Where clause applies to the individual rows.	Where as having clause is used to test some condition on the group rather than on individual rows.
Where clause is used to restrict rows.	But having clause is used to restrict groups.
Restrict normal query by where	Restrict group by function by having
In where clause every record is filtered based on where.	In having clause it is with aggregate records (group by functions).

Order of where and having:

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

The WHERE clause cannot be used to restrict groups. you use the

HAVING clause to restrict groups.

Difference between Rowid and Rownum?

ROWID

A globally unique identifier for a row in a database. It is created at the time the row is inserted into a table, and destroyed when it is removed from a table.'BBBBBBBB.RRRR.FFFF' where BBBBBBBB is the block number, RRRR is the slot(row) number, and FFFF is a file number.

ROWNUM

For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

Rowid	Row-num
Rowid is an oracle internal id that is allocated every time a new record is inserted in a table. This ID is unique and cannot be changed by the user.	Row-num is a row number returned by a select statement.
Rowid is permanent.	Row-num is temporary.
Rowid is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed from a table.	The row-num pseudocoloumn returns a number indicating the order in which oracle selects the row from a table or set of joined rows.

What is the Difference between Delete, Truncate and Drop?

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. The operation cannot be rolled back.

What is the difference between view and materialized view?

View	Materialized view
A view has a logical existence. It does not contain data.	A materialized view has a physical existence.
Its not a database object.	It is a database object.
We cannot perform DML operation on view.	We can perform DML operation on materialized view.
When we do select * from view it will fetch the data from base table.	When we do select * from materialized view it will fetch the data from materialized view.
In view we cannot schedule to refresh.	In materialized view we can schedule to refresh.
	We can keep aggregated data into materialized view. Materialized view can be created based on multiple tables.