Oracle Autonomous Data Warehouse Cloud does not allow to create indexes. Is this a problem for star schemas because no Star Transformation can be used? Or are the required bitmap indexes automatically created? A look under the hood of ADWC.
The Autonomous Data Warehouse Cloud (ADWC) is a self-configuring, fast, secure and scalable platform for data warehouses. Does this mean we don’t have to take care anymore about performance of our ETL processes? Which performance tips are still important for us, and where can we hand over the responsibility to ADWC? A revised version of an old blog post, with regard to Oracle’s Data Warehouse Cloud solution.
Optimizer statistics are essential for good execution plans and fast performance of the SQL queries. Of course, this is also the case in the Autonomous Data Warehouse Cloud. But the handling of gathering statistics is slightly different from what we know from other Oracle databases.
Since Oracle 12c, the query optimizer is able to change execution plans at runtime. This feature is called “Adaptive Plans”. Something similar happened on the ODTUG Community Service Day at the Kscope conference in Orlando.
Global temporary tables (or private temporary tables, if you use Oracle 18c) are a practical feature to be used for SQL queries with multiple steps, for example complex reports in a data warehouse. If you want to use them, some details must be considered.
Complex SQL statements are hard to understand – not only for us, but for the query optimizer, too. Sometimes, it is a good idea to write a simpler SQL query to do the same work. Here a nice story about a performance improvement by rewriting the SQL statement.
Direct-Path INSERT is a very efficient way to load data into data warehouse tables. The load performance can even be improved with NOLOGGING on the target table. In Oracle 12c Release 2, this is even possible for Oracle Data Guard environments. But is it a good idea?
