Automatic Indexing in Oracle 19c version

Machine learning is essential in Oracle 19c's Automatic Indexing, enabling the automatic creation, management, and optimization of indexes based on workload patterns.

Machine learning in automatic indexing works like a smart librarian organizing books based on how often they’re checked out and what people are looking for. Here’s a simple explanation along with an analogy:

Simple Explanation

1. Observing Patterns: The system watches which SQL queries (like questions about the data) are run most frequently and how long they take to get results. It learns which queries need faster access to the data.

2. Making Suggestions: Based on this observation, it suggests or creates new indexes (like special bookmarks) for the columns most often used in those queries.

3. Adjusting Over Time: If the system notices that certain indexes aren’t used anymore (like old bookmarks that are no longer needed), it can remove or change them to keep things efficient.

Analogy

Imagine a library where every time someone asks for a book, the librarian takes note of it.

Observing Patterns: The librarian notices that a lot of people are asking for books on gardening but not many on obscure historical events.

Making Suggestions: To make it easier for everyone, the librarian starts putting the gardening books in a special, easy-to-find section and might even create a new section for popular topics based on demand.

Adjusting Over Time: If gardening becomes less popular and people start asking for cooking books instead, the librarian can move the gardening books back and make a new section for cooking books.

Example

In Database Terms:

If many users frequently run a query to find all orders from the last month (e.g., SELECT * FROM Orders WHERE order_date > '2024-09-01'), the automatic indexing system will notice this pattern.

It might create an index on the order_date column, making this query run much faster in the future.

If later it finds that no one is using that index (perhaps because users are now looking for customer information instead), it will remove that index and focus on optimizing the columns that are currently in demand.

Conclusion

Just like a librarian adapts the library’s organization based on what readers want, machine learning in automatic indexing helps databases stay efficient by automatically managing indexes based on how users interact with the data.

machine learning in automatic indexing significantly improves database performance by intelligently managing indexes according to real-time data and evolving patterns, thus minimizing administrative overhead