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.#

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.

This note is just a little speculation about the problem – I was planning to write some detailed stuff about it, but Richard Foote supplied a suitable answer to the original question and I couldn’t find the time to do anything more detailed.

Basic point, I think, is that someone did the trade off between extra coding and extra benefit and decided that adding the feature would require too much effort for too little reward.  As Richard points out in this reply – the rowid stored in the index entry for a global index is a four-part structure in the order (data_object_id, file_id, block_id, row directory subscript) and a bitmap index entry consists of (user-defined key, start rowid, end rowid, string of bits).

If  Oracle decided to implement a global bitmap index they would either have the extra complication of working out how to make “string of bits” cross a data segment boundary; or they could keep it “easy” by simply ensuring that “string of bits” was limited to a single segment.

• Strategy 1: start worrying about all the complicated side effects that might appear after production implementation if you try to make strings cross segments and decide not to risk it.
• Strategy 2: recognise that if you keep a string inside a single segment then all your processing is going to be focused on “table segment at a time” – so you might as well not bother with creating global indexes, because any index combination code is going to behave pretty much like local indexes – particularly when you realise that each key value within a segment is likely to reference a lot of rows when the primary purpose of global indexes is to avoid doing a large number of (redundant) index probes to find a small number of rows.

One anomaly than pops into my mind – a globally hash partitioned index on a non-partitioned table doesn’t store an object_id. So why not allow for that type of global bitmap index.  Two-fold answer (a) why do more complicated stuff for one special case, and (b) the primary reason for creating globally hash-partitioned indexes on non-partitioned tables is to reduce contention on highly concurrent inserts – and you don’t create bitmap indexes if that’s happening to the table.

Since you can’t create global (or globally partitioned) bitmap indexes, you also can’t use the index_combine() operation to do btree/bitmap conversions, though. So once you’ve got this far with your thinking you might want to look at the possibility of using my “extended index hash join” strategy for getting as close as possible to emulating an index_combine().  (Investigation and demonstrataion left as exercise – but don’t be surprised if you can’t make it work, unexpected things happen with partitioned tables and indexes.)

 

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.

PS enqueue resources represent parallel execution slaves
BF enqueue resources represent Bloom Filters

The OP tells us that he’s getting this deadlock when running a parallel query (i.e. select, not insert, update, or delete). Parallel query is exactly the environment where parallel execution slaves get allocated and freed, and where parallel execution slaves and the query co-ordinator write and read to Bloom Filters.

You may want to read the entire thread to see where we got to – but the end user found an initial workaround to the problem by setting the hidden parameter “_bloom_pruning_enabled” to false. (The query involved a partitioned table, and partition pruning is one of the places where Bloom filtering can be used.)

Update

Identified by Herald ten Dam as bug “12637294 – Deadlock of PS and BF locks during parallel query operations”. Patched in 11.2.0.4.

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.

drop table transactions purge;

create table transactions (
	transaction_date	date		not null,
	job_id			varchar2(10)	not null,
	account_id		number(8)	not null,
	transaction_type	varchar2(2)	not null,
	transaction_id		varchar2(10)	not null,
	amount			number(10,2)	not null,
	padding			varchar2(100),
	transaction_week 	generated always as (trunc(transaction_date,'IW'))
)
partition by range (transaction_week) interval (numtodsinterval(7,'day')) (
	partition p0 values less than (to_date('30-Sep-2013','dd-mon-yyyy'))
)
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate)-7, 1, 1, 'IN', 1, 1, rpad('x',100))
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate), 2, 2, 'IN', 2, 2, rpad('x',100))
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate) + 7, 3, 3, 'IN', 3, 3, rpad('x',100))
;

begin
	dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'transactions',
                method_opt       => 'for all columns size 1'
	);
end;
/

So transaction_date is a column in the table, but the partitioning column is transaction_week, which is defined as trunc(transaction_date,’IW’) which gives me Mondays as the partition boundaries. (I always go back to the manuals for truncating on weeks – there are at least two options (W and IW) which have very different effects.)

So here are a couple of simple queries – will the optimizer do partition elimination ?

select * from transactions where transaction_date = sysdate;
select * from transactions where transaction_date = to_date('17-Oct-2013','dd-mon-yyyy');

And the answer is yes. Note the “partition single”, although in both cases the partition start and stop show KEY/KEY – which means Oracle has to deduce which partitions are relevant at run-time.

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |   130 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRANSACTION_DATE"=SYSDATE@! AND "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(SYSDATE@!,'fmiw'))

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |   130 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRANSACTION_DATE"=TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(TO_DATE(' 2013-10-17 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'),'fmiw'))

Pretty clever !

I find it slightly strange that the predicate section reports the derived predicate on the virtual column – it’s clearly not necessary for this example because we can see that the optimizer has already decided that the query will visit just one partition, so and every row in that partition will have the same value for the virtual column. Possibly its presence is just a side effect of the generic strategy used for more complex predicates and the special-case code hasn’t been included, but maybe there’s something I’ve overlooked that makes it necessary.

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.

Update (in response to comment 1)

An example of what you do, and what you might see, with sql_trace or event 10704 enabled (the latter at level 10):

alter table pt_range
exchange partition p600 with table t1
including indexes
without validation
;

A couple of interesting items from SQL_trace – note particularly the join which is doing some validation and could be quite expensive, but unnoticeable unless you were testing at scale:

LOCK TABLE "PT_RANGE" PARTITION ("P600") ,"T1" IN EXCLUSIVE MODE  NOWAIT

select /*+ first_rows(1) ordered */ 1
from
 "TEST_USER"."T1" A ,"TEST_USER"."PT_RANGE" B where "A"."ID" = "B"."ID" and (
  tbl$or$idx$part$num("TEST_USER"."PT_RANGE",0,0,0 ,B .rowid )  <  3 ) and
  tbl$or$idx$part$num("TEST_USER"."PT_RANGE",0,0,0 ,A .ID )  <>  3 and rownum
  < 2

And a little something for locking (I grep’ed for “TM” – table locking):

ksqgtl *** TM-000130a9-00000000 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TM-000130ab-00000000 mode=3 flags=0x401 timeout=0 ***
ksqgtl *** TM-000130b1-00000000 mode=6 flags=0x401 timeout=0 ***

Note that the (hex) number after the TM is the object id, and the lock mode is clearly visible. In this case we have signs of a row-exclusive lock on the partitioned table, and exclusive locks on the simple table and the partition that was being exchanged. The ksqgtl is getting the lock, ksqrcl (not shown) is the release.

MV Refresh

I have a fairly strong preference for choosing simple solutions over complex solutions, and using Oracle-supplied packages over writing custom code – provided the difference in cost (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.

12c

The news is out that 12c is now available for download (Code, Docs and Tutorials). There are plenty of nice little bits in it, and several important additions or enhancements to the optimizer, but there’s one feature that might prove to be very popular:

SQL> alter table p1 move partition solo ONLINE;

Table altered.

Partitioning 12c

Most useful presentation of OOW so far, from Hermann Baer of Oracle on improvements in partitioning features in 12c – and there are lots of useful ones, including:

Online move of a partition – so easy to compress a partition when it has reached its final “read-only” state

multiple partition maintenance in one operation – e.g. merge 3 monthly partitions into one quarterly partition, or split one partition into 24 (think about “how do I partition a non-partitioned table”, and 12c has just made it easier and quicker – exchange it into an empty partitioned table, then do one massive split).

partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”.

interval-reference partitionining – you can create an interval partitioned table, and use ref-partitioning to create child (and further decendent) tables, and their partitions will automatically be generated, truncated, and dropped as the parent is extended, truncated or dropped (needs enabled foreign key constraints).

Lots more details – and lots of stress-testing to be done – but I’m off to hear “the optimizer lady” talk about hints.

Reminder:

Oracle has a “safe harbour” slide at the start of all presentations about future developments pointing out that the information presented is an indication of direction, but not guaranteed to make it into production.

Recursive SQL

A question came up on the OTN database forum a few days ago asking why a very simple query against a hash partitioned table was doing so much physical I/O to get a very small amount of data from a single partition:

I have table hash-partitioned on an indexed column “id” which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don’t filter on the id column.

What I’m seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression … resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on “id” only and results is 106 selected rows, I see roughly 1400 physical reads.

The poster has clearly applied some careful thought to minimising the work required – so what’s gone wrong ? To help us analyse the problem he posted the results from running a query through autotrace:

Truncate Partition

A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints in place. Let’s start with a demonstration of the problem:

Table rebuilds

Here’s a question from the OTN forum that I’ve seen fairly frequently in the last couple of years:

I have a table abc(c_date date, c_id varchr2(20), c_name varchar2);
The table is already range partitoned on “c_date” column and I have lot of data in that table.
Now my task is to create the hash partions under range; hash partiton needs 16 partitons.
I dont want to create another table with range and hash partitons and migrate the data. The amount of data I have is 3 terabytes.

Indexing

A question about partitioning came up on OTN a few days ago – but the really interesting part of the thread was about the choice of indexes, and how the choice of partitioning, combined with the data patterns, could make a big difference to the choice of indexes. I think the comments I made about indexes are worth seeing, so I’ve linked to the thread.

Partitioned Bitmaps

The following question appeared in a comment to an earlier posting on multi-column bitmap indexes and the inability of Oracle to create a bitmap index join when (to the human eye) the strategy was an obvious choice.

I have a query which is using 2 indexes both are bitmap indexes (sizes are 37 and 24 Mbs) and table size is 17gb. While i ran the following query which can very well get the index itself, it takes around 6-8 minutes and using pga around 3 gb.

could you please explain me why ?

Interval Partitions

Here’s a little gem from the OTN database forum – why should the CPU usage of querying an “interval partitioned” table depend on the size of something that’s missing ?

(The most significant symptom is identified in a reply I wrote to the thread – but that’s only a step in the right direction).

Prefixed

From time to time the question about whether local indexes on partitioned tables should be prefixed or non-prefixed appears on the Oracle forums and mailing lists.

It’s possible that I’m wrong – although no-one has come up with a counter-example to the statement I keep repeating – but the whole prefixed/non-prefixed thing for local indexes dates back to a limitation in the optimizer somewhere in the 8.0 time line where Oracle couldn’t do partition elimination on indexes properly but the overhead of the error it made could be dramatically reduced (in most cases) by sticking the partition key at the start of the index.

The guideline for local indexes are the same as they would be for a non-partitioned index on a non-partitioned heap table – the partitioning column(s) are just columns in the table that might be worth including in the index, and the order of the index columns is dictated by the important queries that have to access the table.

For further comments, there’s a note I wrote (which I’ve just been reminded of) on the OTN database forum that adds a little detail to this argument.