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@gmail.com>
Sent: 08 October 2021 15:39
To: info-ingres@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@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@lists.planetingres.org
>> https://lists.planetingres.org/mailman/listinfo/info-ingres
> _______________________________________________
> Info-ingres mailing list
> Info-ingres@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@shift7solutions.com.au<br>
w: https://www.shift7solutions.com.au<br>
International: +61414681799<br>
_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org
https://lists.planetingres.org/mailman/listinfo/info-ingres