[DERBY-3747] provide way to implement primary key table as single btree rather than separate btree/heap table.

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.4.1.3
• Component/s: SQL, Store
• Labels: derby_triage10_10
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Mike Matrigali
• Created: 01/Jul/08 17:47
• Updated: 30/Sep/12 02:42

Description

Derby currently always stores base tables in a heap table, and then all indexing is provided by separate indexes on top of the base table.
This architecture fits well with tables with small keys relative to the indexed data and for tables that want multiple indexes on top of a single table.
But for cases where only a single index is required the overhead could be removed. One option would be to just go ahead and store the whole
table in the btree and have no heap at all. The following issues would have to be resolved:

1) current btree does not support keyed/unkeyed fields. The infrastructure is there but just not used. This project could be worked on independently
and would provide benefit to existing users. It would allow more efficient "covered" indexes where only part of the covered columns need be
be indexed. This would also solve the problem that currently there are some types which are not comparable and thus can't be put into
a btree.
2) current btree limits the size of entries, there is no "overflow" concept as is supported in base tables. An overflowed key is going to be a serious
performance issue, but would seem reasonable to support overflow non-keyed fields.
3) current locking strategy is "data-only" locking that uses the rowid of the heap row as the invarient key associated with a row in the database. Some
substitute would have to be created - any unique id generator stored as part of the row in the btree in place of the row location should work.
4) To support another index on such a "index base table" is a problem as the rows in the btree will move. So secondary indexes would have to do
btree searches of the index base table to find a row. The secondary index would have to store the key columns of the index base table in addition
to any key column's of the secondary index.
5) changes to language to build such an index base table and to recognize and use such an index, including understanding that there is no base
table (or maybe it is a base table and changes need to happen to access it differently than other "base" tables).