Oltp vs Datawarehouse DSS

OLTP(online transaction processing) database Is used for online shopping, billing, customer applications. OLTP has heavy transaction volume like millions of customers shopping on Amazon.  But the transactions are smaller transactions, runs for few milli seconds. It is Highly Normalized. It has heavy Inserts so number of Indexes should be minimized. OLTP system keeps only last 3-6 months of data in the database. Older data is mo ved into Datawarehouse which retains data for 10-20 years depending on Business requirement. Must be available 24/7/365 and has short maintenace intervals. Datawarehouse/DSS(Decision Datawarehouse/DSS(Deci sion Support System) Database Are huge databases and used for Reporting purpose. It is Highly Denormalized to optimize the sql queries. Has many Indexes to optim ize the sql performance. A datawarehouse query runs against huge data(10-20 years data). e.g Finding out what City/State has most/least Sale of a Product in last 5 years. Based on these  analysis Marketing/Sales can focus on a specific City/State. Datawarehouse Transactions are bigger but transaction volume is lesser. Online Transaction Processing and Decision-Support System Databases Online transaction processing databases are the bread and butter of most consume r- and supplier oriented databases. This category includes order entry, billing, customer, supplier, and supply-chain databases. These These databases are characterized characterized by and a need to be online continuously, which today (given the use of the Internet to access such systems)  means 24/7/365 availability, short maintenance intervals, and low tolerance for breakdowns in t he system. Decision-support systems range from small databases to large data warehouses. Th ese are typically not 24/7 operations, and they can easily manage with regularly schedul ed downtime and maintenance windows. The extremely large size of some of these data warehouses n ecessitates the use of special techniques both to load and to use the data. There isnt a whole lot of difference between the administration of a DSS-oriented  data warehouse and a transaction-oriented OLTP system from the DBA s perspective. The backup and recovery methodology is essentially the same, and database security and other related iss ues are also very similar. The big difference between the two types of databases occurs at the design and i mplementation stages. DSS systems usually involve a different optimization strategy for queries and di fferent

physical storage strategies. Oracle Database 11g provides you with the choice of  implementing an OLTP database or a DSS database using the same database server software. Performance design considerations that may work well with one type of database m ay be entirely inappropriate for another type of database. For example, a large number  of indexes can help you query a typical data warehouse efficiently while you are getting some report s out of that database. If you have the same number of indexes on a live OLTP system with a large number  of concurrent users, you may see a substantial slowing down of the database, because the many updates, inserts, and deletes on the OLTP system require more work on the part of the database.