Search

OakieTags

Who's online

There are currently 1 user and 15 guests online.

Online users

Recent comments

partitioning

12.2 Online Conversion of a Non-Partitioned Table to a Partitioned Table (A Small Plot Of Land)

In my previous post, I discussed how you can now move heap tables online with Oracle Database 12.2 and how this can be very beneficial in helping to address issues with the Clustering Factor of key indexes. A problem with this technique is that is requires the entire table to be effectively reorganised when most of […]

Partition count for interval partitioned tables

When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below

How to move Partitions ONLINE and make them READ ONLY in #Oracle 12c

readonlyhttps://uhesse.files.wordpress.com/2017/01/readonly.png?w=574&h=60

How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle

fight_contention_2https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=576&... 576w, https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=144&... 144w" sizes="(max-width: 288px) 100vw, 288px" />

Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:

How to reduce Buffer Busy Waits with Hash Partitioned Indexes in #Oracle

fight_contentionhttps://uhesse.files.wordpress.com/2016/12/fight_contention.png?w=150&h=132 150w, https://uhesse.files.wordpress.com/2016/12/fight_contention.png 474w" sizes="(max-width: 300px) 100vw, 300px" />

Buffer Busy Waits can be a serious problem for large OLTP systems on both tables and indexes. If e.g. many inserts from multiple sessions occur simultaneously, they may have to compete about the same index leaf blocks like the picture below shows:

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:

How to rebuild a 12 TB index that you accidentally dropped from a 55 TB table

Once upon a time there was a very experienced database administrator who accidentally dropped a 12 TB index from a 55 TB table. The question I had was “How did he fix it?”(read more)

DML and Bloom

One of the comments on my recent posting about “Why use pl/sql bulk strategies over simple SQL” pointed out that it’s not just distributed queries that can change plans dramatically when you change from a simple select to “insert into … select …”; there’s a similar problem with queries that use Bloom filters – the filter disappears when you change from the query to the DML.

This seemed a little bizarre, so I did a quick search on MoS (using the terms “insert select Bloom Filter”) to check for known bugs and then tried to run up a quick demo. Here’s a summary of the related bugs that I found through my first simple search:

Virtual Partitions

Here’s a story of (my) failure prompted by a recent OTN posting.

The OP wants to use composite partitioning based on two different date columns – the table should be partitioned by range on the first date and subpartitioned by month on the second date. Here’s the (slightly modified) table creation script he supplied: