Lesson 1

When working with large volumes of data in a single table, the capability to divide data into distinct, logical groups can help optimize queries. Partitioning^[1] tables has been used prior to Oracle8, and this lesson focuses on the Oracle features that enhance and expand the capability to partition tables.

To avoid the recreation of any bitmap index structure, Oracle recommends creating every partitioned table with at least one compressed partition whenever you plan to partially or fully compress the partitioned table in the future. This compressed partition can stay empty or even can be dropped after the partition table creation. Having a partitioned table with compressed partitions can lead to slightly larger bitmap index structures for the uncompressed partitions. The bitmap index structures for the compressed partitions, however, are in most cases smaller than the appropriate bitmap index structure before table compression. This highly depends on the achieved compression rates.

Optimizing database queries for better performance in Oracle 19c involves several strategies that can help reduce response times and improve overall database efficiency. Here are some key approaches:

1. Understand Execution Plans: Use the `EXPLAIN PLAN` statement to understand how Oracle executes your SQL queries. This will help you identify potential bottlenecks, such as full table scans or inefficient joins.
2. Indexing: Proper indexing is crucial for query performance. Consider creating indexes on columns that are frequently used in WHERE clauses, JOIN conditions, or as part of an ORDER BY clause. However, be mindful not to over-index as this can slow down DML operations (INSERT, UPDATE, DELETE).
3. SQL Tuning Advisor: Oracle provides the SQL Tuning Advisor tool that analyzes SQL statements and provides recommendations for optimizing performance. It can suggest creating indexes, restructuring SQL queries, or implementing SQL profiles.
4. Optimizer Hints: You can use optimizer hints to influence the way Oracle executes a query. Although it's generally recommended to let Oracle's optimizer choose the execution plan, in some cases, providing hints like `/*+ INDEX() */`, `/*+ FULL() */`, or `/*+ LEADING() */` can improve performance.
5. Partitioning: If you have very large tables, consider partitioning them. Partitioning can help reduce the amount of data scanned for a query and can also improve DML operations' performance by limiting the number of partitions that need to be accessed or modified.
6. Statistics Gathering: Ensure that statistics for your database objects are up to date. Oracle uses these statistics to determine the most efficient execution plan for a query. Use the `DBMS_STATS` package to gather and manage statistics.
7. Avoiding Full Table Scans: Try to design queries and indexes in a way that avoids full table scans, unless absolutely necessary. Full table scans can be very costly in terms of performance, especially for large tables.
8. SQL Plan Management: Use SQL Plan Management (SPM) to maintain consistent performance of SQL statements, even when changes occur in the database (such as upgrades or changes in data distribution). SPM captures and evolves execution plans to ensure that only verified plans are used.
9. Connection Pooling: If your application connects to the database frequently, use connection pooling to reduce the overhead associated with establishing connections to the database.
10. Proper Use of Bind Variables: Use bind variables in your SQL statements to improve performance and avoid hard parsing. Hard parsing is more resource-intensive than soft parsing, as it involves syntax check, semantic check, and generating an execution plan.
11. Caching: Consider caching frequently accessed data in memory to reduce physical I/O operations. Oracle provides several caching mechanisms, including the Result Cache, which can store the results of queries or PL/SQL function calls.
12. Analyzing and Refactoring Queries: Analyze complex queries to identify possible improvements, such as eliminating unnecessary columns, avoiding nested subqueries when possible, and combining multiple queries into a single, more efficient query using joins.
13. Use of Aggregate Functions: When working with aggregate functions (e.g., `SUM`, `AVG`), consider whether you can use Oracle's analytic functions instead of subqueries, which can often lead to better performance.

By applying these strategies, you can significantly enhance the performance of your Oracle 19c database queries. It's also beneficial to continuously monitor query performance and adjust your optimization techniques as necessary, since what works best can change over time with data growth and changes in query patterns.

Purpose: Use the ANALYZE statement to collect statistics, for example, to:

1. Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2. Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3. Identify migrated and chained rows of a table or cluster.

By the end of the module, you will be able to:

1. Partition an index-organized table
2. Partition an object table
3. Partition a table with LOBs
4. Describe how to enable row movement in a partitioned table
5. Rename, move, and coalesce partitions
6. Exchange a partition with a table
7. Describe how to create a partition-wise query or join

You can partition tables that contain LOB columns. As a result, LOBs can take advantage of all of the benefits of partitioning including the following:

1. LOB segments can be spread between several tablespaces to balance I/O load and to make backup and recovery more manageable.
2. LOBs in a partitioned table become easier to maintain.
3. LOBs can be partitioned into logical groups to speed up operations on LOBs that are accessed as a group.

This section describes some of the ways you can manipulate LOBs in partitioned tables.
The next lesson looks at how to partition an index-organized table.

[1] Partitioning: Dividing one table into several sections, or partitions, which are physically stored as if each partition were a separate table.