[Info-ingres] index problem on a partitioned table

[Martin Bowes]

Multiple locations wouldn't have altered the number of pages available. ... I think.

I managed to adjust the page_size of the index to 16K and that has given me back a few million pages which is enough to continue for a while.

I suspect my best answer is to convert this table to an x100 table. And alter a few programs to modify to combine. Sadly it also means I have to unload, recreate with x100 and reload this old database.

Something to look forward too.

Marty 

-----Original Message-----
From: Paul White <shift7solutions at gmail.com> 
Sent: 08 October 2021 15:39
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] index problem on a partitioned table

Hi there Karl,

I suggested to Marty earlier he may like to try to create the index with multiple locations.

I created two locations pointing to the same path. It seems to work:


create index p1_idx on p1 (v)
with structure=btree,
page_size = 8192,
location=(ii_database, ii_database2, ii_database3)


ls -l /data4/ingresII/ingres/data/default/pw
total 0
-rw-------. 1 ingres ingres 0 Oct  8 19:24 aaaaanfo.t01 -rw-------. 1 ingres ingres 0 Oct  8 19:24 aaaaanfo.t02


Paul



On 8/10/2021 11:24 pm, Karl Schendel wrote:
> You can't partition an index.  That was a feature in the original 
> partitioned tables spec, but never got implemented.  Part of the 
> reason is that a partitioned index would require three identifiying numbers: base table, index, partition, and we only have two.
> Adding a number, or somehow rejiggering the numbering scheme, would be 
> a massive change throughout DMF and other places.  The can has been 
> kicked down the road ever since.
>
> I'm not sure what exactly to suggest in this case.  You might have to 
> split up the base table, or do a manual pseudo-index that's explicitly referenced in queries.
>
> Karl
>
>
>> On Oct 8, 2021, at 3:28 AM, Martin Bowes <martin.bowes at ndph.ox.ac.uk> wrote:
>>
>> Hi All,
>>   
>> II 11.1.0 (a64.lnx/100) +p15599
>>   
>> I have a partitioned table (pidapp) with an index (pidapp_pid). The table is fine, but the index is now bumping into max pages.
>>   
>> BBA_CTSU_OX_AC_UK ::[42297             , 11346     ,  0000000000000000:00007faf22db5ac0, dm1p.c:4471           ]: Thu Oct  7 18:39:18 2021 E_DM92BF_DM1P_TABLE_TOO_BIG  Error extending table past maximum table size of 8388608, Database ace_trexy_live,  Table ace.pidapp_pid, Current size 8388596 pages, Extend size 16 pages
>>   
>> The index looks like this:
>> ame:                 pidapp_pid
>> Owner:                ace
>> Created:              20/02/2020 13:41:32
>> Location:             ii_database
>> Type:                 secondary index on pidapp
>> Version:              II10.0
>> Page size:            8192
>> Cache priority:       0
>> Alter table version:  0
>> Alter table totwidth: 12
>> Row width:            12
>> Number of rows:       1124378214
>> Storage structure:    btree
>> Compression:          none
>> Duplicate Rows:       allowed
>> Number of pages:      7272402
>> Overflow data pages:  0
>> Journaling:           enabled if journaling on the base table is enabled
>> Base table for view:  no
>> Permissions:          none
>> Integrities:          none
>> Optimizer statistics: will use any existing statistics on the base 
>> table
>>   
>> Index Column Information:
>>                                                                    Key
>> Column Name              Type               Length Nulls Defaults Seq
>> pid                      integer                 4   no      no     1
>> tidp                     integer                 8   no      no     2
>>   
>> I’m not sure if we can partition an index. I’m not sure if bumping the index to a higher page size will help either as the data is so small we can easily fit the max number of tids per page into 8k as it is.
>>   
>> Ideas?
>>   
>> Martin Bowes
>> _______________________________________________
>> Info-ingres mailing list
>> Info-ingres at lists.planetingres.org
>> https://lists.planetingres.org/mailman/listinfo/info-ingres
> _______________________________________________
> Info-ingres mailing list
> Info-ingres at lists.planetingres.org
> https://lists.planetingres.org/mailman/listinfo/info-ingres

--
Paul White<br>
Shift Seven Solutions<br>
<b>m: 0414681799</b><br>
p: 0754482137<br>
e: paul.white at shift7solutions.com.au<br>
w: https://www.shift7solutions.com.au<br>
International: +61414681799<br>

_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org
https://lists.planetingres.org/mailman/listinfo/info-ingres