Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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

INDEX_CARDINALITY Function

Description

The INDEX_CARDINALITY function returns the index cardinality in a table. The index cardinality is the number of unique values defining the index. The index cardinality can be applied even to the partial key of the multiple column index and displays the number of the unique value for the partial key by specifying the column location with the third parameter.

The return value is 0 or a positive integer and if any of the input parameters is NULL, NULL will be returned. If tables or indexes that are input parameters are not found, or key_pos is out of range, NULL will be returned.

For the table and the index names which are the first and the second input parameters, they cannot be passed as NCHAR or VARNCHAR types.

Syntax

INDEX_CARDINALITY(table, index, key_pos)

  • table : Table name
  • index : Index name that exists in the table
  • key_pos : Partial key location key_pos starts from 0 and has a range that is smaller than the number of columns that composes the key. That is, the key_pos of the first column is 0. For the single column index, it is 0. It can be one of the following types.
    • Character string that can be converted to a numeric type. NCHAR and VARNCHAR are not supported.
    • Numeric type that can be converted to an integer type. The FLOAT or the DOUBLE types will be the value converted by the ROUND function.
Example

CREATE TABLE t1( i1 INTEGER ,

i2 INTEGER not null,

i3 INTEGER unique,

s1 VARCHAR(10),

s2 VARCHAR(10),

s3 VARCHAR(10) UNIQUE);

                  

CREATE INDEX i_t1_i1 ON t1(i1 DESC);

CREATE INDEX i_t1_s1 ON t1(s1(7));

CREATE INDEX i_t1_i1_s1 on t1(i1,s1);

CREATE UNIQUE INDEX i_t1_i2_s2 ON t1(i2,s2);

 

INSERT INTO t1 VALUES (1,1,1,'abc','abc','abc');

INSERT INTO t1 VALUES (2,2,2,'zabc','zabc','zabc');

INSERT INTO t1 VALUES (2,3,3,'+abc','+abc','+abc');

 

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',0);

   index_cardinality('t1', 'i_t1_i1_s1', 0)

===========================================

                                          2

 

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',1);

   index_cardinality('t1', 'i_t1_i1_s1', 1)

===========================================

                                          3

 

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',2);

   index_cardinality('t1', 'i_t1_i1_s1', 2)

===========================================

                                       NULL

 

SELECT INDEX_CARDINALITY('t123','i_t1_i1_s1',1);

  index_cardinality('t123', 'i_t1_i1_s1', 1)

=============================================

                                         NULL