Lesson 1

Index-organized tables were introduced in Oracle8. While the core purpose of storing data in the order of the primary key index remains the same, the functionality and capabilities of index-organized tables have definitely evolved over time with subsequent Oracle releases, including:
Enhanced Functionality:

• Support for secondary indexes, partitioning, and materialized views: These features, introduced in Oracle9i and later, provided greater flexibility and query performance improvements for index-organized tables.
• Additional data types and compression options: Newer versions introduced support for more data types and compression options, allowing for more efficient storage and retrieval of data.

Improved Performance:

• Faster query performance, especially for range queries: Optimizations throughout the releases led to significant performance gains, particularly for queries that leverage the ordered nature of the data.

Increased Scalability and Security:

• Handling larger datasets and more concurrent users: Oracle versions like 12c and 19c improved the ability of index-organized tables to handle massive datasets and high user concurrency.
• Addressing security vulnerabilities: Security enhancements were implemented to address potential vulnerabilities associated with index-organized tables.

Overall, the purpose of index-organized tables remains the same, but their capabilities and performance have seen significant improvements over time.
Here's a table summarizing the key changes:

Oracle Version	Key Enhancements
8	Introduced index-organized tables
9i	Support for secondary indexes, partitioning, and materialized views
10g	Improved performance for range queries
11g	Additional data types and compression options
12c	Increased scalability for larger datasets and more users
18c	Enhanced security
19c	Continued improvements in compatibility and new features

So far in this course, you have learned how to use both partitioning and clusters to optimize the physical retrieval of data in your Oracle database from disk. Index-organized tables is a feature that was introduced with Oracle8, and these database structures can improve performance when used with appropriate types of data. In this module, you will not only learn about index-organized tables, but you learn to:

1. Explain how index-organized tables differ from normal tables
2. Know when to use an index-organized table
3. Create an index-organized table
4. Create secondary indexes on an index-organized table
5. Handle large rows in an index-organized table
6. Delete and modify index-organized tables
7. Reorganize an index-organized table

Like clusters, index-organized tables are not appropriate for all types of data, although in suitable situations, they too canprovide a significant performance increase.

In general, rows within a regular table are unordered. Although the Oracle DBMS offers many different ways to physically organize tables on disk

1. heap tables,
2. index clusters,
3. hash clusters,
4. index-organized tables, and
5. sorted hash clusters

you should never expect the rows to be physically stored in a certain order. Even if a particular order exists today, there is no guarantee that it will be the same tomorrow. This is a fundamental property of relational databases. See Rule Number 8.
Suppose the EMPLOYEES table contains 50,000 rows (instead of the 14 rows we have), and suppose you want to know which employees have a name starting with the letter 'Q'. Normally, the Oracle DBMS can use only one method to produce the results for this query: by accessing all 50,000 rows (with a full table scan) and checking the name for each of those rows. This could take quite some time, and perhaps there would be no employees at all with such a name. An index on employee names would be very useful in this situation. When you create an index, the Oracle DBMS creates, and starts to maintain, a separate database object containing a sorted list of column values (or column combination values) with row identifiers referring to the corresponding rows in the table. To further optimize access, indexes are internally organized in a tree structure. If there were such an index on employee names, the optimizer could decide to abandon the full table scan approach and perform an index search instead. The index offers a very efficient access path to all names, returning all row identifiers of employees with a name starting with a Q. This probably would result in a huge performance improvement, because there are only a few database blocks to be visited to produce the query result.
In the next lesson, you will learn how index-organized tables differ from normal tables.