Any benefit in NUSI on "customer_type" column?

Database
N/A

Any benefit in NUSI on "customer_type" column?

Hi,

Is there any point/benefit in creating secondary index on column,

which has low number of distinct values compared to size of the table.

Hypothetical example:

CREATE TABLE CUSTOMER
(
customer_id bigint,
customer_name varchar(10),
customer_type smallint
)
UNIQUE PRIMARY INDEX (customer_id)
INDEX (customer_type) -- Is this index on customer_type worthless?
;
SELECT count(*), count(distinct customer_type) FROM CUSTOMER;

| count(*) | count(distinct customer_type) |
+----------+-------------------------------+
| 10000000 | 200 |

(also lets assume data is distributed equally between customer_types)

Am I right in thinking that such index is kind of pointless?

Tags (1)
1 REPLY
Teradata Employee

Re: Any benefit in NUSI on "customer_type" column?

That NUSI doesn't look selective enough. Nevertheless you can create it, collect statistics on the index, and then do an explain referencing the NUSI. If the parser chooses a FTS over using the NUSI, drop the index.