Represents the key 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 |
class_name |
VARCHAR(255) |
Name of the class to which the index belongs |
key_attr_name |
VARCHAR(255) |
Name of attributes that comprise the key |
key_order |
INTEGER |
Order of attributes in the key. Begins with 0. |
asc_desc |
VARCHAR(4) |
'DESC' if the order of attribute values is descending, and 'ASC' otherwise. |
key_prefix_length |
INTEGER |
Length of prefix to be used as a key |
CREATE VCLASS db_index_key (index_name, class_name, key_attr_name, key_order, key_prefix_length)
AS
SELECT k.index_of.index_name, k.index_of.class_of.class_name, k.key_attr_name, k.key_order
CASE k.asc_desc
WHEN 0 THEN 'ASC'
WHEN 1 THEN 'DESC' ELSE 'UNKN' END,
k.key_prefix_length
FROM _db_index_key k
WHERE (CURRENT_USER = 'DBA' OR
{k.index_of.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 {k.index_of.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 key information of the class.
SELECT class_name, key_attr_name, index_name
FROM db_index_key
ORDER BY class_name, key_order;
'athlete' 'code' 'pk_athlete_code'
'city' 'city_name' 'pk_city_city_name'
'db_serial' 'name' 'pk_db_serial_name'
'db_user' 'name' 'i_db_user_name'
'event' 'code' 'pk_event_code'
'female_event' 'code' 'pk_event_code'
'game' 'host_year' 'pk_game_host_year_event_code_athlete_code'
'game' 'event_code' 'fk_game_event_code'
'game' 'athlete_code' 'fk_game_athlete_code'
...