Bug #4848 Indexes for primary keys are not reflected in domain.indexes
Submitted: 1 Aug 2004 18:05 Modified: 4 Jan 2005 12:03
Reporter: Stefan Proels Email Updates:
Status: Not a Bug Impact on me:
Category:MaxDB Severity:S3 (Non-critical)
Version:7.5.0 OS:Linux (Linux)
Assigned to: Ulf Wendel CPU Architecture:Any

[1 Aug 2004 18:05] Stefan Proels
Indexes automatically created for primary keys are not reflected in the system table DOMAIN.INDEXES. My problem is actually that they are not retured by the JDBC driver's getIndexInfo() but I think this is the same problem.

How to repeat:
create table test (x integer primary key)

select * from domain.indexes
-- nothing returned

-- check if an index for the PK really has been created:
create index pk_test on test (x) 
* -7055: Column(s) already indexed:PK_TEST SQLSTATE: I7055

Suggested fix:
Make those indexes visible the domain.indexes
[3 Jan 2005 15:20] Ulf Wendel
Hello Stefan,

I belive this is not a bug. You're mixing up the concept of primary keys and indexes. 

Primary keys and indexes have different semantics. Primary keys are unique identifiers for rows in a database table. Indexes are kind of hints for the database system. Indexes are auxillary structures used to speed up lookup operations. 

The system table domain.indexes contains all indexes. Primary keys are not indexes.

When people talk about primary keys they use the picture of implicitly created indexes. Indexes in the sense of that sentence are search structures. And yes, MaxDB creates a auxillary search structure to speed up lookup operations for every primary key. But this is not an index in the sence of database theory. The structure that gets created differs from indexes. Indexes in a table that contains a primary key are implemented as pointers to the primary key structure.

Back to you JDBC problem. Please use getPrimaryKeys().

[3 Jan 2005 22:21] Stefan Proels
Of course, primary keys and indexes are different concepts, but indexes are used to efficiently implement primary keys. Their internal structure may well be different from ordinary indexes, usually indexes created for primary keys are clustered while ordinary indexes are not. Nevertheless there are indexes.

While it would theoretically be possible, though very inefficient, to implement primary keys without indexes, MaxDB does not do so, as it clearly states that the columns are already indexes when you try to create an ordinary index on it (see error message above).

All database systems I know list the search structures created for primary keys as indexes in the system tables. If you insist of not doing so, the error message above is a bug.
[4 Jan 2005 12:03] Ulf Wendel

I've contacted the developers and they agreed on the following answer I've prepared for you:

MaxDB does not consider primary keys as indexes, because they are different concepts. That's why MaxDB does not show them in DOMAIN.INDEXES nor in getIndexInfo(). MySQL 5.0 for example returns the primary keys on getIndexInfo() but that's a matter of definition by the vendor. Please use getPrimaryKeys() instead, we won't change the
implementation of getIndexInfo(). 

You're correct: the error message needs some improvement. We'll take care of that. A PTS (Problem Tracking Message) has been created and we try to fix it in 7.6. Once it has been synchronized with the internal systems, you should be able to track the progress on http://www.sapdb.org/webpts . The PTS number is 1133220 (Advanced Search => Number).

Best Regards,