This discussion is archived
7 Replies Latest reply: Nov 29, 2012 1:36 PM by rp0428 RSS

Index of partition

652398 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Explorer
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    Yes I did - thanks for the correction.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points