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

["Mike Matrigali (JIRA)" <ji...@apache.org>]

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

                 Key: DERBY-3747
                 URL: https://issues.apache.org/jira/browse/DERBY-3747
             Project: Derby
          Issue Type: Improvement
          Components: SQL, Store
    Affects Versions: 10.4.1.3
            Reporter: Mike Matrigali
            Priority: Minor


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).

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.