rio 01 teradata vs other databases v1.0
Post on 22-Oct-2015
38 Views
Preview:
DESCRIPTION
TRANSCRIPT
Teradata Vs Other Databases
LEVEL – PRACTITIONER
About the Author
Created By:
Credential Information:
Version and Date:
Cognizant Certified Official Curriculum
Sudeshna Pal(200271) Sarnava Das(247258)
Teradata Certified Professional V12. Worked in Teradata Technologies for last 2.5 years.
V 1.0 Date – 12/17/2012
2
Icons Used
Questions
Demonstration
Hands on Exercise
Coding Standards
Best Practices & Industry Standards
Tools
3
Case StudyTest Your Understanding
Workshop
4
Teradata Vs Other Databases: Overview
•Introduction:– This chapter gives a comparison of Teradata database with Oracle
10G and DB2 database.
5
Teradata Vs Other Databases: Objectives
• Objective:After completing this chapter you will be able to:
– Compare Teradata with Oracle 10G and DB2 database
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
DW Awareness
Data Base product to be intelligent and understand the DW environment like to have specific options (or the vendor have a separate DW product) while creating a Database as whether it is for a DW or OLTP system. (Whether the database is going to have frequent updates to data or queries, size of the pages, buffer). Awareness of star schema or not (whether the table is Fact or Dimension).
Yes. Architecture is designed for DW. Not recommended for OLTP.
Has the facility to create tables of Dimension Type. Code optimization feature and grid architecture improving the performance like full table scan are faster by 30%. SQL Model clause introduced. It allows users to embed 'spreadsheet-like' models in a SELECT statement.
It is an integrated platform for developing warehouse-based analytics, including Web-based applications with embedded data mining and multi-dimensional OLAP. It integrates core components for warehouse construction and administration, data mining, OLAP, and inline analytics/reporting.
Comparison with Oracle 10G and DB2
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
DW Query Improvement Techniques
Indexing techniques supported (Bit, Clustering, Star Join etc). Data Partitioning (Table (Column, Row), Index, anything unique...). Support for OLAP query functions like RANK etc.
Teradata supports Primary index, secondary index, hash index, join index and partitioned primary index. It also has functions for complex analytical querying and data mining like RANK,QUANTILE,CSUM,MAVG,MSUM,MDIFF,MLINREG,ROLLUP,CUBE,GROUPING and GROUPING SETS
Oracle 10g supports B-tree index, B-tree cluster index, hash cluster index, Reverse key index, bitmap index and bitmap join index. The partitioning methods available here are : Range, List, Hash and composite. OLAP functions like ranking, lag, lead are available.
DB2 supports dynamic bitmap indexes, encoded vector indexes, join support and parallel index build. It supports table, column and index partitions.OLAP functions like rank and over are available here.
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
OLAP Functions
Wide number of OLAP Functions Supported (RANK, Ordered Rank, Relative Rank, Quantile etc).
Rank, Dense Rank,Cume Dist, Percent Rank, Top N Ranking, Bottom N Rankint, Windowing Aggregate function: Cumulative Aggregate, moving aggregate, Lag/Lead functions, First/Last functions, Linear Regression functions, Width bucket functions etc.
Typical OLAP functions like RANK, DENSE_RANK, ROW_NUMBER, RANGE BETWEEN, PARTITION BY, ORDER BY, ASC, BETWEEN are available
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Indexing Techniques
Data Distribution and Data accessing is based on Primary Index. Also can go for NUSI & USI to improve access performance.
Bitmap index, Bitmap join index,B-tree (Bitmap indexes are primarily intended for data warehousing applications where users query the data rather than update it. ),Clustered Index, Hashed Index.
B+Tree, Clustered indexes, Multi-dimensional clustering
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Data Partitioning Data is partitioned on primary index values and can have partitioned primary index for accessing range values.
Range Partitioning ,Hash Partitioning, List Partitioning, Composite partitioning are supported.
DB2 has flexibility in spreading data across multiple database partitions of a partitioned database. a distribution map specifies the mapping of distribution key values to database partitions.
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
DW Monitoring tools
Query Monitor:Statistics/Visual representation of an executed Query. Query Governor (to queue the queries based on their importance, so that important or less resource consuming queries are executed first). We should have a feature (interface) by which we can assign weights (priority) to the queries/ users/ applications.
Teradata Manager : Used for monitoring query, sessions, users, system performance etc. Priority assignment at user level. PMON: used for monitoring queries at session level and go up to individual step level
Oracle Enterprise Manager can be used to monitor the DW performance.
The Event monitors provides detailed data for pinpointing/diagnosing problems.The Health monitor provides high-level picture of database health.The snapshot monitor is available to capture a picture of the state of database activity at a particular point in time .
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Loading Tools FastLoad (for loading empty tables) Mload (Regular/Incremental Loading, can load to any table) Tera builder: New ETL Tool (From V2R4). It also supports data export facility
SQL*Loader - To load bulk data from a datafile
The import and export utilities move data between a table or view and another database or spreadsheet program.The load utility moves data into tables, extends existing indexes, and generates statistics. Load moves data much faster than the import utility when large amounts of data are involved
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Real time Loading tools
Unloading tools
Tpump: For real time loading but slow compared to other utilities
FastExport (exports from Db to Flat files)
SQL*Loader - To load bulk data from a datafile
Data Pump, Export
Data Propagator is a component of the DB2 database system that allows automatic copying of table updates to other tables in other DB2 relational databases.
Export
Comparison with Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Parallel Processing Architecture Supported
SMP-Symmetric Multi Processing
MPP-Massive Parallel Processing Architecture
NUMA-Non-Uniform Memory Access Architecture
Yes
Yes
No
Yes Yes
Yes
Yes
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
DSS Tool Integrated
TPC score TPC –H
TPC -R
1. Tera Miner: Data Mining tool2. Teradata Warehouse Builder Infrastructure provides complete ETL solutions in parallel environment
http://www.tpc.org/tpch/results/tpch_perf_results.asp
http://www.tpc.org/tpcr/results/tpcr_results.asp
ETL-Oracle Warehouse Builder (OWB), Oracle9i OLAP, Oracle Data Mining (ODM).
DB2 UDB Data Warehouse Enterprise Edition,DB2 Cube Views and DB2 Office Connect
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Gartner Score
Administration
http://mediaproducts.gartner.com/reprints/ncr/vol3/article2/article2.html
Name of Administration Tool
Administrative WorkStation(AWS)
Same as 9i + Less time in tuning and monitoring the database with features liked automatic workload repository a improved version of statspack
Control Center
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Backup, On-line "hot" Backup, Parallel
Distributed Database Admin.
Recovery from System Failure
Recovery, On-line (Concurrent)
Recovery, Parallel
Recovery, Point-in-Time
Teradata BAR Solutions
N/A
Automatic
Automatic
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Administration Alerts self-managing framework is 'Server Generated Alerts', a method where the database server sends notifications via email to the DBA - including a recommendation as to how best to deal with the situation
Yes.
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Architecture Client Multi-Server
Multithreaded Server
Multi-User Database
Raw Devices, Support for Unix
Sharable Cached Data
Shared Dynamic SQL
Shared Static SQL
Shared-Nothing Architecture
Supported
Supported
Supported
Yes
Yes
Yes
Yes
Yes
Automatic Memory ManagementAutomatic Memory ManagementAutomatic Memory ManagementNew feature ASM ( Automatic storage management)
Yes.
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Locking Strategy
What is the locking strategy used by your DBMS?
Supports locking up to row level, table level and database level. Read, Write , Access and Exclusive are the four type of locks its supports
The following Locks are supported.Exclusive Table Locks (X)Share Row Exclusive Table Locks (SRX)Share Table Locks (S)Row Exclusive Table Locks (RX)Row Share Table Locks (RS)
Table and row locks are supported. Row locks include Lost updates, Access to uncommitted data, Non-repeatable reads and Phantom reads. Isolation levels are Uncommitted Read, Cursor Stability, Read Stability and Repeatable Read
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Replication across database
Exception Handling/64 bit
What is the replication support across databases?
How are exceptions handled by your database? Any support for 64 bit Windows OS?
Not Applicable
Supports Windows OS.
Following are the Replication SupportedReplication ObjectsReplication GroupsReplication Sites
Yes
Yes
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Messaging/Queuing
How is message queue management supported by your DBMS? What distribution transport mechanisms are supported and is there some characteristics that is unique to your product. What other tools in conjunction with your product will make a USP in data distribution mechanisms.
Supported through External Stored Procedures from V2R6 onwards
Oracle Uses Advanced Queuing Concept, which leverages the functions of the Oracle database. The mechanism used to achieve these are Oracle Net Services, HTTPS and SMTP. Oracle uses change data capture (CDC) and uses Asynchronous concept. except CDC uses synchronous data transfer.
Yes
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
SOA support
XML support
.NET support on Database
Mention support for HTTP SOAP etc. natively in the DBMS?
Mention support for XML, Xquery available in the DBMS?
Is there a native .NET data provider with the DBMS? How is .NET integrated with the DBMS?
Teradata Application Platform (TAP) supports open industry standards like XML, UDDI and SOAP through JDBC
Supported through ODBC
Supported through ODBC using .NET Data Provider for Teradata
Yes, Supported
Yes, Supported( No Xquery Support.)
Yes, Supported
Yes, Supported
Yes, Supported
Comparison With Oracle 10G and DB2 (Contd.)
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Support for Identity Column/User Defined Data Types/Rules/Functions
Is there mechanism to generate primary key automatically? Are there support for User Defined Data Types/Functions? Is there support for rules in database?
Supports Identity column, UDT and functions (UDF)
Yes, Automatic generation of PK supported.Yes, User Defined types & Functions Supported.Yes, Rules (Triggers) are Supported.
Yes, Supported
Comparison With Oracle 10G and DB2 contd..
Criteria Description Teradata v13 Oracle 10G DB2 Data Warehouse Edition (based on UDB 9)
Notification Services
Reporting Services
How is notifications to external devices handled from DBMS?
Is reporting services integrated with DBMS?
Not supported
NO. Only simple reporting functions supported thru BTEQ utility
Notification to external devices are supported using Advanced Queuing.
Same as 9i
Yes, Supported
Yes, Supported
26
Questions
27
Welcome Break
28
State True or False:
1. Teradata is ideal for Data warehousing applications.
2. SQL*Loader can be used to load bulk data to a table from a data
file in DB2.
Test Your Understanding
29
• Summary:– In this module, you have compared the following features
handled in Teradata V13, Oracle 10g and DB2 Datawarehouse edition• DW Awareness • DW Query Improvement Techniques • OLAP Functions • Indexing Techniques • Data Partitioning • DW Monitoring tools • Architecture • XML support• Notification Services
Summary
30
• www.teradataforum.com• www.teradata.com
Source
Disclaimer: Parts of the content of this course is based on the materials available from the Web sites and books listed above. The materials that can be accessed from linked sites are not maintained by Cognizant Academy and we are not responsible for the contents thereof. All trademarks, service marks, and trade names in this course are the marks of the respective owner(s).
Teradata Vs Other Databases
You have successfully completed - Teradata Vs Other Databases
top related