partitioning

Jonathan Lewis's picture

Missing Bloom

Here’s a little surprise that came up on the OTN database forum a few days ago. Rather than describe it, I’m just going to create a data set to demonstrate it, initially using 11.2.0.4 although the same thing happens on 12.1.0.2. The target is a query that joins to a range/hash composite partitioned table and uses a Bloom filter to do partition pruning at the subpartition level.  (Note to self: is it possible to see Bloom filters that operate at both the partition and subpartition level from a single join? I suspect not.). Here’s my code to create and populate both the partitioned table and a driving table for the query:

Jonathan Lewis's picture

Reverse Key

A question came up on the OTN database forum recently asking if you could have a partitioned index on a non-partitioned table.

(Aside: I’m not sure whether it would be quicker to read the manuals or try the experiment – either would probably be quicker than posing the question to the forum. As so often happens in these RTFM questions the OP didn’t bother to acknowledge any of the responses)

Richard Foote's picture

12c Online Partitioned Table Reorganisation Part I (Prelude)

First post for 2014 !! Although it’s generally not an overly common activity with Oracle databases, reorganising a table can be somewhat painful, primarily because of the associated locking implications and the impact it has on indexes. If we look at the following example: So we have a table with a couple of indexes. We […]

Jonathan Lewis's picture

Current row

Here’s a question that I’ve had on my todo (and draft posts) list for a few years – so I’m presenting it as a task for anyone who can demonstrate the answer.

If you’ve got a pl/sql cursor open and you’re using the “update current of” syntax, what happens if you update the same row twice but the row comes from a partitioned table and moves to a new partition on the first update ?

If you have a demo that you want to include in the comments then start with “sourcecode” and end with “/sourcecode” – in square brackets, without the quotation marks – to get a fixed font format and space preservation. If you have a good demonstration or reference article that you can link to, a simple URL will do nicely.#

Jonathan Lewis's picture

Bitmap Question

This question came up on the OTN database forum a couple of months ago: “Why doesn’t Oracle allow you to create globally partitioned bitmap indexes?” The obvius answer is “It just doesn’t, okay.” But it can be quite interesting to think of reasons why a particular mechanism might not have been implemented – sometimes the answers can give you an insight into how a feature has been implemented, it might suggest cases where a feature might not work very well, it might give you some ideas on how to work around a particular limitation, and sometimes it just an entertaining puzzle to while away a short flight.

Jonathan Lewis's picture

Deadlock

There an interesting example of a deadlock on the OTN database forum:

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
PS-00000001-00000011        92     423     S             33     128     S     X
BF-2ed08c01-00000000        33     128     S             92     423     S     X

One of the responses to the post points out that Oracle error ORA-00060 is an application error and the OP needs to fix his code – and that’s usually a valid comment, especially if the deadlock involves only TX enqueues, TM enquees or a mixture of both; but this deadlock is between a BF and a PS enqueue.

Jonathan Lewis's picture

Virtual date partitions

I posted this question on twitter earlier on today (It was a thought that crossed my mind during a (terrible) presentation on partitioning that I had to sit through a few weeks ago – it’s always possible to be prompted to think of some interesting questions no matter how bad the presentation is, though):

Quiz: if you create a virtual column as trunc(date_col,’W') and partition on it – will a query on date_col result in partition elimination?

The answer is yes – on the version of Oracle that I happened to have to hand (12c) the next time I had a few minutes spare. Here’s a quick and dirty demo – with data adjusted to the publication date, so you may need to adjust the code to your current date.

Jonathan Lewis's picture

Testing Partitions

A quick check-list on testing new partitioning features:

  • Step 1 – test with a small amount of data in place, just to see if it works at all
  • Step 2 – test with a reasonably large amount of data in place so that you can spot any unexpected time being lost
  • Step 3 – test with a few uncommitted transactions from other sessions so that you can spot locking problems easily
  • Step 4 – test with SQL tracing enabled so that you can check the trace files for any recursive SQL threats
  • Step 5 – (optional, and harder to recognise) test with event 10704 set so that you can check the trace files for locking threats

Whatever else you do, though, make sure that you always have some data in every object because Oracle has some special optimisations for dealing with partitions that are known to be empty so, in the absence of data, you may be testing something that will never happen in production.

Jonathan Lewis's picture

MV Refresh

I have a fairly strong preference for choosing simple solutions over complex solutions, and using Oracle-supplied packaged over writing custom code – provided the difference in cost THere’(whether that’s in human effort, run-time resources, or licence fees) is acceptable. Sometimes, though, the gap between simplicity and cost is so extreme that a hand-crafted solution is clearly the better choice. Here’s an idea prompted by a recent visit to a site that makes use of materialized views and also happens to be licensed for the partitioning option.

arupnanda's picture

New York Oracle User Group Fall Conference Materials

Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time - with me. I sincerely hope you found both the presentations enlightening as well as entertaining.

Please see the details of the sessions below along with the download links.

Keynote: Oracle 12c Gee Whiz Features

Yet another Oracle version is out and so are about 1500 new features in a variety of areas. Some are well marketed (e.g. pluggable database or the multitenant option) and some shine by their sheer usefulness. And there are some that do not get a whole lot of coverage but are are hidden gems. In this session you learned 12 broad areas of Oracle Database 12c I feel are worth learning about to make your job as a DBA or developer better, easier, smoother and, in some cases, even make it possible what was hitherto impossible or impractical.

To prevent automated spam submissions leave this field empty.
Syndicate content