Search

Top 60 Oracle Blogs

Recent comments

data warehouse

Data Warehouse Design: To Index, or Not to Index, that is the question

This post is part of a series that discusses some common issues in data warehouses.

When you query a star schema, you essentially have two choices;

  • bitmap index and star transformation 
  • full scan, bloom filter, and hash join

Star Transformation 

Star transformation was introduced in Oracle 8(see also Oracle Optimizer Blog: Optimizer Transformations: Star Transformation).   A star transformation requires:

How Not to Build A(n Autonomous) Data Warehouse

My day job involves investigating and resolving performance problems, so I get to see a lot of bad stuff.  Often, these problems have their roots in poor design.  It is not surprising. but is nonetheless disappointing, that when I point this out I am told that the system is either delivered this way by the vendor, or it has already been built and it is too late to change.
In the last couple of years, I have worked on several data warehouse applications that have provided the inspiration for a new presentation that I am giving at the DOAG and UKOUG conferences this year.
The presentation and this series of related blogs have several objectives:

Data Warehouse Design Mistakes 1: Lack of Foreign Key Constraints

This post is part of a series that discusses some common issues in data warehouses.

Data Warehouse Design Mistakes 2: Effective Dating Dimensions

This post is part of a series that discusses some common issues in data warehouses.

I have seen situations where some dimensions are effective-dated.  That is to say that there are multiple rows in the dimension table for the same main dimension id, but for different date ranges.  At least one of the date columns has to become part of the primary key.
The dimensions in the Sales History sample schema have got effective from and to dates, but these columns are not part of the primary key.  Let's imagine that the products get updated every year and a new version of the product is sold, requiring a new dimension row.

How Partial Indexing helps you save space in #Oracle 12c

partialhttps://uhesse.files.wordpress.com/2016/12/partial1.png?w=600&h=580 600w, https://uhesse.files.wordpress.com/2016/12/partial1.png?w=150&h=145 150w" sizes="(max-width: 300px) 100vw, 300px" />

Over time certain partitions may become less popular. In 12c, you don’t have to index these partitions anymore! This can save huge amounts of space and is one of the best 12c New Features in my opinion. Really a big deal if you are working with range partitioned tables where the phenomenon of old ranges becoming unpopular is very common. Let’s have a look, first at the problem:

How to change RANGE- to INTERVAL-Partitioning in #Oracle

set_intervalhttps://uhesse.files.wordpress.com/2016/11/set_interval.png?w=578&h=600 578w, https://uhesse.files.wordpress.com/2016/11/set_interval.png?w=144&h=150 144w" sizes="(max-width: 289px) 100vw, 289px" />

An existing RANGE partitioned table can easily be changed to be INTERVAL partitioned with the SET INTERVAL command. My table has been created initially like this:

Canberra Event – Next Let’s Talk Database presentation by Richard Foote

Let’s Talk Database – Thursday, 28 July 2016

The next in Richard Foote’s popular Let’s Talk Database series – Let’s Talk Database: Oracle Database 12c – Built for Data Warehousing – is on in Canberra on July 28th. These are free events but due to limited places have often “sold out” in the past, so booking early is recommended to avoid disappointment.

Session Details

The Oracle Database is the leading database in market but it might come as a surprise to some that it’s also the leading database with respect to Data Warehousing in terms of both sales and analyst rankings. The focus today is a detailed look at all the special database capabilities that makes the Oracle Database the perfect platform for Data Warehouse type applications.

OOW11: Exalytics Hits The Stage — In-Memory Analytics

News from Oracle OpenWorld flor… What is Exalytics? It’s a BI appliance machine — it’s like an application middle tier for complete Business Intellegence data warehousing solutions. You put it in front of Exadata and users get all the tools to work with that data – analyze, predict, run reports and etc. Exalytics is a [...]

The Core Performance Fundamentals Of Oracle Data Warehousing – Table Compression

[back to Introduction] Editor’s note: This blog post does not cover Exadata Hybrid Columnar Compression. The first thing that comes to most people’s mind when database table compression is mentioned is the savings it yields in terms of disk space. While reducing the footprint of data on disk is relevant, I would argue it is the lesser of the benefits for data warehouses. Disk capacity is very cheap and generally plentiful, however, disk bandwidth (scan speed) is proportional to the number of spindles, no mater what the disk capacity and thus is more expensive. Table compression reduces the footprint on the disk drives that a given data set occupies so the amount of physical data that must be read off the disk platters is reduced when compared to the uncompressed version. For example, if 4000 GB of raw data can compress to 1000 GB, it can be read off the same disk drives 4X as fast because it is reading and transferring 1/4 of the data off the spindles (relative to the uncompressed size). Likewise, table compression allows for the database buffer cache to contain more data without having to increase the memory allocation because more rows can be stored [...]

The Core Performance Fundamentals Of Oracle Data Warehousing – Balanced Hardware Configuration

[back to Introduction] If you want to build a house that will stand the test of time, you need to build on a solid foundation. The same goes for architecting computer systems that run databases. If the underlying hardware is not sized appropriately it will likely lead to people blaming software. All too often I [...]