Search

OakieTags

Who's online

There are currently 0 users and 33 guests online.

Recent comments

Affiliations

November 2010

List partitions

Despite the title and content, this article is more about thinking and testing than it is about list partitions as such. It’s just based on a little thought experiment with list partitioning.

I have a list-partitioned table, with no indexes, defined as follows:


create table area_sales (
	area		varchar2(10)	not null,
	dated		date		not null,
	quantity	number(6),
	value		number(8,2),
	padding		char(100),
	constraint as_area_ck check (area in ('England','Ireland','Scotland','Wales'))
)
partition by list (area) (
	partition england values  ('England'),
	partition ireland values  ('Ireland'),
	partition scotland values ('Scotland'),
	partition wales values    ('Wales')
)
;

You’ll notice that there is no default partition, the partitioning column is declared as not nul and is limited by a check constraint to a very specific set of values, and that every partition is defined to hold exactly one of the legal partition key values. Given those restrictions you might like to think about which of the following queries will be able to do perfect partition elimination:


select	count(*)
from	area_sales
where	area = 'England'
;

select	count(*)
from	area_sales
where	area in ( 'England', 'Ireland', 'Wales')
;

select	count(*)
from	area_sales
where	area != 'England'
;

select	count(*)
from	area_sales
where	area not in ( 'England', 'Ireland', 'Wales')
;

select	count(*)
from	area_sales
where	area between 'England' and 'Ireland'
;

Once you’ve thought about what’s likely to happen, you might want to create and populate the table with a few thousand rows and run some tests to see if your thoughts were correct. Here’s a simple statement to spread some data evenly through the tables:


insert into area_sales
select
	decode(mod(rownum,4),
		0,'England',
		1,'Ireland',
		2,'Scotland',
		3,'Wales'
	),
	sysdate + 0.01 * rownum,
	rownum,
	rownum,
	rownum
from
	all_objects
where
	rownum <= 30000
;

Once you’ve done the tests, and satisfied yourself that you understand what’s going on, you might start thinking about a few variations that might make the behaviour change, such as:

    you add a default partition
    you add a default partiiton and remove the check constraint
    you add a default partition and remove the not null constraint
    you define partitions that hold multiple values
    you change the order of partitions so the key values don’t appear in alphabetical order
    you use a different version of Oracle

I’m not going to tell you the answers to these questions. The point I want to make is that this is the type of model, and these are the types of question you should ask yourself when you start to investigate a feature to see whether it will behave as well as you hope, and what special benefits you might get from it.

In fact, these are the questions I’m asking myself right now as I look at a system which is using list-partitioned tables to hold (lots of) sales data. I think it might be a good idea to restructure the table to disallow nulls, include a check constraintm, and hold just one key value per partition – and then modify some of the code to get rid of “not in” and “!=” predicates.

The trouble is it’s a massive table with lots of indexes, the number of partitions would jump from eight to 40, and the number of times that rows would migrate due to partition key updates would increase; so the cost of restructuring would be large and there would be some fairly undesirable impact on DML that would offset the benefits of more efficient queries.

11.2.0.1 ODBC Update Problem

November 2, 2010 (Updated November 3, 2010) An interesting ODBC problem was brought to my attention yesterday regarding the Microsoft Access 2010 program and the Oracle Database 11.2.0.1 ODBC client.  What is the problem?  The person reported that they were trying to update a table in a 10.2.0.4 Oracle Database using values from a database [...]

Pro Oracle Database 11g RAC on Linux is out

So it has finally happened, the day many of you who patiently waited for. Amazon and other publishers are fulfilling pre-orders! The book I helped publish, Pro Oracle Database 11g RAC on Linux is out in print.

I received my author copies last week, and was very happy to see that after the book was out in the USA first, the “pre-order now” recommendation has been removed from Amazon’s UK and German websites when I last checked.

I have to give great credit to all of those who helped me make this possible with extra input and pointers to related information. On the Linux part Steve has done an incredible job compiling all this useful information you don’t find in this form anywhere else.

From now on, feedback and new research with specific reference to the book will appear on my wordpress blog (the one you are reading now), in a separate category-”RAC Book”.

So thanks again for all your patience while we were working very hard to finish the book.

Cooking kills…

I woke up at about 04:00 this morning, which sounds kinda early, but I ended up going to bed at 17:00 yesterday. I’m still trying to adjust to the new timezone. Anyway, when I woke up there was a vague smell of natural gas. I blew my nose and then I noticed it was actually a very strong smell of gas. I walked down stairs, remembering not to turn on any lights. When I got to the kitchen I found one of the rings on the cooker was turned on about 1/4 of the way. I turned it off and opened every door and window I could. A few minutes later I was able to turn on the lights.

I’m taking this as a sign that cooking is an extremely deadly persuit. If I hadn’t boiled that rice yesterday, I wouldn’t have knocked the other ring by accident and I wouldn’t have had a brush with death…

So next time you are watching Jamie Oliver, Gordon Ramsey or Nigella Lawson, I urge you to point a finger at the TV and scream, “Noooooo!”, then open a can of baked beans and eat them cold, safe in the knowledge that you will not wake up dead. :)

Cheers

Tim…

OT: Movember Update

Well I wasn't expecting such a response. Thinking it over :-

A) I have some very generous friends.
B) I have some very mischevious friends who are looking forward to 4 weeks of fun ;-)
C) Several donors mentioned pictures. Yes, definitely. It's all 'trust but verify' round here.
D) I kind of knew the ORA-DUDE would insist on side-burns.

Sincere thanks for all the donations. There's no going back now. But Lisa and Debra have yet to show me the colour of their money. Cut throat razor? Hall 1?!?

Final note. For you gentlemen who haven't hit Tuesday morning's shave yet, there's still time ...

Go Mo!


Frightening number of linking errors for 11.2.0.1.3 on AIX 5.3 TL11

I just applied PSU 3 for 11.2.0.1 on AIX 5.3 TL11. That would have been more straight forward, had  there not been a host of errors when relinking oracle. There were loads of these:

SEVERE:OPatch invoked as follows: 'apply '
INFO:
Oracle Home       : /u01/app/oracle/product/11.2.0.1
Central Inventory : /u01/app/oracle/product/oraInventory
 from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.3
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0.1/oui
Log file location : /u01/app/oracle/product/11.2.0.1/cfgtoollogs/opatch/opatch2010-11-01_09-28-22AM.log
...
[more output]
...
INFO:Running make for target ioracle
INFO:Start invoking 'make' at Mon Nov 01 09:29:26 GMT 2010Mon Nov 01 09:29:26 GMT 2010
INFO:Finish invoking 'make' at Mon Nov 01 09:29:40 GMT 2010
WARNING:OUI-67215:
OPatch found the word "error" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ld: 0711-415 WARNING: Symbol ldxdts is already exported.
ld: 0711-415 WARNING: Symbol ldxsto is already exported.
ld: 0711-415 WARNING: Symbol lnxadd is already exported.
...
ld: 0711-319 WARNING: Exported symbol not defined: cout__3std
...
ld: 0711-224 WARNING: Duplicate symbol: .kotgscid
...
ld: 0711-783 WARNING: TOC overflow. TOC size: 219488    Maximum size: 65536
 Extra instructions are being generated for each reference to a TOC
 symbol if the symbol is in the TOC overflow area.

Wow, that looked like a completely corrupted installation of Oracle now, and I got ready to roll the change back. However, MOS had an answer to this!

These errors can be ignored as per this MOS document: Relinking causes many warning on AIX [ID 1189533.1]. See also MOS note Note 245372.1 TOC overflow Warning Can Safely Be Ignored and BUG:9828407 – TOO MANY WARNING MESSAGES WHEN INSTALLING ORACLE 11.2 for more information.

Advert: PL/SQL Masterclasses in Bulgaria and Serbia…

In November I’m scheduled to do 2 PL/SQL masterclasses in Europe:

  • Bulgaria (Nov 22-23)
  • Serbia (Nov 25-26)

As was shown by my recent class in Hong Kong, sometimes the number of people wanting to do the course is fine, but they don’t register in time so the class gets cancelled. Luckily in the case of the Hong Kong class I was able to reschedule it in time, but it could easily not have happened.

It is only 3 weeks until these courses start, so Oracle University will have to confirm/cancel them soon. If you are interested in either of the classes, contact your local Oracle Education office to register your interest. If you leave it until 1 week before the class is scheduled to start, you may find it has already been cancelled and I’ll be home watching day-time TV. :)

Cheers

Tim…