0

Elaborate:

CREATE TABLE TEST(
  school_year DATE,
  B VARCHAR2(12),
  C VARCHAR2(15),
  D NUMBER
)

And the table is already partitioned on column school_year. Should column school_year be part of the index definition? The most frequently executed query would be:

WHERE school_year = TO_DATE('06/30/2014', 'MM/dd/yyyy')
and B = '020100001000'
and D = 3

Should the index be:

CREATE INDEX IDX_SAMPLE (SCHOOL_YEAR, B, D)

or

CREATE INDEX IDX_SAMPLE (B, D)
  • Are you creating local indexes? Or global indexes? I assume you've left off the part of the create table statement that specifies the partitioning clause. – Justin Cave Nov 25 '15 at 19:27
  • @JustinCave based on your comment - existing indices are local. LOCAL ( PARTITION SY09, LOCAL ( PARTITION SY10 LOCAL ( PARTITION SY11 etc... – Brian Nov 25 '15 at 19:34
  • OK. So you're creating local indexes. Is school_year reasonably selective within a partition? Or will all rows in the partition have the same value? If you are doing a range partition by month, for example, and your values are distributed throughout the month, that's different than if all rows in the partition will have the same school_year value. – Justin Cave Nov 25 '15 at 19:37
  • All rows in the partition will have the same value (6/30/yyyy). We partition by school year, and we have a partition for 6/30/2006, 6/30/2007, 6.30/2008, .... 6/30/2020. – Brian Nov 25 '15 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.