Data Vault Queries and Join Elimination
If you work with Data Vault for a data warehouse running in an Oracle database, I strongly recommend to use Oracle 12.2 or higher. Why that? Since Oracle 12c Release 2, join elimination works for more than one join column. This is essential for queries…
Partition Pruning and NLS Settings
NLS (National Language Support) parameters are very useful for multilingual applications in an Oracle database. But it is important to understand their impact on query performance. I was aware that NLS is important for indexes, but never thought about its impact on partitioning. In a…
How to Save 7 Keystrokes with dbms_xplan
An almost unknown simplification in the usage of dbms_xplan is possible since Oracle 12.2. Sometimes, there are small details that makes our job (a little bit) easier. In one of his presentations at the Kscope19 conference, Kim Berg Hansen mentioned an interesting detail about the syntax…
Bitmap-based COUNT(DISTINCT) Functions in Oracle 19c
Oracle 19c provides a set of new SQL functions for bitmap operations. They allow fast computation of COUNT(DISTINCT) expressions. What is the purpose of these functions? The Oracle documentation is very sparse, but at least it tells us that the main focus are Materialized Views on…
Materialized View Refresh for Dummies
Materialized Views are often used in data warehouses to improve query performance on aggregated data. But what if it takes too long to refresh the materialized views? Here are some basic rules to improve refresh performance. Unlike indexes, materialized views are not automatically updated with…
Data Types of Join Columns
The data type of primary key and foreign key columns has an impact on the query performance. This should be considered in the design decision of keys in a Data Vault schema. In a performance review of a customer project, we detected a lot of…
Star Schema Optimization in Autonomous Data Warehouse Cloud
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. A typical recommendation for star…
10 Tips to Improve ETL Performance – Revised for 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…
Gathering Statistics in the Autonomous Data Warehouse Cloud
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….
Bloom filters and parallel execution
Today I want to share with you another interesting example of solving performance problem which happened in the database used for Business Intelligence purposes. Users were complaining that the performance in kind of staging database (STGDB) was much better than the production one (PRDDB), even…
Social Media @trivadis