X

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...

Wednesday, May 9, 2018 | Autonomous | Read More
Oracle

Integrated Cloud Applications & Platform Services