Naming Primary Indexes

Analytics

Naming Primary Indexes

I need some helpe to name primary indexes.

select column1, column2, column3
from table name
with data
primary index (column1, column2, column3) How do I name this index

If I only insert into this table and collect statistics on the index name, is the table going to be skewd or is there a definition stored?

4 REPLIES

Re: Naming Primary Indexes

You can name your primary index as follows in table definitions itself

create table tablename
(
....
....
)
primary index index name ( column list)

You can collect statistics on Index as well as column list of primary index.Collection of statistics does not contribute to data skew.It just gathers statistical information about the values stored in the index/columns on which statistics are collected.

Re: Naming Primary Indexes

Thanks for your help.

Is it safe to assume that if I never drop table and only insert into it, I can just collect statistics on the index?

Collecting statistics on index will increase performance if using the index (seek or scan) correct?

Re: Naming Primary Indexes

Yes, you can just collect statistics on table whenever the table size grows.It is recommended to collect stats whenever there is 10% or more increase in table size(rows).

Collection of stats always helpful in improving query performance provided they are updated periodically based on table growth

Re: Naming Primary Indexes

Also some times you would find it helpful to collect stats on columns which are not part of indices, but are used in joins, where clause etc. as well as the special column PARTITION in case of PPI.

Again there's no single pill ;-) , but these are some good points to start with.