Data Mart vs Data warehouse :

Data warehouse Data mart Data warehousing is subject oriented, time variant, non-volatile collection of data which is used for creation of transactional reports as well as historical reports. Data Mart is simply a subset of Organization’s Data warehouse .These are most important concepts of data mining.

Definition:

The Data Warehouse is a large repository of data collected from different organizations or departments within a corporation.

Definition:

The data mart is an only subtype of a Data Warehouse. It is designed to meet the need of a certain user group.

Focus:

Data warehouse focuses on multiple business areas.

Focus:

Data mart focuses only on single subject area.

Usage:

It helps to take a strategic decision.

Usage:

The data mart is used to take tactical decisions for growth of business.

Type of system :

This is centralized system where one fact is at center surrounded by dimension tables.

Type of system :

Data mart system is de centralized system

Scope of Business:

The Data warehouse will cover all business areas.

Scope of Business:

The scope of Data mart is within the line of the Business.

Data Model:

Data warehouse always follows top-down model

Data Model:

Data mart always follows bottom-up model.

Data Size:

Data warehouse contains all historical data so the database size is large.Approximate size of data warehouse is greater than 100 GB.

Data Size:

Data mart contains data with only one business area so the size of database is smaller than data warehouse.

Source:

Data warehouse data comes from multiple heterogeneous data sources.

Source:

Data mart data is data of only one business area.Many times it will come from only one data source.

Implementation Time:

Data warehouse contains all data which will come from multiple data sources. It will take time to build data warehouse. The Time to build data warehouse is months to years.

Implementation Time:

Data mart is small data warehouse which will contain the data of only a single business area. The implementation time to build data mart is in months.

Hope you like this Data Mart vs Data warehouse article. If you have any suggestions about Data Mart vs Data warehouse article kindly comment in comment section.

Views in SQL is nothing but the logical table created from one or more tables.We can use the views to fetch the columns from one or more different tables at a time.In real life specifically views are used in Reporting purpose.To create a report we need data from different tables and need to show it on a single dashboard so we are using the views to fetch the data from different tables.View can contain all rows from the table or selected rows from the table.

CLICK HERE TO GET INFORMATION ON MATERIALIZED VIEW.

View is nothing but virtual table in sql which will fetch data from one or more physical tables

There are 2 types of views in SQL:

1.Simple View-Simple view is view created on single table

2.Complex View-Complex view is view created on more than 1 tables

• Simple View:

When user wants data or some columns from same table then simple view is used.For an example if there is employee table which has Employee_num,Employee_name,salary columnns and we just need to see Employee_num,Employee_name then user can create a simple view.

Employee_num Employee_name Department Salary 1 Amit OBIEE 680000 2 Rohan OBIEE 550000 3 Rohit OBIEE 430000

We want to restrict data from the above table and show only Employee_Num and Employee_name then we will use the view.

Syntax: Create view Viewname as Select column_name1,Coumn_name2 from tablename. Example: Create view V_Employee as Select Employee_num,Employee_name from Employee;

You can access the view using Select statement of SQL:

select * from V_Employee;

Output:

Employee_num Employee_name 1 Amit 2 Rohan 3 Rohit

• Complex View:

Complex view is created on using more than one tables.When user wants to retrieve data from more than 1 table then we have to use complex views.To create complex view there should be relation between 2 tables else cartesian product will come by joining 2 tables.There should be some joining conditions,some filters needs to be considered while creating complex views.

Suppose there are 2 tables:

1.Employee tables:-Employee_name,Employee_num,Department_code columns

2.Department:-Department_code,Deptartment_name columns

We need to create view where we want to show the associated department to employee.Here We need to use complex join.

Create view V_EMP_DEPT as Select e.Employee_name,d.Department_name from Employee e,Department d where e.Department_code=d.Department_code;

If you have any issues regarding SQL Views or If you want notes in PDF format comment below section.

SQL Performance Tuning Techniques is the process of ensuring that the SQL statements will run in the fastest possible time.

• Tune your SQL here:

Following are some basic tips using which you will able to optimize your query and improve SQL Performance Tuning Techniques .The first step to check is cost of your query.For checking the cost of query use following simple command.I will explain the

'Explain' statement in detail in my other advanced tuning articles.As of now just refer following statement to find out the cost of query.More the cost less the performance of query.

Explain [Query] Explain Select * from Employee;

How to improve SQL Query Performance? - Tips and Examples

Check 1 : Need to Avoid Multiple number of Joins

1. There is common practice where we are using the unnecessary multiple joins.

2.If you see the explain plan the joins cost is very high.

3.We require to check all the joins and remove the unnecessary joins by restructuring query.

Check 2 : Cursors Illumination

1.We require to revisit the outputs of multiple queries and need to remove the unnecessary cursors.

2.Dynamic cursors limits the optimizer to use nested loop joins.

3.We require to avoid cursors and use set-based queries instead of cursors.

Check 3 : Avoid non-correlated sub queries

1. There are so many times due to bad SQL techniques the people are using the non correlated sub-queries.

2.We require to use correlated sub queries.

Check 4 : Avoid the costly functions and statements

1.We require to check the system functions and its cost .

2.We require to avoid distinct or count like functions wherever possible. These functions are costly and will reduce the cost.

Check 5 : Need to create correct index on correct places

1.We require to create the indexes on correct places.

2.We require to reduce the data retrieval time for queries.

3.We require to create highly selective indexes by checking and analyzing data.

Check 6 : Check the data volume

1.We require to check the data volume of statement.

2.We need to review the data volume and apply the partitioning techniques accordingly.

check 7 : Drop unused indexes

1. We require to check the low performing indexes and unused indexes.

2.We need to recreate the good indexes and remove the unused indexes.

check 8 : Create Stats with update stats :

1.There are so many times when we forget to gather stats of tables,indexes or schemas.

2.We need to gather stats and set some jobs to gather stats on regular basis.

Check 9 : Need to check the constraints

1.There are so many times when the performance will decrease because of the constraints

2.We need too revisit the design and check the primary key,foreign key and not null and check constraints.

Some tips to improve the SQL Performance :

Tip 1: Never use *(Star) to fetch all records from table

Sql query become fast if you use actual columns instead of * to fetch all the records from the table.

Not Recommended: Select * from Employee; Recommended: Select Eno,Ename,Address from Employee;

• Tip 2: Try to avoid distinct keyword from the query

Try to avoid distinct keyword from select statements.Distinct keyword has high cost and low performance.When anyone uses distinct keyword it first sorts the data from column and then fetches the distinct values.Use exist operator inspite of distinct keyword.

Not Recommendedd: SELECT DISTINCT d.dept_no, d.department_name
FROM Department d,Employee e
WHERE d.dept_no= e.dept_no; Recommended: SELECT d.dept_no d.department_name
FROM Department d
WHERE EXISTS ( SELECT 'X' FROM Employee e WHERE d.dept_no= e.dept_no);

INTERVIEW QUESTIONS ON PERFORMANCE TUNING

• Tip 3:Carefully use where conditions in sql

Try to use correct operator as per requirement given.

Not Recommended: Select * from Employee where salary != 65000; Recommended: Select * from Employee where salary > 65000 and salary < 65000;

• Tip 4: Use Like operator in spite of equal to (=)

Not Recommended: Select * from Employee where name='Amit'; Recommended: Select * from Employee where name like 'Amit%';

• Tip 5: Avoid having clause/group by statements

Having clause and group by statements have high cost.So try to avoid it in sql query.

Not Recommended: Select * from Employee where name='Amit' group by department having salary=45000; Recommended: Select * from Employee where name='Amit' and salary=45000;

Tip 6: Use of Exist and In Operators

Basically Operator IN has lowest performance.IN operator is used when Filter criteria is in subquery whereas Exist operator is used when filter criteria is in main query.

Example: In Operator: Select * from Employee where Department_name In(Select Department_name from Department where Dno=10); Exist operator: Select * from Employee where Exist(Select Department_name from Department where Dno=10);

Try to use union all inspite of union.As union scans all data first and then eliminate duplicate so it has slow performance.

Not Recommended: Select * from Employee where dept_no=10 Union Select * from Employee where dept_no=20; Recommended: Select * from Employee where dept_no=10 Union All Select * from Employee where dept_no=20;

• Tip 8: Avoid use of Functions in Where condition.

Not Recommended: Select * from Employee where Substr(name,1,3)='Ami'; Recommended: Select * from Employee where name like 'Ami%';

About SQL OPTIMIZER AND PARSER

I have explained the Basic tips which is used to improve and optimize performance of SQL Queries.Hope this article is helpful to you to improve the performance of your sql queries.