DWH: OLTP Vs OLAP

DWH: OLTP Vs OLAP

OLTP(Online Transactional Processing)              OLAP(Online Analytical Processing)

1.It deals with Transactional / Operational Data       1.It deals with Historical Data

2.Databases are normalised to facilitate frequent      2.Databases are de-normalised to facilitate              insertion,updation and deletion                                 queries and analysis

3.Old data is purged or archived                              3.Old Data or Historical data is stored to do                                                                                        trend  analysis and future predictions.

4.Queries are small and it deals with small               4.Queries are complex and it deals with                 amount of data                                                          huge amount of data
   Eg: Updation of Account Balance                           Eg: Total sales in North India

5.Updates happens frequently                                 5.Updates does not happen frequently.

6.Response time is very fast.                                  6.Response time is slow

7.Number of Joins are more as Table is                   7. Mostly less number of joins as Table is in           Normailesd.                                                              de-normalised.

   

SQL - DELETE and TRUNCATE

SQL- DELETE and TRUNCATE

Major difference between DELETE and TRUNCATE are as follows:

          DELETE                                                            TRUNCATE
1. DELETE is  DML Command                   1. TRUNCATE is DDL Command
2. WHERE clause can be used                  2. WHERE clause cannot be used
    along with DELETE.                                   along with TRUNCATE
3. It removes data from the table                 3. It removes all data from the table
   based on WHERE clause.                        
4. It puts lock on a row .                             4. It puts locks on a table
5. It is slower as it keeps log.                      5. It is faster as it does not keep log.
6. Space is not released.                            6. Space is released.
7.Syntax:                                                  7.Syntax:
   DELETE FROM table_name                     TRUNCATE TABLE table_name
   [WHERE condition]