Composite Interval Partitioning isn't as advertised.

Oracle® Database VLDB and Partitioning Guide 11g Release 1 (11.1) Part Number B32024-01 says:

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition.

You can create single-level interval partitioned tables as well as the following composite partitioned tables:

* Interval-range

* Interval-hash

* Interval-list

Sure, I can create these composite partitions, but the results aren't particularly useful. When I tried. Oracle spread my results nicely between the two hash subpartitions for the manually defined partition, but put everything in the same subpartition for the interval generated partition. Notice that these are identical sets of rows. The only difference is the key to force them into the manually specified partition or the generated partition. I assume that there is a metalink note on this somewhere.

I got equivalent results for interval-list composite partitioning. I won't bore the reader with the step-by-step for that test since the results are also that all rows in the generated partitions are forced into one subpartition.

Here are my results for the interval hash test:

SQL> create table interval_hash (
N number,
N2 number
)
partition by range(N) interval (2)
SUBPARTITION BY HASH (N2)
(partition p1 values less than (2)
(SUBPARTITION p_1 ,
SUBPARTITION p_2
));

Table created.

SQL> BEGIN

FOR i IN 1 .. 15 LOOP

INSERT INTO interval_hash VALUES (5, i);
INSERT INTO interval_hash VALUES (0, i);

END LOOP;
COMMIT;
END;
/

PL/SQL procedure successfully completed.


SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_HASH', granularity=>'ALL');

PL/SQL procedure successfully completed.


SQL> SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
INTERVAL_HASH P1..................P_1........................... 6
INTERVAL_HASH P1..................P_2........................... 9
INTERVAL_HASH SYS_P138......SYS_SUBP137............15

(I am having tabbing problems in blogger. I hope that my added lines of dots don't confuse too much)


SQL> select * from interval_hash subpartition(p_2) order by n2;

N N2
---------- ----------
0 1
0 3
0 4
0 7
0 9
0 10
0 12
0 14
0 15

9 rows selected.

SQL> select * from interval_hash subpartition(p_1) order by n2;

N N2
---------- ----------
0 2
0 5
0 6
0 8
0 11
0 13

6 rows selected.


SQL> select * from interval_hash subpartition(SYS_SUBP137) ORDER BY N2;

N N2
---------- ----------
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
5 9
5 10
5 11
5 12
5 13
5 14
5 15

15 rows selected.

That's a whole lot of partitions!

Playing with interval partitioning...
I create the simplest table possible and insert 3 rows - generating 3 partitions.

SQL> create table d1 (dt date)
2 partition by range (dt) interval (numtoyminterval(1,'MONTH'))
3 (PARTITION P1 VALUES LESS THAN (TO_DATE('08/01/1776', 'MM/DD/YYYY')));

Table created.


SQL> insert into d1 values (to_date('07/04/1776', 'MM/DD/YYYY'));

1 row created.

SQL> insert into d1 values (to_date('09/22/1862', 'MM/DD/YYYY'));

1 row created.

SQL> insert into d1 values (to_date('08/18/1920', 'MM/DD/YYYY'));

1 row created.


SQL> select * from d1;

DT
---------
04-JUL-76
22-SEP-62
18-AUG-20


SQL> select table_name, partition_name from user_tab_partitions where table_name = 'D1';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
D1 P1
D1 SYS_P62
D1 SYS_P63

But when I look at the partition_count in user_part_tables...

SQL> select table_name, partition_count from user_PART_TABLES where table_name = 'D1';

TABLE_NAME PARTITION_COUNT
------------------------------ ---------------
D1 1048575

That's a whole lot of partitions! Clearly that is the maximum possible partitions. It's odd that the developers at Oracle chose to store that value there rather than the actual count of partitions created. They obviously have it available. Ah, the mysteries of the Oracle.

It's wonderful, but will I survive?

It's Open World! Sunday a full day of IOUG lectures. Today I heard Jonathan Lewis on "Performance Tuning - being an expert"; Greg Rahm on Data Warehousing and Exedata; Cary Millsap on Performance and Chen Shapira on the uses of charts. I had an introduction to desktop widgets from two experts, I was the only attendee. And I had a nice long introduction to Apex at the Demo Grounds.

It is all Wonderful. Just one little question. How am I going to survive 3 more days? I'm going to bed!

A trivial index demo

Today I was sitting in a public library, minding my own business when a man who I had never seen before, leaned over to me and asked me, "Is it true that you can drop and index created explicitly, but not one created implicitly as part of a constraint? Initially the reaction was implicit/explicit? Please speak English. When I sorted that out there were 2 further reactions, also unspoken, "Duh, that's pretty obvious", and "Why me?"

"It is an Ancient DBA and he stoppeth one of three."

I demonstrated the matter a bit to him.

Logged on as scott, I create a play table:
SQL> create table tmp(f1 number(4), f2 number(4), f3 number(4));

Table created.

SQL> insert into tmp values (1, 2, 3);

1 row created.

SQL> create index tmp_f2 on tmp(f2);

Index created.

SQL> drop index tmp_f2;

Index dropped.


All straight forward. The index was created 'explicitly' and there is no constraint that it affects if it is dropped, so I can drop it without problem.


Now to add a constraint, and an 'implicit' index.

SQL> alter table tmp modify f2 unique;

Table altered.

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TMP';

CONSTRAINT_NAME
------------------------------
SYS_C0011482

SQL> select index_name from user_indexes where table_name = 'TMP';

INDEX_NAME
------------------------------
SYS_C0011482

The constraint has been created as has an index


SQL> drop index SYS_C0011482;
drop index SYS_C0011482
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

and as expected, the index cannot be dropped since it is used by the constraint.


It is all very trivial.

What did get a little interesting was that I was able to enter multiple rows of nulls in a field with a 'unique' constraint. I need to review the properties of null.

And the real question that was never answered

"Now wherefore stopp'st thou me?"

OOW any free Discover tickets around?

A quick question.

I see that Oracle Open World Discover ticket costs $50. I remember that it cost something in prior years, but that there was some way to get it for free if you signed up by the early bird date. Is there such a thing again this year?

I have seen the 'chance for a full registration', and have entered, but I haven't seen the free 'discover' pass. Is it around?

TYIA

Making problems for myself

Playing around with my toy database I asked myself, "What happens if DUAL has more than 1 row?" I found out.

SQL> insert into dual values ('Y');

1 row created.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*) ---------- 1

I tried it again. Same result. "Oh, I guess I can't insert into DUAL", says I, and I went about my business.

Later I logged on as SCOTT and tried to drop a table. Playing I have more EMP tables than employees.

SQL> DROP TABLE EMP4;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows

WHAT??!!?

Yes it is there and there is only 1 table called EMP4.

SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME = 'EMP4'

OWNER OBJECT_NAME

------------------------------ ------------------------------

SCOTT EMP4

I looked the matter up at orafaq. and followed the instructions.

SQL> select * from dual;

D

-

X

SQL> create table temp_dual as select * from dual;

Table created.

SQL> select * from temp_dual;

D

-

X

Y

Y

Yes, I plead guilty. I DID succeed in inserting those rows into DUAL.

SQL> delete from dual where dummy = 'Y';

1 row deleted.

Strange. It deleted 1 row even though I had put 2 in.

SQL> drop table temp_dual;

drop table temp_dual

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows

I deleted the second excess row:

SQL> delete from dual where dummy = 'Y';

1 row deleted.

and I had a functioning database back.

SQL> DROP TABLE TMP_DUAL;

Table dropped.

and then as SCOTT

SQL> DROP TABLE EMP4;

Table dropped.

OK, I get it, Oracle consults DUAL in the drop process. And don't go messing up a database of any importance. But it is odd how the fact that I was succeeding to mess things up was hidden from me. Yes it told me that I had inserted the row, but then it didn't display it with a select. It was an interesting bit of play.

Lazy Log Writer - Vindication & thank you Fitaloon

The final chapter: The database continued plodding along wretchedly until last Friday night at midnight when I got a call, "The production database is down."
I crawled out of bed, bleary eyed, signed in, and tried to start it. I got:

ALTER DATABASE MOUNT
Sat Oct 11 00:35:05 2008
ORA-00202: control file: '/u01/control02.ctl'
ORA-27037: unable to obtain file status

That woke me up. OK, Who/what messed with the control file?
I tried to look at the file and got a message that told me that not only was the control file inaccessable, the entire drive /u01 was inaccessable.

I called across the great divide to the company who controls the hardware and eventually got, "The backup battery for the write cache needs replacing in the SAN".

Monday morning I resent my email to management from a week and a half before. I highlighted my paraphrasing of Fitaloon's comment to my first posting on this subject, "Could our problem be caused by the write cache on the disk having some sort of problem? For example, could it be something as simple as failed backup batteries for the write cache?" Thank you, Fitaloon. You hit the nail on the head.

Since the replacement of the battery, our log writer has been zipping right along, good performance, at last!