create table P_LIST_HASH
partition by hash(USERID)
SUBPARTITION BY list (divindicator)
subPARTITION hp1_p1 VALUES ( 0 ),
subPARTITION hp1_p2 VALUES ( 1 ),
subPARTITION hp1_p3 VALUES ( 2 )
subPARTITION hp2_p1 VALUES ( 0 ),
subPARTITION hp2_p2 VALUES ( 1 ),
subPARTITION hp2_p3 VALUES ( 2 )
subPARTITION hp3_p1 VALUES ( 0 ),
subPARTITION hp3_p2 VALUES ( 1 ),
subPARTITION hp3_p3 VALUES ( 2 )
as select * from GL_USERS
I tried to remove the partitions 4 then i execute it, we are getting the below error.
In GL_USERS total 22M data exists.
ORA-14400: inserted partition key does not map to any partition
Yor partitioning key is not mapped with values in GL_USERS..
check the values for userid and divindicator in GL_USERS table and then create partition.. it should cover all the values the in the table..
values in the table and the your partition values not matching..
I don't think oracle offers HASH-LIST composite partition. For a composite partition the parent partition should be RANGE or LIST and can't be HASH.
I have not checked the document yet, but you can verify the same in Oracle document.
Well, your original statement failed because you told it to create four partitions, but only listed three of them. And your statement is failing now because you've told it to subpartition based on divindicator being either 0, 1 or 2 and
(presumably) there are rows in your table where this column has some other value.
list/hash is a valid option, but hash/list is not.
For the OP a google search on "oracle composite partition options" produced the following link near the top. (The top link was a paid one for a company selling Office (no, not MS-Office) Partitioning - which rather missed the point).
P.S. As a related topic, the OP might like to take note ofe the "subpartition template" clause which allows one to avoid writing a repetitive subpartition clause.
You are right. In db table GL_USERS, having another value divindicator 3, I added in the partition table and able to create hash-list partition successfully.
Thanks for pointing the exact error.
Thanks for the link.
Even Tom makes mistakes very occasionally - but there's no arguing with a working example
I had a syntax error in the statement I wrote to confirm the document - and didn't notice it because I was expecting the statement to fail anyway.
If you're not worried about naming, here's a (corrected) example using the template syntax:
create table t1
partition by hash(object_name)
subpartition by list(object_type)
subpartition template (
subpartition sp1 values ('TABLE'),
subpartition sp2 values ('INDEX'),
subpartition sp3 values (default)
select object_name, object_type, created
It's a little quirk of the unnamed hash partitions that the sp1, sp2, sp3 that would normally be attached to the partition name also disappear and the subpartition names include sequence numbers.
I am unable to create composite list hash partition using CTAS.
Although you marked this correct I would like to know what use case you have where a hash-range partitioning scheme will actually be of any benefit?
It can't be for data management/archiving since a hash parittioning will have a range of values.
Can you provide info about your use case?
Can you tell us WHY you have chosen hash-range partitioning? I would like to see some test results that show that scheme will actually benefit your use case.