Represents information of indexes created for the class for which the current user has access authorization to a database.
Attribute Name |
Data Type |
Description |
---|---|---|
index_name |
VARCHAR(255) |
Index name |
is_unique |
VARCHAR(3) |
‘YES’ for a unique index, and ‘NO’ otherwise. |
is_reverse |
VARCHAR(3) |
'YES’ for a reversed index, and ‘NO’ otherwise. |
class_name |
VARCHAR(255) |
Name of the class to which the index belongs |
key_count |
INTEGER |
The number of attributes that comprise the key |
is_primary_key |
VARCHAR(3) |
'YES' for a primary key, and ‘NO’ otherwise. |
is_foreign_key |
VARCHAR(3) |
'YES' for a foreign key, and ‘NO’ otherwise. |
CREATE VCLASS db_index (index_name, is_unique, is_reverse, class_name, key_count, is_primary_key, is_foreign_key)
AS
SELECT i.index_name, CASE WHEN i.is_unique = 0 THEN 'NO' ELSE 'YES' END,
CASE WHEN i.is_reverse = 0 THEN 'NO' ELSE 'YES' END, i.class_of.class_name, i.key_count, CASE WHEN i.is_primary_key = 0 THEN 'NO' ELSE 'YES' END, CASE WHEN i.is_foreign_key = 0 THEN 'NO' ELSE 'YES' END
FROM _db_index i
WHERE (CURRENT_USER = 'DBA' OR
{i.class_of.owner.name} subseteq (
SELECT set{CURRENT_USER} + coalesce(sum(set{t.g.name}), set{})
from db_user u, table(groups) as t(g)
where u.name = CURRENT_USER ) OR
{i.class_of} subseteq (
SELECT sum(set{au.class_of})
FROM _db_auth au
WHERE {au.grantee.name} subseteq (
SELECT set{CURRENT_USER} + coalesce(sum(set{t.g.name}), set{})
from db_user u, table(groups) as t(g)
where u.name = CURRENT_USER ) AND
au.auth_type = 'SELECT'));
The following example shows how to retrieve index information of the class.
SELECT class_name, index_name, is_unique
FROM db_index
ORDER BY 1;
class_name index_name is_unique
==================================================================
'athlete' 'pk_athlete_code' 'YES'
'city' 'pk_city_city_name' 'YES'
'db_serial' 'pk_db_serial_name' 'YES'
'db_user' 'i_db_user_name' 'NO'
'event' 'pk_event_code' 'YES'
'female_event' 'pk_event_code' 'YES'
'game' 'pk_game_host_year_event_code_athlete_code' 'YES'
'game' 'fk_game_event_code' 'NO'
'game' 'fk_game_athlete_code' 'NO'
'history' 'pk_history_event_code_athlete' 'YES'
'nation' 'pk_nation_code' 'YES'
'olympic' 'pk_olympic_host_year' 'YES'
'participant' 'pk_participant_host_year_nation_code' 'YES'
'participant' 'fk_participant_host_year' 'NO'
'participant' 'fk_participant_nation_code' 'NO'
'record' 'pk_record_host_year_event_code_athlete_code_medal' 'YES'
'stadium' 'pk_stadium_code' 'YES'