This discussion is archived
9 Replies Latest reply: Jul 19, 2012 4:55 AM by Nicosa RSS

how to create index on a single parition

user8731258 Newbie
Currently Being Moderated
Hi,
I have a table which has got 7 partitons,I want to index only one partition how do it?
i am using oracle 11g.Also i want to know if it is possible to create an index within a o partiotn only fr some values..

Edited by: user8731258 on Jul 13, 2012 12:35 AM
  • 1. Re: how to create index on a single parition
    Karthick_Arp Guru
    Currently Being Moderated
    user8731258 wrote:
    Hi,
    I have a table which has got 7 partitons,I want to index only one partition how do it?
    i am using oracle 11g.
    May be create a local index and make the index on 6 partition unusable. But why do you want to do this?
  • 2. Re: how to create index on a single parition
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user8731258 wrote:

    I have a table which has got 7 partitons,I want to index only one partition how do it?
    Not possible. An index exists against a table. For a partitioned table, that index can be "global" (single physical index) or "local" (physical index per partition). But the index definition is for the table - not for a "specific piece" of the table.
    i am using oracle 11g.Also i want to know if it is possible to create an index within a o partiotn only fr some values..
    Does not make sense. The CBO needs to figure out the best execution plan for a SQL statement. It cannot do that effectively if some parts of the physical table differs from other parts.

    Simple example. The table is partitioned on year. For the 2005 partition, you want an index on product code - but not on the other partitions. You issue the following SQL statement:
    select sum(qty_sold) from my_table where year = :yearVar and product_code = :productVar

    The CBO does not know what year you will provide as bind value. So it does not know what partition will be used. It does not know whether it can use the 2005 partition's product code index - an index that does not exist on the other partitions.

    If it does decide to create an execution plan using this index, the execution plan will throw an error when you pass a non-2005 year as bind value.

    If it does not use the index in the execution plan, and you do pass year 2005 as value, the index is not use. So what is the purpose of that index then if it is not used when it is actually needed for optimal access?

    So think a bit about the impact and logic of a request like this - and whether it makes any sense.
  • 3. Re: how to create index on a single parition
    user8731258 Newbie
    Currently Being Moderated
    I have paritioned table on the basis of source I have seven different sources.I am fetching data for one of the sources.Tats why i want to creta index only on that parition because rest of the sources i am not using..
  • 4. Re: how to create index on a single parition
    rp0428 Guru
    Currently Being Moderated
    >
    I have paritioned table on the basis of source I have seven different sources.I am fetching data for one of the sources.Tats why i want to creta index only on that parition because rest of the sources i am not using
    >
    Simple - just delete the data since you aren't using it anyway.
  • 5. Re: how to create index on a single parition
    Galo Balda Explorer
    Currently Being Moderated
    Is partition pruning taking place when you look at the "source" (1/7) that you want? You need to check your execution plan to see if this is happening.

    Do you you have global or local indices? If you only want the index that covers "source" 1/7 then you need a local index.

    HTH,
    Galo
    @galobalda
  • 6. Re: how to create index on a single parition
    rp0428 Guru
    Currently Being Moderated
    >
    Do you you have global or local indices? If you only want the index that covers "source" 1/7 then you need a local index.
    >
    Absolutely wrong! An index is on the table. It doesn't make any difference if the index is global or local.

    Reread Billy's answer. In particular, read this part
    >
    Not possible. An index exists against a table. For a partitioned table, that index can be "global" (single physical index) or "local" (physical index per partition). But the index definition is for the table - not for a "specific piece" of the table.
  • 7. Re: how to create index on a single parition
    Nicosa Expert
    Currently Being Moderated
    Hi,

    I've been thinking a bit about your requirement.
    One way could be to use the fact that index do not "store" nulls.

    Let's make a table with data :
    SQL> create table t
      2  (
      3  grp integer,
      4  id integer,
      5  val varchar2(10)
      6  )
      7  partition by range(grp)
      8  (
      9  partition p01 values less than (2)
     10  ,partition p02 values less than (3)
     11  ,partition p03 values less than (4)
     12  ,partition pXX values less than (maxvalue)
     13  );
    
    Table created.
    
    SQL> insert into t select 1+mod(level,4), level, dbms_random.string('a',10) from dual connect by level <= 200000;
    
    200000 rows created.
    
    SQL> select grp, count(*) cnt from t group by grp order by grp;
    
           GRP       CNT
    ---------- ----------
          1     50000
          2     50000
          3     50000
          4     50000
    
    SQL> select grp, count(*) cnt from t group by grp order by grp;
    
           GRP       CNT
    ---------- ----------
          1     50000
          2     50000
          3     50000
          4     50000
    
    SQL> begin
      2  dbms_stats.gather_table_stats(
      3  ownname => user,
      4  tabname => 'T',
      5  method_opt => 'for all columns size 1',
      6  granularity => 'global and partition',
      7  estimate_percent => null);
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select table_name, partition_name, num_rows from user_tab_partitions where table_name='T' order by partition_name;
    
    TABLE_NAME                 PARTITION_NAME               NUM_ROWS
    ------------------------------ ------------------------------ ----------
    T                      P01                       50000
    T                      P02                       50000
    T                      P03                       50000
    T                      PXX                       50000
    Now let's say you only want to index P01.
    You could do it as follows :
    SQL> create index t_idx on t(decode(grp,1,id,null)) local;
    
    Index created.
    The index would only have IDs from partition P01.
    SQL> select INDEX_NAME, PARTITION_NAME, DISTINCT_KEYS
      2  from user_ind_partitions
      3  where index_name='T_IDX'
      4  order by partition_name;
    
    INDEX_NAME                 PARTITION_NAME                DISTINCT_KEYS
    ------------------------------ ------------------------------ -------------
    T_IDX                      P01                          50000
    T_IDX                      P02                           0
    T_IDX                      P03                           0
    T_IDX                      PXX                           0
    
    SQL> select SEGMENT_NAME, PARTITION_NAME, BYTES
      2  from user_segments
      3  where SEGMENT_NAME='T_IDX'
      4  order by partition_name;
    
    SEGMENT_NAME                                               PARTITION_NAME                BYTES
    --------------------------------------------------------------------------------- ------------------------------ ----------
    T_IDX                                                    P01                        1048576
    T_IDX                                                    P02                          65536
    T_IDX                                                    P03                          65536
    T_IDX                                                    PXX                          65536
    See, only the keys from P01 are here, and only the segment for P01 partition has grown <i>(the 65k segment for the others partitions come from default values of storage clause which I didn't specify)</i>.

    Now you can query your table in the following way.
    Instead of writing (for P01) :
    select * from t where grp=1 and id between 30 and 40;
    If you want to use the index on P01, you would do :
    SQL> select * from t where grp=1 and decode(grp,1,id,null) between 30 and 40;
    
           GRP        ID VAL
    ---------- ---------- ----------
          1        32 hSrmmTYXXW
          1        36 xofKlsxORE
          1        40 vazRGnWzWc
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1186642050
    
    ------------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time        | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |   125 |  2375 |      3   (0)| 00:00:01 |        |        |
    |   1 |  PARTITION RANGE SINGLE         |        |   125 |  2375 |      3   (0)| 00:00:01 |      1 |      1 |
    |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T        |   125 |  2375 |      3   (0)| 00:00:01 |      1 |      1 |
    |*  3 |    INDEX RANGE SCAN             | T_IDX |   225 |        |      2   (0)| 00:00:01 |      1 |      1 |
    ------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("GRP"=1)
       3 - access(DECODE("GRP",1,"ID",NULL)>=30 AND DECODE("GRP",1,"ID",NULL)<=40)
    The index is being used.
    +(also note that the grp value is "given" twice in the query : once explicitly, the other one within the decode.)+

    Now for a query like :
    SQL> select * from t where grp=2 and id between 30 and 40;
    
           GRP        ID VAL
    ---------- ---------- ----------
          2        33 bhFrCYCiDb
          2        37 jnPauHJiyo
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2931986080
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |      |     5 |    95 |    68     (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|      |     5 |    95 |    68     (0)| 00:00:01 |     2 |     2 |
    |*  2 |   TABLE ACCESS FULL    | T    |     5 |    95 |    68     (0)| 00:00:01 |     2 |     2 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("ID"<=40 AND "ID">=30 AND "GRP"=2)
    The optimizer goes for a FULL TABLE (partition) SCAN.

    <b>NOTE :</b> That would also work with a global index.

    Of course, it remains a rather dirty tweak...
    ;-)
  • 8. Re: how to create index on a single parition
    Solomon Yakobson Guru
    Currently Being Moderated
    Nicosa wrote:
    Of course, it remains a rather dirty tweak...
    I assume you understand the difference betwen index on a single parition and index on all partitions with all but one empty partitions.

    SY.
  • 9. Re: how to create index on a single parition
    Nicosa Expert
    Currently Being Moderated
    Hi Solomon,
    Solomon Yakobson wrote:
    I assume you understand the difference betwen index on a single parition and index on all partitions with all but one empty partitions.
    Yes, I agree the index is not on a single partition (as this doesn't exists in Oracle).

    But this might achieve what the OP wanted assuming it was :
    - to not consume space for indexing on every other partitions
    - to not slowing insertion on other partitions
    - to allow index access on the desired partition

    It took it as an "exercise", that is why I specified it's "rather dirty", but I thought that it would be worth to post in order to have the feedbacks/critics from power user (such as you).

Legend

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