Lesson 1

This module deals with the new data type structures and explains their performance implications for Oracle databases. Prior to the introduction of Oracle, critics were comparing the added functionality of the new object-oriented databases with the traditional relational database model. With Oracle we see several new object-oriented data structures that promise faster performance. In this module, we will take a closer look at these new features. The next lesson reviews the new Oracle data structures.

• Data Structures: Oracle includes numerous data structures to improve the speed of Oracle SQL queries, which takes advantage of the low cost of disk storage. Oracle includes indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. This module explores the internals of Oracle indexing and reviews the standard
  1. b-tree index,
  2. bitmap indexes,
  3. function-based indexes, and
  4. index-only tables (IOTs); and
  demonstrates how these indexes may dramatically increase the speed of Oracle SQL queries. Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query.

By the time you finish this module, you will be able to:

1. List the functionality of each of the Oracle data structures
2. Create an Abstract Data Type (ADT)
3. Create a nested table
4. Define a table with a VARRAY

Every organization has information that it must store and manage to meet its requirements. For example, a corporation must collect and maintain human resources records for its employees. This information must be available to those who need it. An information system is a formal system for storing and processing information. An information system could be a set of cardboard boxes containing manila folders along with rules for how to store and retrieve the folders. However, most companies today use a database to automate their information systems. A database is an organized collection of information treated as a unit. The purpose of a database is to collect, store, and retrieve related information for use by database applications.

Nested tables are still supported and used in Oracle 19c. Nested tables are a type of collection, a table column that can store multiple values in a single row, making them a useful feature for handling multivalued attributes or data that has a hierarchical or repeating structure.
Key Features of Nested Tables in Oracle 19c:

1. Scalability: Nested tables can grow as needed and do not have a fixed size, making them more flexible compared to other collection types like VARRAYs.
2. Storage: Unlike VARRAYs, nested tables are stored separately from the main table, which allows them to be more efficient when working with large data sets.
3. Querying: Oracle provides the ability to query nested tables with SQL, using `TABLE` expressions to join the parent table with the nested table.
4. Indexing: Oracle allows indexing of nested tables, improving the performance of queries on large nested structures.
5. Manipulation: You can manipulate nested tables using SQL or PL/SQL, including inserting, updating, and deleting elements from the nested table.

Use Cases for Nested Tables:

• Storing Repeated Data: When you have a one-to-many relationship and want to avoid creating a separate table, you can use nested tables to store repeated values within a single row.
• Hierarchical Data: Nested tables are useful for representing hierarchical data structures (e.g., departments and employees).
• Object-Relational Mapping: Nested tables are part of Oracle's object-relational features and are useful when working with object types in PL/SQL.

Even though nested tables are still used, some developers opt for alternatives like JSON data types for certain use cases in modern applications. However, nested tables remain a powerful tool in Oracle 19c for specific scenarios where structured data needs to be stored in a relational format.