Lesson 1

Performance tuning is not only about rewriting SQL. In Oracle, query speed is strongly influenced by the data structures the optimizer can use to access and filter data efficiently. These structures determine whether Oracle can satisfy a query with fast index access paths, or whether it must fall back to expensive operations such as large-table full scans, disk sorts, and high-volume I/O.

This concept is still required in Oracle Database 23ai. Even with modern automation (SQL plan management features, advisors, and improved instrumentation), the cost-based optimizer can only choose efficient plans when the physical design gives it good options. Index choice, index type, and data representation directly shape access paths, join methods, and sort requirements.

• Indexing data structures for performance: Oracle provides multiple index structures that can dramatically reduce I/O and CPU for selective queries and join operations. In this module, you will review how Oracle indexing works internally and when each structure is appropriate:
  1. B-tree indexes (general-purpose OLTP indexing for high-selectivity predicates)
  2. Bitmap indexes (often effective for low-cardinality columns in read-mostly analytic workloads)
  3. Function-based indexes (index expressions so predicates can avoid runtime computation and full scans)
  4. Index-organized tables (IOTs) (store table data in a B-tree structure ordered by the primary key)

  The tuning objective is simple: design structures that let Oracle avoid unnecessary full-table scans and large disk sorts when servicing common queries.

• Object-relational and collection data structures: Oracle also supports object-relational structures such as object types (ADTs) and collection types like nested tables and VARRAYs. These features remain supported in modern Oracle releases and can be useful in PL/SQL and specialized modeling scenarios. However, they must be used deliberately, because they can introduce hidden storage and query costs when compared to conventional relational normalization.

  In modern designs, you will also see JSON used for certain multivalued or document-style attributes. Oracle’s direction increasingly emphasizes “relational storage with JSON views” for document access patterns, but object-relational collections continue to exist as first-class types for specific use cases.

The next lesson begins the detailed review of these data structures and connects each one to common performance outcomes, such as reduced logical reads, fewer physical reads, improved join efficiency, and reduced sort pressure.

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

1. List the functionality and performance implications of key Oracle data structures
2. Create an Abstract Data Type (ADT) and explain when it is appropriate
3. Create and query a nested table, including why storage differs from the parent table
4. Define a table with a VARRAY and explain when ordered, bounded collections are a good fit

Databases exist to collect, store, and retrieve related information to meet business requirements. Oracle provides both traditional relational structures and optional object-relational structures. The tuning skill is knowing which structures accelerate your dominant access patterns—and which ones introduce complexity or overhead without measurable benefit.

Nested tables and VARRAYs are Oracle collection data types. They remain supported and are most commonly used in PL/SQL, object-relational designs, and specialized schemas that benefit from strongly typed collections.

Key differences:

• VARRAY: ordered collection, typically bounded in size; often used when you retrieve and manipulate the whole collection.
• Nested table: conceptually unbounded collection; can be stored and queried in ways that behave more like a relational child table.

From a performance perspective, the important questions are: how often you query individual elements, how large the collection can grow, and whether you need indexes on collection elements. When element-level querying and indexing matters, a conventional relational child table (or a carefully designed nested table implementation) is usually more predictable than “packing” values into a single row.

In later lessons, you will connect these structures back to tuning fundamentals: access paths, join strategies, cardinality estimation, and the practical impact of your physical design on query plans.