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>
>> 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
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-lReceived on Wed Jul 12 2017 - 18:25:16 CEST