Is it possible to setup local index on a sybset of partitions on a single partitioned table

[james_morrison]

We have a table with range partition on date (create_date). Each partition holds 1 months worth of data. Data is loaded throughout day , each day. By end of the month partition holds up to 30 million rows. The range partition column is not indexed. New requirements require create_date as leading index. Apps requiring this data are concerned only with current data -- queries not going back more than a month . Can I create local indexes on subset of partitions -- data in current partition and future partitions?

[jong-un_seo · Accepted solution]

Hi James,

Yes, you can do it.

Please use partial index clause of create index command.

1> create table tab1(a int, b varchar(100) null, cr_date date null)
2> partition by range (cr_date)
3> (YMW1848 values <= ('2018/12/01'),
4> YMW1850 values <= ('2018/12/08'))
5> go

1> sp_helpartition tab1
2> go
name type partition_type partitions partition_keys
---- ---------- -------------- ---------- --------------
tab1 base table range 2 cr_date
(1 row affected)

partition_name partition_id compression_level pages row_count segment create_date
-------------- ------------ ----------------- ----- --------- ------- -------------------
YMW1848 1376004902 none 1 0 default Dec 5 2018 7:23AM
YMW1850 1392004959 none 1 0 default Dec 5 2018 7:23AM

Partition_Conditions
------------------------
VALUES <= ('2018/12/01')
VALUES <= ('2018/12/08')

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
----------- ----------- ----------- --------------------------- ---------------------------
1 1 1 1.000000 1.000000 (return status = 0)

1> create nonclustered index tab1_idx on tab1 (a) local index
2> go

1> create nonclustered index tab1_idx2 on tab1 (cr_date) partial index tab1_idx2_part for YMW1850
2> go

1> sp_helpindex tab1
2> go
Object has the following indexes

index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local
---------- ---------- ----------------- ----------------------- ---------------- -------------------- ------------------- -------------
tab1_idx a nonclustered 0 0 0 Dec 5 2018 7:24AM Local Index
tab1_idx2 cr_date nonclustered 0 0 0 Dec 5 2018 7:30AM Partial Index

(2 rows affected)
index_ptn_name index_ptn_seg index_ptn_comp
------------------- ------------- ------------------
tab1_idx_1424005073 default inherit from index
tab1_idx_1440005130 default inherit from index
tab1_idx2_part default inherit from index

(3 rows affected)
(return status = 0)

Best regards,
Jongun