Insights into Statistics, Query Optimization and the Oracle Optimizer
Autonomous
The Oracle Optimizer and ADWC - Hints
This is Part 3 of a series on the Oracle Optimizer in the Oracle Autonomous Data Warehouse Cloud. You can find part 1 here and part 2 here. It's time to take a look at optimizer hints. Here's our test query: select sum(t1.num), sum(t2.num) from table1 t1 join table2 t2 on (t1.id = t2.id); Executing on an ADW database (using the LOW consumer group) yields this plan: ---------------------------------------------------------------------------------------- | Id | Operation ...
Monday, June 11, 2018 | Autonomous | Read More
The Oracle Optimizer and ADWC - Statistics-Based Query Transformation
This is Part 2 of a series on the Oracle Optimizer in the Oracle Autonomous Data Warehouse Cloud. You can find Part 1 here. In this post, I'm going to cover a clever optimization included in ADWC: Statistics-Based Query Transformation. I'm sure that you can guess what it is already, but let's take it step-by-step. Consider the following query: SELECT max(sale_value) FROM huge_fact_table; To find MAX, the Oracle Database would normally need to scan all of the rows in the...
Friday, May 18, 2018 | Autonomous | Read More
The Oracle Optimizer and ADWC - Statistics and Bulk Load
It's time for some posts on the Oracle Autonomous Data Warehouse Cloud and the enhancements we've made to Statistics Management and the Oracle Optimizer. This is Part 1, and it covers statistics maintenance during bulk load. I'll add links to the other posts when they become available. My scripts on the topic of autonomous are stored in GitHub here. The scripts for this post are here. Statistics and Bulk Loading Consider an empty fact table called FACT1. Let's populate...