7 Replies Latest reply: Nov 29, 2012 3:36 PM by rp0428 RSS

    Index of partition

    652398
      A table is a partition table as a quarter. The result of using "SELECT * FROM USER_PART_INDEXES;" is:
      INDEX_NAME TABLE_NAME PARTITIONING_TYPE
      C_AIRDATEINDEX     T_C     RANGE
      C_IDX1     T_C     RANGE
      C_IDX2     T_C     RANGE
      C_IDX3     T_C     RANGE
      C_IDX4     T_C     RANGE

      My question is: Which type of these indexes "C_AIRDATEINDEX、C_IDX1、C_IDX2、C_IDX3、C_IDX4"
      1. Non-partition index in partition.
      2. Partition index in partition.
      3. Local index.

      If using:
      create index C_IDX1 on T_C (A);
      create index C_IDX2 on T_C (B);
      create index C_IDX3 on T_C (C);
      create index C_IDX4 on T_C (D);

      Which type of these indexes:
      1. Non-partition index in partition.
      2. Partition index in partition.
      3. Local index.

      Thanks a lot.
        • 1. Re: Index of partition
          Chanchal Wankhade
          Hi,

          I think this may solve your doubt..


          Example of a Local Index Creation
          CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;
          Example of a Global Index Creation
          CREATE INDEX employees_global_idx ON employees(employee_id);
          Example of a Global Partitioned Index Creation
          CREATE INDEX employees_global_part_idx ON employees(employee_id)
          GLOBAL PARTITION BY RANGE(employee_id)
          (PARTITION p1 VALUES LESS THAN(5000),
           PARTITION p2 VALUES LESS THAN(MAXVALUE));
          Example of a Partitioned Index-Organized Table Creation
          CREATE TABLE sales_range
          (
          salesman_id   NUMBER(5), 
          salesman_name VARCHAR2(30), 
          sales_amount  NUMBER(10), 
          sales_date    DATE, 
          PRIMARY KEY(sales_date, salesman_id)) 
          ORGANIZATION INDEX INCLUDING salesman_id 
          OVERFLOW TABLESPACE tabsp_overflow 
          PARTITION BY RANGE(sales_date)
          (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
           OVERFLOW TABLESPACE p1_overflow, 
           PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
           OVERFLOW TABLESPACE p2_overflow, 
           PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
           OVERFLOW TABLESPACE p3_overflow, 
           PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
           OVERFLOW TABLESPACE p4_overflow);
          • 2. Re: Index of partition
            rp0428
            >
            A table is a partition table as a quarter. The result of using "SELECT * FROM USER_PART_INDEXES;" is:
            INDEX_NAME TABLE_NAME PARTITIONING_TYPE
            C_AIRDATEINDEX T_C RANGE
            C_IDX1 T_C RANGE
            C_IDX2 T_C RANGE
            C_IDX3 T_C RANGE
            C_IDX4 T_C RANGE

            My question is: Which type of these indexes "C_AIRDATEINDEX、C_IDX1、C_IDX2、C_IDX3、C_IDX4"
            1. Non-partition index in partition.
            2. Partition index in partition.
            3. Local index.
            >
            You have the information to answer your own question but you didn't post it.

            1. Non-partitioned indexes are only listed in the xxx_INDEXES view. Only partitioned indexes are listed in the xxx_PART_INDEXES view.
            2. Only partitioned indexes are listed in the xxx_PART_INDEXES view
            3. If you look at the LOCALITY column of USER_PART_INDEXES it will be 'LOCAL' if it is a local partitioned index.

            See ALL_INDEXES in the Database reference
            http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1096.htm
            >
            ALL_INDEXES describes the indexes on the tables accessible to the current user.
            >

            See ALL_PART_INDEXES in the same doc
            http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2003.htm
            >
            ALL_PART_INDEXES displays the object-level partitioning information for the partitioned indexes accessible to the current user.
            • 3. Re: Index of partition
              652398
              Thanks. I saw the SQL of indexes from PLSQL Developer GUI (By viewing the table and its SQL):
              create index C_IDX1 on T_C (A);
              create index C_IDX2 on T_C (B);
              create index C_IDX3 on T_C (C);
              create index C_IDX4 on T_C (D);

              However, these indexes is in "ALL_PART_INDEXES" and "USER_INDEXES". As you said, it should be a Global index not a partition index from SQL?
              • 4. Re: Index of partition
                652398
                Thanks. I saw the SQL of indexes from PLSQL Developer GUI (By viewing the table and its SQL):
                create index C_IDX1 on T_C (A);
                create index C_IDX2 on T_C (B);
                create index C_IDX3 on T_C (C);
                create index C_IDX4 on T_C (D);

                However, these indexes is in "ALL_PART_INDEXES" and "USER_INDEXES". Does it should be a partition index not a global index from SQL?
                • 5. Re: Index of partition
                  rp0428
                  >
                  However, these indexes is in "ALL_PART_INDEXES" and "USER_INDEXES". Does it should be a partition index not a global index from SQL?
                  >
                  I have no idea what that means or what question you are asking.

                  1. ALL_PART_INDEXES only shows indexes that are partitioned.
                  2. ALL_INDEXES only shows indexes that are NOT partitioned.
                  3. A LOCAL index is ALWAYS partitioned - that is implied by the word 'LOCAL'
                  4. A GLOBAL index may or may not be partitioned.
                  • 6. Re: Index of partition
                    Max Seleznev
                    >
                    2. ALL_INDEXES only shows indexes that are NOT partitioned.
                    >

                    I'm sure you meant to say it shows both partitioned and non-partitioned indexes based on your previous post. The PARTITIONED column indicates which is which.

                    Besides there're DBA/ALL_IND_[SUB]PARTITIONS DBA/ALL_TAB_[SUB]PARTITIONS that provide further information.
                    • 7. Re: Index of partition
                      rp0428
                      Yes I did - thanks for the correction.