Difference between Oracle and Teradata

Oracle and Teradata are both Relational Database Management Systems (RDBMS), however Oracle implements an Object-Relational Database Management System (ORDBMS).[i]

The RDBMS was introduced using the Relational Model[ii] that maintains relationships between tables using indexes, primary and foreign keys.  It is a quicker to fetch and store data compared to the old Database Management System (DBMS).

Although similar to a relational database, Oracle’s object-oriented database model uses objects, and classes supported in the database schema and query language.

In the late seventies, while Oracle became the first commercially available RDBMS, Teradata was laying the foundation for the first data warehouse.  Its’ capabilities made it perfect for big data (a term coined by Teradata), Business Intelligence (BI) tools[iii], and the Internet of Things (IoT)[iv].

OLTP and OLAP

Relational Database Management Systems (RDBMS) can be categorized as Online Transaction Processing – OLTP and Online Analytical Processing – OLAP.[v]

OLTP is transactional and provides data to data warehouses and OLAP is designed to analyze the data.

“A data warehouse is a database containing data that usually represents the business history of an organization. Data in a data warehouse is organized to support analysis rather than to process real-time transactions as in online transaction processing systems (OLTP).

OLAP technology enables data warehouses to be used effectively for online analysis, providing rapid responses to iterative complex analytical queries.”[vi]

Although Teradata is geared towards OLAP and Oracle for OLTP, Oracle can run both OLTP and OLAP databases on the same platform, which Teradata does not support.  The performance gives Oracle an advantage over Teradata in most mixed workload scenarios.

Transactions comprise one or more SQL statements executed on a database to complete tasks, while maintaining data integrity.  Transactions are interdependent to manage and process data being added or deleted from a database.

Oracle is tailor-made for transaction processing because of its architectural flexibility (with an object RDBMS), whereas Teradata’s OLAP is powerful for slicing and dicing data (OLTP’s data history) for analysis, without having to move or restructure data.

Oracle is primarily used as an online back-end application managing inserts, updates, and deletes while transacting, whereas Teradata is Data Warehousing that maintains big data for analytics and there are no real-time transactions.

Teradata is a good combo between hardware and software, producing a top end enterprise database appliance, however, Oracle launched its OLAP Exadata Server in 2008.  It was Oracle’s answer to a full database appliance.

Both require a significant investment and are more suited to very big databases that demand great performance with complex queries.

Share Nothing or Everything?

Teradata is Shared Nothing (SN) Architecture whereas Oracle is Shared Everything.

The term shared nothing architecture refers to a multiprocessor database management system where memory and disk storage are not shared between the processors.[vii]  Network bandwidth is shared to transfer data.
With Teradata, the data is stored on servers and each computes its own part.  The data is partitioned and stored across a set of servers and every server is responsible for its data.  Examples of high-end platforms using the same architecture include Google, Amazon, and Facebook.

However with Oracle’s shared everything architecture, all data is accessible across all servers and access levels can be applied to any part of the data as needed.  The potential downside is the increase in the data access management overhead and could make the system management more complex to administer.

Oracle’s architecture effectively means any machine can access any data, and this is the fundamental difference between Teradata, making Teradata ideally suited for data warehousing, and Oracle more suited to OTLP.

Note that although Oracle Exadata deployed a hybrid approach of the two architectures, an Exadata system cannot be used without Oracle.  Exadata is an ‘add-on’ storage engine and not an overhaul of the Oracle database engine.

Scalability

Scalability includes several aspects of an IT infrastructure such as handling increases in data and transactional volumes, as well as the increase in multidimensional data, number of users, workload management, query complexity and volume, etc.

Teradata is Linearly Scalable[viii]meaning the database capacity can be increased by adding more nodes to the infrastructure, and when the data volume increases, performance is not affected.

The system is designed for multi-dimensional scalability and provides more robust models for scale-up and scale-out than Oracle.  Although Oracle does have good scalability, bottlenecks are known to occur with the storage subsystem and it does have one of the best data processing speeds, but only up to a certain limit.  For example, if most queries are known and data used is less than 600TB, then Oracle could be suitable, but if the data is expected to increase beyond that, then Teradata is the better choice.

Parallelism

Teradata has unconditional parallelism[ix] whereas Oracle has Conditional.  It gives Teradata an advantage with OLAP as the performance is exceptional to achieve a single answer faster than a non-parallel system. Parallelism uses multiple processors working together to accomplish a task quickly.

To use an analogy of how parallelism works, think of a queue at a busy shopping store, with one queue branching off to separate queues for each till.  The line moves faster this way instead of one queue and one till.  The more efficient the parallel loading, the better the performance of the system.

Teradata has parallelism throughout its’ system, including the architecture, complex processing, and data loading.  Query tuning is not needed to process requests in parallel and Teradata does not depend on column range constraints or limited data quantity, unlike Oracle.

Feature Comparisons

Other mentionable differences include:

• The graphical user interface is far more matured and advanced in Oracle than in Teradata.
• Oracle and Teradata almost support the same programming languages, except Visual Basic languages where Oracle supports it and Teradata does not.
• With operating systems, Oracle runs on Unix, Linux, Windows, Mac OS X, z/OS, whereas Teradata does not run on Mac OS X, z/OS.
• Teradata has substantially improved its compression, although it is considered to be a step behind Oracle’s Exadata’s Hybrid Columnar Compression[x].
  

In Summary

Oracle and Teradata are both mature in product and technology, however Oracle outnumbers the amount of implementations compared to Teradata.

As Oracle is more widely-used than Teradata, it has a rich set of tools, making it the better choice for developers and business users.  Business user perceptions view Teradata as too complex or reserved for specialized experts, therefore it’s easier to find a skilled Oracle user or developer, than it is to find a Teradata one.

Comparison Table