This discussion is archived
5 Replies Latest reply: Aug 19, 2013 6:35 PM by Hemant K Chitale RSS

Partitioning or index organized table. Suggestion required.

supersen Newbie
Currently Being Moderated

Hi Gurus,

 

We have decided to perfomance increase of table customer which has more than 100 Million records

 

{code}

customer_id  number,

cust_name   varchar,

Applied_date  date,

city  varchar(100)

{code}

 

This is the customer table structure.

 

We decided to composite partition the table based on applied date(range) and customer_id( hash).

 

I am confused to go with index-organized table ( where table and index are stored together)  for better performance.

 

Please suggest which one i go?  for better performance.

 

Please reply

 

Supersen

  • 1. Re: Partitioning or index organized table. Suggestion required.
    Sudharsanan Newbie
    Currently Being Moderated

    In my point of view, we can use both Composite partition and index-organized table.

    considering the table of records having more than 100 Million.

    When we use both the table performance will be higher than considering one.

    Answer to your question is that, the partitioning will be better than indexing

    while considering the largest records.

  • 2. Re: Partitioning or index organized table. Suggestion required.
    dba-developer Newbie
    Currently Being Moderated

    One more thing i would like to add to this while chosing between iot and normal partition table we also need to consider the statistics gathering as in our database we have been adding 5000 partitions daily to partitioned tables which are then analyzed . Which is very important and takes a lot of time.

     

    So my question to experts is : if we gather table stats for a normal partitioned table with cascade=true then it will gather stats for the index also when flase then we need to gather index stats seperately.For IOT how are the stats gathered and are they faster then normal partitioned table.As this is as major factor to be considerd wile choosing the partition type.

  • 3. Re: Partitioning or index organized table. Suggestion required.
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >I am confused to go with index-organized table ( where table and index are stored together)  for better performance.

     

    Is it with CUSTOMER_ID as the Primary Key ?

     

    >We decided to composite partition the table based on applied date(range) and customer_id( hash).

    The decision should be driven by

    a. How you insert data

    b. How you query data

    c.  How you plan to maintain (delete or move) data

     

    I presume that applied_date and customer_id will both be incremental -- i.e. every new customer has a higher applied_date and customer_id.

     

    What are queries against this table ?  How is it maintained ?

     

     

    Hemant K Chitale

  • 4. Re: Partitioning or index organized table. Suggestion required.
    supersen Newbie
    Currently Being Moderated

    My answer is

     

    Customer id is primary key.  we are not inserting any data. Just selecting from the query.

     

    I would like to know this.

     

    1) Please tell me it is compulsory to change all the source query , so that it contains parition name in it?

     

    or simply selecting from the table is enough ?

     

    Please tell

     

    Supsen

  • 5. Re: Partitioning or index organized table. Suggestion required.
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    If the query predicates (the WHERE clause) include the Partition Key columns, Oracle can do Partition Pruning -- i.e. identify the target Partition.  Else, it would have to do a Full Table Scan as it wouldn't know which Partition the target row(s) is/are in.

     

    For example, if you are partitioning by APPLIED_DATE  but your query is on the table by CITY, Oracle cannot identify the target Partition and has to do a Full Table Scan -- even if you subpartition by CUSTOMER_ID and include CUSTOMER_ID in your query, Oracle cannot identify the Subpartition because it cannot identify the Partition.

    Hemant K Chitale

Legend

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