Re: Request for feedback: Oracle index interval range partitioning

From: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk>
Date: Wed, 12 Jul 2017 18:25:16 +0200
Message-Id: <072BB1ED-7681-4C73-A07B-F631882BB079_at_strychnine.co.uk>


I must be having a senior moment. With my hand on a bible, I swear I tested this at length earlier and this was not my observation. It is clearly time to call it a day today. Thank you to both you and Raj.
Mike

> On 12 Jul 2017, at 18:16, contact_at_soocs.de wrote:
> 
> Hello Mike,
> i may not get your point but it works with 11.2.0.3?
> 
> --------------8<---------------
> CREATE TABLE foo (
>  id           NUMBER,
>  created_date DATE
> )
> PARTITION BY RANGE (created_date)
> INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
> (
>   PARTITION init01 values LESS THAN (TO_DATE('01-01-1900','DD-MM-YYYY'))
> );
> 
> CREATE INDEX local_i_created_date ON foo (created_date) LOCAL;
> 
> INSERT INTO foo VALUES (1,SYSDATE);
> COMMIT;
> --------------8<---------------
> 
> Best Regards
> Stefan Koehler
> 
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de
> Twitter: _at_OracleSK
> 

>> Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk> hat am 12. Juli 2017 um 15:03 geschrieben:
>>
>> Hi, HPUX 11.2.0.3.0
>>
>> I have many 0.5billion row range partitioned (date) with a one month "interval" tables, so
>>
>> create table foo
>> (
>> loads of columns
>> ) ......
>> ......
>> tablespace "baa"
>> partition by range(dateCOlumn)
>> interval (numtoyminterval(1,'MONTH'))
>> (partition initial values less than (to_date('01-01-1900','DD-MM-YYYY'))
>> );
>>
>> I am considering index partitioning too, however index interval partitioning appears to not be supported in 11.2.
>>
>> Partitions will be scheduled to be dropped periodically and I need to concern myself with efficient index rebuilding/dropping.
>>
>> Has anyone found themselves in this Oracle interval partitioning supported for tables but not the underlying indexes dilemma before and, if so, how you approached the problem?
>>
>> All feedback appreciated.
>>
>> Mike
>>
>> —
>>
>> Michael D O'Shea
>> Woodward Informatics Ltd, http://www.strychnine.co.uk
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 12 2017 - 18:25:16 CEST

Original text of this message