Forum Stats

  • 3,816,536 Users
  • 2,259,202 Discussions
  • 7,893,507 Comments

Discussions

Categories

Create Global Index on Interval partitioned tables

User_NY02I
User_NY02I Member Posts: 356 Blue Ribbon
edited Dec 2, 2021 10:05AM in General Database Discussions

Hi All,

I am looking for ways to create a Global Index on Interval partitioned table I have looked up over internet but did not found any helpful document about it.

Below is the partitioning Key which we have defined on table.


PARTITION BY RANGE ("DATE_SITU") INTERVAL (NUMTODSINTERVAL(1,'DAY')) 

 (PARTITION "PART_10_OCT_2014_MVT_HBFR_H" VALUES LESS THAN (TO_DATE(' 2014-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))


and the table has more than at present 57 partitions on it.

I am doing this activity in order to check the tuning option for refresh of a MVIEW as it is taking time and DBA suggested me to try Global index.

Please let me know if more information is required from me.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    I am looking for ways to create a Global Index on Interval partitioned table

    For global index do not specify the keyword LOCAL. For global partitioned index also specify partition clause.

    I have looked up over internet but did not found any helpful document about it.

    docs.oracle.com SQL Language Reference for your version/CREATE INDEX

    · Share on TwitterShare on Facebook
  • User_NY02I
    User_NY02I Member Posts: 356 Blue Ribbon

    I tried below but it gives error.


    CREATE INDEX idx_datesitu_global ON CRAFT_MVT_HBFR_H (DATE_SITU)

    GLOBAL PARTITION BY RANGE ("DATE_SITU") INTERVAL (NUMTODSINTERVAL(1,'DAY')) 

     (PARTITION "PART_10_OCT_2014_MVT_HBFR_H" VALUES LESS THAN (TO_DATE(' 2014-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    edited Dec 2, 2021 3:20PM

    The documentation does not assume an interval keyword for indexes.

    изображение.png

    The global partitioning of single column index has meaning only for index fast full scan. You can create partitions manually and check the performance gain.

    Why do you need to create global partitions the same as the table??

    · Share on TwitterShare on Facebook
  • User_NY02I
    User_NY02I Member Posts: 356 Blue Ribbon

    Why do you need to create global partitions the same as the table??

    I want to create global as the partitioning of table is based on each day and the query on this table look for data in atleast for a month i.e. the query would look into 30 partitions.

    the partitions are created automatically each day based on intervals i.e. why i want to use same as table otherwise in real scenario how will i add new created partitions in Global Index ?

    · Share on TwitterShare on Facebook