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 is returned. If tables or indexes that are input parameters are not found, or key_pos is out of range, NULL is 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)

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