Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

DB_INDEX

Represents information of indexes created for the class for which the current user has access authorization in the 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.

Definition

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'));

Example

The following is an example of retrieving 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'