[Info-ingres] index problem on a partitioned table

[Karl Schendel]

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