Data Warehouse - Copmarision Matrix

DW Product	IBM - DB2 Data Warehouse	Oracle - Exadata	Teradata
Company	IBM has four major businesses: hardware, software, services, and financing. Data warehousing is part of the data management business, which is part of the software business.	Oracle has three businesses: database, applications, and consulting. Its database business is the largest by far and currently represents 80 percent of Oracle’s new license revenues.	NCR has four businesses: data warehousing, financial self-service, retail store automation, and customer services. The Teradata Division is responsible for the data warehousing business
Product URL	www.ibm.com/DataWarehousing‎	https://www.oracle.com/database/data-warehouse/index.html	www.teradata.com/
STRENGTHS	- Rich and flexible data partitioning capabilities -strong analytic functionality in OLAP and data mining - Market presence - Data Models - Hardware bundle - Partner network - Strong services arm (IBM GSA)	- Intelligent Storage Grid - Hybrid Columnar Compression - Smart Flash Cache	- Massively-parallel, partitioned, - Shared-nothing database server architecture - its simple and highlyautomated physical data warehouse implementation -set of indexing approaches that enable fast access to data - scalable hybrid-storage capabilities - Teradata has buddied up with all enterprise Hadoop distro providers, enabled new analytic workloads to be added to Teradata systems (JSON, geospatial, 3D geospatial and others) and more.
WEAKNESSES	- complex physical implementation - lack of integration with multidimensional - High cost of ownership - DB2 on the open systems platforms continues to suffer from locking problems. • Closed systems; can’t easily ride cost curves associated with commodity hardware. • Expensive fault tolerant solution compared with Exadata		- its incomplete visual tools for build and manage functionality - Proprietary hardware - Costly to maintain and upgrade - Limited skilled implementation expertise
Deployment Platforms	IBM AIX Microsoft Windows Linux Sun Solaris	IBM AIX Hewlett-Packard HP-UX Linux Microsoft Windows Sun Solaris	NCR SVR4 UNIX MP-RAS Microsoft Windows
Server Architecture	Server platform with a single processor Single database partition on a server platform with a multiple processors Multiple partition configurations • Shared-nothing • Multiple server platforms • Server interconnect • Any number of readers and writers	Single server platform Distributed database Real Application Cluster (RAC) • Shared, partitioned data • Multiple server platforms • Server interconnect • Any number of readers and writers	Single and multiple node organization where a node is a hardware and software platform specialized and dedicated to data warehousing Teradata Warehouse is a sharednothing architecture in both its single and multiple node configurations
Data Type Support	SQL types: • Numeric • Binary • Character • Date time DATALINK XML Large objects (max 2 gigabytes) User-defined types (distinct— renamed SQL types, structured— object oriented, reference— hierarchies of built-in types)	Oracle built-in data types (SQL types): • Numeric • Binary • Character • Date time Large objects (max 2 gigabytes) User-defined types (object-oriented types, object identifier types, arrays, nested tables) Oracle-supplied types • Spatial • Media • Text • XML	SQL types: • Numeric • Binary • Character • Date time Large objects
Physical Design Recommendation	Neutral on the physical design of data warehouses.	Neutral on the physical design of data warehouses.	Teradata is neutral on the physical design of data warehouses but recommends a physical design of third normal form for data warehouses to maximize flexibility. Teradata further recommends that denormalized structures be implemented as views or redundant structures (logical data marts or special purpose tables).
Physical Implementation	Manual	Manual Template-based via templates and Database Configuration Assistant (DBCA) tool Automated via Oracle managed files	Automated
Custom Transformations	May be written in: SQL Java C++	May be written in: SQL PL/SQL	May be written in: SQL C++
Summary Table Support	Materialized query tables automate the creation and management of summary tables. A materialized query table stores the results of a query in a table	Materialized views automate the creation and management of summary tables. A materialized view stores the results of a query in a table	The OLAP transformations of Teradata Warehouse Miner can create and manage summary tables.
SQL Extensions	CUBE and ROLLUP in SELECT Functions • Aggregate • Numeric • Statistical • Correlation • Random number generation • Regression • Date time User-defined	CUBE and ROLLUP in SELECT Functions • Ranking • Window aggregate • Reporting aggregate • Lag/lead • Linear regression • Inverse percentile • Hypothetical rank and distribution • First/last • Numeric • Date time User-defined	QUALIFY, SAMPLE, and WITH in SELECT Functions and operators • Aggregate • Numeric • Date time • OLAP
OLAP	DB2 provides OLAP build and manage capabilities, relational OLAP on DB2 tables, and multidimensional and hybrid OLAP on a combination of DB2 tables and external multidimensional structures. DB2 OLAP Server is a separately- priced and -packaged product that is an external, but tightly integrated, multidimensional OLAP facility that IBM OEMs from Hyperion Solutions.	Oracle OLAP is a separatelypackaged and -priced product that provides OLAP functionality	Provides relational OLAP on Teradata Warehouse tables
Data Mining	DB2 Intelligent Miner is bundled with DB2 Data Warehouse Enterprise Edition.	Oracle Data Mining is a separately- priced and -packaged product.	Teradata Warehouse Miner is a separately-packaged and -priced product that is tightly integrated with Teradata Warehouse.

Oracle Data Warehouse - Exadata

-->

Company - DW Product	Oracle - Exadata
Company	Oracle has three businesses: database, applications, and consulting. Its database business is the largest by far and currently represents 80 percent of Oracle’s new license revenues.
Product URL	https://www.oracle.com/database/data-warehouse/index.html
STRENGTHS	- Intelligent Storage Grid - Hybrid Columnar Compression - Smart Flash Cache
WEAKNESSES
Deployment Platforms	IBM AIX Hewlett-Packard HP-UX Linux Microsoft Windows Sun Solaris
Server Architecture	Single server platform Distributed database Real Application Cluster (RAC) • Shared, partitioned data • Multiple server platforms • Server interconnect • Any number of readers and writers
Data Type Support	Oracle built-in data types (SQL types): • Numeric • Binary • Character • Date time Large objects (max 2 gigabytes) User-defined types (object-oriented types, object identifier types, arrays, nested tables) Oracle-supplied types • Spatial • Media • Text • XML
Physical Design Recommendation	Neutral on the physical design of data warehouses.
Physical Implementation	Manual Template-based via templates and Database Configuration Assistant (DBCA) tool Automated via Oracle managed files
Custom Transformations	May be written in: SQL PL/SQL
Summary Table Support	Materialized views automate the creation and management of summary tables. A materialized view stores the results of a query in a table
SQL Extensions	CUBE and ROLLUP in SELECT Functions • Ranking • Window aggregate • Reporting aggregate • Lag/lead • Linear regression • Inverse percentile • Hypothetical rank and distribution • First/last • Numeric • Date time User-defined
OLAP	Oracle OLAP is a separatelypackaged and -priced product that provides OLAP functionality
Data Mining	Oracle Data Mining is a separately- priced and -packaged product.

Oracle vs SQL Server : High Availability, Licensing, Performance and security

High Availability
SQL Server 2008:
Database-mirroring :
Use a rolling upgrade process to upgrade database instances in a database-mirroring session.
Take advantage of write-ahead functionality on the incoming log stream on the mirror server.
Use page read-ahead capability during the undo phase to further improve performance.
Provide reporting capabilities with a database snapshot as a source for reports on the mirror server.

Failover clustering:
Enable failover support by sharing access among nodes and restarting SQL Server on a working node.
Increase scalability with support of up to 16 nodes in a single failover cluster.
Support a rolling upgrade process for servers participating in a failover-clustering configuration.

Peer-to-peer replication:
Replicate changes in near real time, while all databases also handle their primary responsibilities.
Boost scalability, availability, and processing capacity by configuring applications to use peers and to fail over to another peer.
Protect against accidental conflicts with built-in conflict detection.
Increase availability by dynamically adding a new node to an existing topology.

Log shipping:
Provide database redundancy by using standby servers to automatically back up transaction logs.
Increase availability by providing multiple failover sites.
Reduce the load on the primary server by using a secondary server for read-only query processing.

Oracle 11g:

Oracle Provides following features for high availability.

Real Application Clusters
Clusterware
Data Guard
GoldenGate
Streams
Secure Backup
Recovery Manager (RMAN)
Flashback Technologies
VM
Cloud Computing
Cloud Storage
Cross-Platform Transportable Tablespace
Edition-Based Redefinition
Online Reorganization

License cost

Oracle 11g license cost

- Per Processor = $17,500
- Support (22%) = $3,850
- Total (Per Processor) = $21,350
- Total (4 Processors) = $85,400


license cost of SQL Server

- Per Processor = $5,999
- Total (4 Processors) = $23,996

Security:

SQL Server features role-based security for server, database and application profiles; integrated tools for security auditing, tracking 18 different security events and additional sub-events; plus support for sophisticated file and network encryption, including SSL, Kerberos and delegation.

Oracles provides powerful security features such as database activity monitoring and blocking, privileged user and multi-factor access control, data classification, transparent data encryption, consolidated auditing and reporting, secure configuration management, and data masking, customers can deploy reliable data security solutions that do not require any changes to existing applications, saving time and money.

ORACLE DATABASE SECURITY PRODUCTS:
Oracle Advanced Security
Oracle Audit Vault
Oracle Label Security
Oracle Configuration Management
Oracle Secure Backup
Oracle Database Firewall
Oracle Database Vault
Oracle Data Masking
Oracle Total Recall

Performance:

In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.


Following Oracle features do not exist in SQL Server.
There are no bitmap indexes
There are no reverse key indexes in SQL Server.
There are no function-based indexes in SQL Server.

JOIN in Oracle 10g

JOIN in Oracle 10g:

JOIN: Oracle 10g As your database grows so will the need to get information from more than one table.

A join is nothing more than writing a query that takes a set of rows from one or more tables and combines them together. The tables that will participate in the join are listed in the FROM clause of the SQL query and then, depending on the syntax we choose, rows from each table are linked by either the WHERE clause or the JOIN clause.

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Equijoins or Inner Join:

Table_A and Table_B each have one column named LETTER.

Table_A.letter Table_B.letter

A A

B

C

An equijoin is a join with a join condition containing an equality operator ( = ).

An equijoin combines rows that have equivalent values for the specified columns.

The following SQL joins Table_A against Table_B where they share the same letter value.

SQL >SELECT Table_A.letter, Table_B.letter 2

FROM Table_A, Table_B 3

WHERE Table_A.letter = Table_B.letter;

LETTER LETTER

---------- ----------

A A

Self Joins:

A self join is a special form of equijoin or INNER JOIN where a table is joined against itself. This means that the table must exists two times in the FROM clause of the SQL query. A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

Left Outer Joins: Often times we need to return rows from one table even if there are no matching rows that are produced through a join condition. For this situation, we use outer joins. A left outer join is where the table, on the left of a FROM clause is required to return all of its rows regardless of having matching rows from the table it is being joined on. So in the following SQL we want to join Table_A to Table_B and show where they are equal on the column LETTER as well as return rows from Table_A that do not have a match on Table_B. The old method in Oracle was to supply the plus sign in parentheses (+) next to all columns in the WHERE clause that may not have values that will match against the table being joined to. Here is the old method in Oracle to produce a left outer join.

SQL >SELECT Table_A.letter, Table_B.letter 2

FROM Table_A, Table_B 3

WHERE Table_A.letter = Table_B.letter(+);

LETTER LETTER

---------- ----------

A A

B

Right Outer Joins:

A right outer join is just the opposite of a left outer join. It states that you would like all rows from the right table in the FROM clause to be returned regardless of having a true match defined in the WHERE clause against the left side table in the FROM clause. Here is the old method in Oracle for producing the right outer join.

SQL >SELECT Table_A.letter, Table_B.letter 2

FROM Table_A, Table_B 3

WHERE Table_A.letter(+) = Table_B.letter;

LETTER LETTER

---------- ----------

A A

C

Full Outer Joins:

The full outer join is a special condition. With Oracle's old method of supplying (+) notation, there is no method to explicitly state in a SQL statement that you want to return both left and right sides of a query regardless of having a match. To accomplish this you must write a left outer join SQL statement and UNION it with a right outer join SQL statement. Doing that produces the following SQL and results.

SQL >SELECT Table_A.letter, Table_B.letter

FROM Table_A, Table_B

WHERE Table_A.letter = Table_B.letter(+)

UNION

SELECT Table_A.letter, Table_B.letter

FROM Table_A, Table_B

WHERE Table_A.letter(+) = Table_B.letter;

LETTER LETTER

---------- ----------

A A

B C

Cartesian Products:

A cartesian join is a query where there is actually no join criteria between the table in the query. What is returned is a cartesian product where each row from a table is matched against every row in the other table. So in our example where Table_A has two rows and Table_B has two rows there will be 2 X 2 or 4 rows returned. The old method was to exclude the WHERE clause. SQL >

SELECT Table_A.letter, Table_B.letter 2

FROM Table_A, Table_B;

LETTER LETTER

---------- ----------

A A

A C

B A

B C

Check locked object

1. Check locked object:
selectc.owner, c.object_name,c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a ,
v$session b,
dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

2. Then use Sid from above queries to see what its doing.
select s.sid, s.username, s.schemaname, s.osuser, s.machine, s.terminal, s.program, sa.sql_text from v$sqlarea sa, v$session s
where s.sql_hash_value = sa.hash_value
and s.sql_address = sa.address
and s.sid=&sid
select sid,username,status,blocking_session,wait_class,seconds_in_wait,state
from v$sessionwhere sid=&sid