SQL Optimization interview questions with answers

In every interview you might ask the basic questions related to SQL Performace. Because SQL Performance improvement is very essential factor for any project. I would like to give more information about SQL Optimization interview questions and answers. I want to cover the basic as well as advanced interview questions that might asked in Interview related to Performance improvement of data. The Performance of the SQL is related to lot of factors like which are different indexes used, What hardware is using, what is system hardware and all. In this article I will cover 20 most important SQL Optimization interview questions that might ask in interview.

SQL Optimization interview questions:

1.What are different parameters to consider the database performance of Application?

Answer:

There are lot of parameters to consider the performance of application:

1.What size of images we are using in application.The images we are using on application should not be maximum size.

2.What is the data volume used to fetch the data

3.Data cardinality: The most important factor is data cardinality of the data in application.Data should be divided in proper manner and the database should be in well normalized form

4.Indexing done:Indexing should be done properly in database (Click here for index info)

2.What are indexes in SQL?(90 % asked in Performance Tuning Interview Questions)

Answer:

“Index is optional structure associated with the table which may or may not improve the performance of Query”

In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.

Click Here to Get 20 most important complex sql queries…

3.What are advantages of Indexes?

Answer:

Indexes are memory objects which are used to improve the performance of queries which allows faster retrieval of records.

Following are advantages of Indexes:

1.It allows faster retrieval of data

2.It avoids the Full table scan so that the performance of retrieving data from the table is faster.

3.It avoids the table access alltogether

4.Indexes always speeds up the select statement.

5.Indexes used to improve the Execution plan of the database

4.What are disadvantages of Indexes?(80 % asked in Performance Tuning Interview Questions)

Disadvantages:

1.Indexes slows down the performance of insert and update statements.So always we need follow best practice of disabling indexes before insert and update the table

2.Indexes takes additional disk space so by considering memory point indexes are costly.

5.What is parser?

Answer:

When SQL Statement has been written and generated then first step is parsing of that SQL Statement.Parsing is nothing but checking the syntaxes of SQL query.All the syntax of Query is correct or not is checked by SQL Parser.

There are 2 functions of parser:

1.Syntax analysis

2.Semantic analysis

Click Here to know more about Parser….

6.What are functions of Parser?

1.Syntax Analysis:

The parser checks for SQL statement syntaxs. If the syntax is incorrect then parser gives the incorrect syntax error.

2.Semantic Analysis:

This checks for references of object and object attributes referenced are correct.

7.What is mean by implicit index.Explain with example.

Answer:

Whenever we define unique key or primary key constraints on the table  the index will automatically create on the table.These indexes are known as implicit indexes because these are created implicitly whenever the constraint has been applied to the table.These indexes are normal indexes not unique indexes.The indexes are normal because the columns already have defined as unique so uniqueness is already been applied.

Example:

Create table Employee

(Employee_ID  varchar2(20) primary key,

Employee name varchar2(50),

salary number(10,0) not null);

If We check description of table:

Desc Employee;

Name                     Null                 Type

——————————————–

Employee_ID        not null      varchar2

Employee_name                      varchar2

Salary                    not null         number

Here you will see index is already created for Employee_ID as it has defined primary key.

Click Here to get information about indexes….

8.What are Explicit Indexes?

Answer:

The indexes which is created by user are called as explicit indexes.You can say the indexes which are created by ‘Create Index’ statement are called as Explicit indexes.

Syntax:

create index indexname on tablename(columnname);

Example:

Create index IND_Employee_ID on Employee(Employee_ID);

9.What are different types of indexes?

Answer:

There are following types of indexes:

1.Normal Indexes

2.Bit map indexes

3.B-tree Indexes

4.Unique Indexes

5.Function Based Indexes

10.What is mean by Unique Indexes?

Answer:

1.To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.

2.Especially while creating the table if we specify the primary key  then unique index is automatically created on that column.

3.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

4.The unique indexes are also called as clustered indexes when primary key is defined on the column.

Example:

Create Unique index  Index_name on Table_name(Unique column name);

Example:

CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

Click Here to get information on basics of Performance Tuning..

11.What are functional Based indexes?Explain with Example

Answer:

1.Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query.

2.As per requirements we are using lot of SQL functions to fetch the results.Function based indexs gives ability to index the computed columns.

3.Function based indexes are easy to implement and it also provides immediate value.These indexes speeds up the application without changing application code or query.

Example:

Syntax:

Create index indexname on tablename(Function_name(column_name));

Example:

Create index FI_Employee on Employee(trunc(Hire_date));

12.What is Bit-map index?Explain with Example.(90 % Asked in Performance Tuning Interview Questions)

Answer:

1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.

2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.

3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.

4.Means If in  1 million  records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.

Syntax:

Create bitmap index Index_name on Table_name(Columns which have distinct values);

Example:

CREATE BITMAP index  BM_DEPT_NAME on DEPT(Department_name);

13.What is Optimizer?

Answer:

Optimizer is nothing but the execution of query in optimum manner.Optimizer is most efficient way of processing the query.SQL parser ,SQL Optimizer and source code generator compiles the SQL statement.

14.What are types of SQL Optimizer?

Answer:

There are following types of optimizer:

1.Rule Based Optimizer

2.Cost Based Optimizer

15.Explain Rule Based Optimizer?

Answer:

When we execute any SQL statement ,the optimizer uses the predefined rules which defines what indexes are present in the database and which indexes needs to be executed during the execution.Rule Based optimizer is used to specify which table is been full scanned and which tables are taking the indexes during the execution.In Earlier the only optimizer which is used by Oracle is Rule Based optimizer

“Rule Based Optimizer  specifies the rules for how to execute the query.”

Reference:Click here

16.What is composite index?(90% asked in Performance Tuning Interview Questions)

Answer:

When 2 or more columns are related to each other in the table and the same columns are used in where condition of the query then user can create index on both columns.These indexes are known as composite indexes.

Example:

Create index CI_Employee on Employee(Eno,Deptno);

17.What is cost based optimizer?

Answer:

Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself  decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on  the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.

“Cost based optimizer considers the statastical information of the table for query execution”

Click here to get most important questions asked in Tech Mahindra..

17.What is visible/invisible property of index?

Answer:

User can make the indexes visible and invisible by altering the indexes.Following statement is used to make indexes visible and invisible.

ALTER INDEX index_name VISIBLE;

18.What is mean by Clustered index?

Answer:

1.The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database.

2.Clustered indexes are created once for each table.When primary key is created then clustered index has been automatically created in the table.

3.If table is under heavy data modifications the clustered indexes are preferable to use.

19.Can Index be Renamed?If Yes How?(90% asked inPerformance Tuning Interview Questions)

Answer:

Yes we can rename the indexes.User should have create any index privilege to rename the index.

Alter index Index_name Rename to New_indexname;

20.What is mean by non clustered indexes?(90 % asked in Performance Tuning Interview Questions)

Answer:

1.The clustered indexes are used for searching purpose as we can create clustered indexes where primary is is defined.But Non clustered indexes are indexes which will be created on the multiple joining conditions,multiple filters used in query.

2.We can create 0 to 249 non-clustered indexes on single table.Foreign keys should be non clustered.

3.When user wants to retrieve heavy data from fields other than primary key the non clustered indexes are useful.

Hope everyone likes this article on SQL Optimization interview questions and answers.If you want PDF of this article kindly comment here.

HOME