default
George Joseph, April 03, 2016 - 6:19 pm UTC
Would it be possible to let the database make up that decision of using the degree of parallelism for a query on the basis of what is available?
April 04, 2016 - 1:10 am UTC
maxvalue - bug or feature
Boris, December 14, 2018 - 4:29 pm UTC
I am using oracle 11g R2 version. could you please explain why the partition "p_sg_2006" below picks up the JP data?
Is this bug or feature?
Sample:
create table range_part
(country varchar2(10) not null,
year number not null, data_1 varchar2(20))
partition by range (country, year)
(
partition p_in_2006 values less than ('IN',2007),
partition p_in_2007 values less than ('IN',2008),
partition p_in_2008 values less than ('IN',2009),
partition p_sg_2006 values less than ('SG',2007),
partition p_sg_2007 values less than ('SG',2008),
partition p_sg_2008 values less than ('SG',2009),
partition p_max values less than (MAXVALUE, MAXVALUE)
)
insert into range_part values ('IN',2007,'Row 1');
insert into range_part values ('IN',2008,'Row 2');
insert into range_part values ('JP',2007,'Row 3');
insert into range_part values ('JP',2015,'Row 4');
insert into range_part values ('US',2006,'Row 5');
insert into range_part values ('US',2009,'Row 6');
commit;
select * from range_part partition (p_sg_2006);
JP 2007 Row 3
JP 2015 Row 4
Why query is why the p_sg_2006 captures the JP values?
Thanks in advance for your valuable service to the oracle community.
December 17, 2018 - 2:24 am UTC
Multi-column range partitions are not a "matrix" or "multi-dimensional" concept but a tie-breaker concept.
So when you insert say: IN,2009 the database says:
"OK, where does IN go. Is there an *exact* partition that it belongs in?"
The answer is No...so then we look at the next column.
"OK, I've narrowed it down to 3 possible partitions. What does the 2009 tell me? Ah, that means, p_in_2008"
So in your example - you insert JP,2007 (or JP anything).
"OK, where does JP go. Is there an *exact* partition that it belongs in?"
"Yes...JP is more than IN and less than SG, so it goes in p_sg_2006"
Hope that makes sense.
max value confusion
Boris, December 17, 2018 - 4:38 pm UTC
Thanks Connor for your response. I am bit confused after reading this
So in your example - you insert JP,2007 (or JP anything).
"OK, where does JP go. Is there an *exact* partition that it belongs in?"
"Yes...JP is more than IN and less than SG, so it goes in p_sg_2006"
My understanding is that when we insert JP,2007, oracle will look for the country code='JP'(left most column match) and then look for the year ranges with the JP country code based
partition. on the top, there is no partition defined for the country code 'JP. Technically JP,2007 would have been inserted into p_max partition.
Could you please expand how "JP is more than IN and less than SG"? Thanks for your time and patience in educating the oracle concepts.
December 18, 2018 - 2:08 am UTC
*Conceptually* I like to think about the multi-columns as a concatenated value - sort of like my partitions are equivalent to:
1) values less than IN2007
2) values less than IN2008
3) values less than IN2000
4) values less than SG2007
So when I give you the value JP1234, I can see that it sorts higher than 3 and lower than 4, so it goes in partition 3.
Unreiliable
A reader, December 18, 2018 - 6:39 am UTC
And what thé hell post About partitions la To do with "Max value to be used in a parallel hint" ?
December 19, 2018 - 1:48 am UTC
Agreed. We should have rejected this one and forced a new question.
But man, one star is a bit harsh :-)