From Newsgroup: comp.databases.ingres

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

IrCOm not sure if we can partition an index. IrCOm 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

--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

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-a 8 19:24 aaaaanfo.t01
-rw-------. 1 ingres ingres 0 Oct-a 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

IrCOm not sure if we can partition an index. IrCOm 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>

--- Synchronet 3.21b-Linux NewsLink 1.2