This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Jun 6, 2012 8:27 AM by askraks RSS

IOT with Partitioned Table

user12050217 Explorer
Currently Being Moderated
Oracle 11.2.0.1
Windows xp

I have to create a table which will be IOT and partitioned. Table columns are Year, Roll_no, Class, Exam_Type, Student_Name, Father_Name, Mother_Name, Result, Tot_Marks, Date_Of_Birth. The table will never be updated no DML/no DDLs. The data will be loaded into the table from different sources. Kindly tell me what should be a good design for this table i.e. partition by year, student_name, father_name, mother_name etc. so that it can produce the query output very fast. Since table will never be updated so kindly tell me what should be storage parameters i.e. PCT* etc. Data will be loaded for the year 2010 to 2012 and total approximated rows will be 150 million.

I am reading below link for the answer, but not finding. Kindly reply me.
http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#i1006727

Thank you.

Edited by: user12050217 on Jun 5, 2012 2:35 AM
  • 1. Re: IOT with Partitioned Table
    GokulGopal Newbie
    Currently Being Moderated
    You seem to have already decided on IOT, partitioned.

    We cannot give design suggestions unless we know what kind of queries will run against this table.
  • 2. Re: IOT with Partitioned Table
    user12050217 Explorer
    Currently Being Moderated
    You tell me what kind of queries can be run against this table. When I am saying that table will never be updated (I mean just read only after loading the data)so, I think there can be only select queries can be there right?
  • 3. Re: IOT with Partitioned Table
    GokulGopal Newbie
    Currently Being Moderated
    Yes, without understanding select queries, we cannot design the table.
  • 4. Re: IOT with Partitioned Table
    user12050217 Explorer
    Currently Being Moderated
    something like :
    select * from table where student_name like 'Gokul%'
    or
    select * from table where father_name like 'Gopal%'
    or
    select * from table where year=2011 and result='Pass' and marks between 300,400 etc.

    Table is just a search table to query the data. Should I partition on each columns, because each / any column can be part of where clause.
  • 5. Re: IOT with Partitioned Table
    user12050217 Explorer
    Currently Being Moderated
    Any help please. How do I create partitions on more than one column ? What should be PCT*, initial next parameters ?
    Is it possible to have range and list partitions simultaneously for a table ?

    I just want to have partitions something like this :

    partition by range(year) <---Seperation of data as year wise.
    (
    PARTITION part1 VALUES LESS THAN (2000),
    PARTITION part2 VALUES LESS THAN (2010),
    PARTITION part3 VALUES LESS THAN (2011),
    PARTITION part4 VALUES LESS THAN (2012)

    And

    partition by range(roll_no) <---There should be a seperate partition for roll number series.
    (
    PARTITION part16 VALUES LESS THAN (1),
    PARTITION part17 VALUES LESS THAN (10000),
    PARTITION part18 VALUES LESS THAN (20000),
    ...
    ...

    PARTITION BY LIST(EXAM) <---Different Exam categories should be in a seperate partition.
    (
    PARTITION VALUES ('K')
    PARTITION VALUES ('M')
    PARTITION VALUES ('D')
    PARTITION VALUES ('P')
    ...
    ...

    partition by range(STUDENT_NAME) <---student name wise seperate partitions.
    (
    PARTITION part51 VALUES LESS THAN ('A'),
    PARTITION part52 VALUES LESS THAN ('B'),
    PARTITION part53 VALUES LESS THAN ('C'),
    ...
    ...

    Am I doing right or wrong, kindly help me. I am thinking that If I have seperate partitions then query performance will be best.

    Thanks.
  • 6. Re: IOT with Partitioned Table
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    The data will be loaded into the table from different sources.
    ...
    Since table will never be updated.
    It is not clear. will it be loaded initially, or periodically?
    Kindly tell me what should be a good design for this table i.e. partition by year, student_name, father_name, mother_name etc. so that it can produce the query output very fast.
    Often partitions made based on time column, to eliminate need of expensive DELETE to purge old data.
    Are you going to load new data and remove old data periodically?

    What column(s) will be in most of queries WHERE close? How many distinct values it will have?
  • 7. Re: IOT with Partitioned Table
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    As you have stated the table does not getted updated (Only data is loaded). As per my knowledge the design and partitioning and index would be based on the type of Queries or reports developers are expecting from this segment.

    As you have given priority for performance (but not managability), then I suggest opt for Year wise Partition with "range" (you can opt sub range - with quarter wise paritions). Further, if I expect your queries are some thing based on

    marks of student (calcuations ratios and averages across years)
    rollnum

    I suspect opt for index with combinations rollnum,tot_marks. Build an index wit Global paritition instead of local parititoned index. Since, as per your description the you are opting or IOT (no deletions are expected), so it would be worthful for performance perspective.
    Lastest records would be hanging around the right most of the leaf blocks (monitonicall increasing).

    HTH

    - Pavan Kumar N
  • 8. Re: IOT with Partitioned Table
    rp0428 Guru
    Currently Being Moderated
    >
    Any help please. How do I create partitions on more than one column ?
    >
    You can't - not what you are trying to do anyway. Your examples show that you are trying to create partitions with multiple, independent partition keys. That isn't supported.

    You can create a single partition key that consists of multple columns. You do that the same way you create an index having multiple columns.
    >
    What should be PCT*
    >
    You can set PCTFREE to zero (0) since you will not be doing updates. PCTUSED does not apply, and cannot be used, for index-organized tables.

    See PCTFREE and PCTUSED of the physical_attributes_clause in the SQL Language doc
    http://docs.oracle.com/cd/E11882_01/server.112/e17118/clauses007.htm
    >
    PCTUSED is not a valid table storage characteristic for an index-organized table.
    >
    As to
    >
    Is it possible to have range and list partitions simultaneously for a table ?
    >
    Depends on exactly what you mean.

    NO - If you mean two top-level partitioning schemes.

    YES - If you mean one top-level partitioning scheme that is then further subpartitioned. That is called composite-partitioning
    See 'Composite Partitioning' in the VLDB and Partitioning Guide
    http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm#i460895

    Oracle supports the six types of composites discussed in that doc
    >
    Composite Range-Range Partitioning
    Composite Range-Hash Partitioning
    Composite Range-List Partitioning
    Composite List-Range Partitioning
    Composite List-Hash Partitioning
    Composite List-List Partitioning
  • 9. Re: IOT with Partitioned Table
    user12050217 Explorer
    Currently Being Moderated
    Thanks for your reply.
    Let me rephrase question.

    Table columns are Year, Roll_no, Class, Exam_Type, Student_Name, Father_Name, Mother_Name, Result, Tot_Marks, Date_Of_Birth. The table will never be updated after loading the data.

    create table stu_history
    (
    year number(4),
    roll_no number(7),
    class varchar2(1),
    exam_type varchar2(1),
    student_name varchar2(45),
    father_name varchar2(45),
    mother_name varchar2(45),
    result varchar2(4),
    tot_marks number(3)
    date_of_birth date,
    primary key(year,roll_no,class,exam_type,student_name,father_name,mother_name,result,tot_marks,date_of_birth)
    )
    organization index
    partition by range(year)
    (
    PARTITION part1 VALUES LESS THAN (2010),
    PARTITION part2 VALUES LESS THAN (2011),
    PARTITION part3 VALUES LESS THAN (2012)
    )
    /

    Table created.

    Here I did not specified PCT* parameters, because it is just at design stage on testing db.

    Now, I want to add more partitions, so if I says : Means seperate partition for roll numbers.
    alter table stu_history add
    partition by range(roll_no)
    (
    PARTITION part4 VALUES LESS THAN (1000000),
    PARTITION part5 VALUES LESS THAN (2000000),
    PARTITION part6 VALUES LESS THAN (3000000)
    /
    
    partition by range(roll_no)
              *
    ERROR at line 2:
    ORA-00902: invalid datatype
    When I said :
    SQL> alter table stu_history add partition part4 by range(roll_no) values less than (1000000);
    alter table stu_history add partition part4 by range(roll_no) values less than (1000000)
                                                  *
    ERROR at line 1:
    ORA-14020: this physical attribute may not be specified for a table partition
    It means now I can not add a new partition by range for different column. So, how to it ?

    At the same time I want to have seperate partitions for all the columns, so that query performance would be best.

    There are 20 types of classes whose code are one character i.e. X,R,P,J,M etc. Means, all data for class X should be in a seperate partition and all data for R in seperate partition (Just thinking that seperate partitions means faster query output). Now, if there are some different code founds at the time of data loading then those rows should be in a separate partition. So, I think for class column I would need list partition.

    Kindly tell me I am doing right or wrong for having seperate partitions for all columns.

    In the where part of select query there can be any/more column(s), so I think all columns should be have separate partitions to fetch the fastest rows.

    Thank you.
  • 10. Re: IOT with Partitioned Table
    Pavan Kumar N Newbie
    Currently Being Moderated
    Hi,

    It would be "SUBPARTITION BY " applicable but not partition by

    - Pavan Kumar N
  • 11. Re: IOT with Partitioned Table
    user12050217 Explorer
    Currently Being Moderated
    Thanks for your reply. Ok. So table's main partition will be year and subpartitions will be other columns like roll_no, student_name, class etc. right? Then how will oracle read sub partitions when there is no year column in where clause ?

    Can you please tell me how do I create the sub partitions on this table ? After loading some data I will explain plan to see that sub partitions were queried or not with Index scan, because its an IOT too.

    Thank you.
  • 12. Re: IOT with Partitioned Table
    Pavan Kumar N Newbie
    Currently Being Moderated
    hi,

    Refer to http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm#i1007318
    AFAIk, you need to drop the table/segment and recreate it or try modify partition.


    PARTITION BY RANGE (equipno)
    SUBPARTITION BY RANGE(roll_no)
    SUBPARTITIONS <number> STORE IN (list of tablespaces)


    Coming to your issue

    alter table stu_history
    MODIFY PARTITION part1
    ADD SUBPARTITION subpart1 values less than (1000000)

    - Pavan Kumar N
  • 13. Re: IOT with Partitioned Table
    user12050217 Explorer
    Currently Being Moderated
    I failed to create the table.
    SQL> ed
    Wrote file afiedt.buf
    
      1  create table stu_history
      2  (
      3  year number(4),
      4  roll_no number(7),
      5  class varchar2(1),
      6  exam_type varchar2(1),
      7  student_name varchar2(45),
      8  father_name varchar2(45),
      9  mother_name varchar2(45),
     10  result varchar2(4),
     11  tot_marks number(3),
     12  date_of_birth date,
     13  primary key(year,roll_no,class,exam_type,student_name,father_name,mother_name,result,tot_marks,date_of_birth)
     14  )
     15  organization index
     16  partition by range(year)
     17  SUBPARTITION BY RANGE (roll_no)
     18  SUBPARTITION sub1
     19  (
     20  SUBPARTITION subpart1 VALUES LESS THAN (1),
     21  SUBPARTITION subpart2 VALUES LESS THAN (10000),
     22  SUBPARTITION subpart3 VALUES LESS THAN (MAXVALUE)
     23  )
     24  SUBPARTITION BY LIST (class)
     25  SUBPARTITION sub2
     26  (
     27  SUBPARTITION subpart4 VALUES ('A'),
     28  SUBPARTITION subpart5 VALUES ('C'),
     29  SUBPARTITION subpart6 VALUES LESS THAN (MAXVALUE)
     30  )
     31  SUBPARTITION BY LIST (exam_type)
     32  SUBPARTITION sub3
     33  (
     34  SUBPARTITION subpart7 VALUES ('M'),
     35  SUBPARTITION subpart8 VALUES ('S'),
     36  SUBPARTITION subpart9 VALUES LESS THAN (MAXVALUE)
     37  )
     38  SUBPARTITION BY RANGE (student_name)
     39  SUBPARTITION sub4
     40  (
     41  SUBPARTITION subpart10 VALUES LESS THAN ('A%'),
     42  SUBPARTITION subpart11 VALUES LESS THAN ('B%'),
     43  SUBPARTITION subpart12 VALUES LESS THAN (MAXVALUE)
     44  )
     45  SUBPARTITION BY RANGE (date_of_birth)
     46  SUBPARTITION sub5
     47  (
     48  SUBPARTITION subpart13 VALUES LESS THAN (TO_DATE('01/01/1985','DD/MM/YYYY')),
     49  SUBPARTITION subpart14 VALUES LESS THAN (TO_DATE('01/01/1986','DD/MM/YYYY')),
     50  SUBPARTITION subpart15 VALUES LESS THAN (MAXVALUE)
     51  )
     52  (
     53  PARTITION part1 VALUES LESS THAN (2010),
     54  PARTITION part2 VALUES LESS THAN (2011),
     55  PARTITION part3 VALUES LESS THAN (2012),
     56  PARTITION part4 VALUES LESS THAN (MAXVALUE)
     57  )
     58* )
    SQL> /
    SUBPARTITION sub1
    *
    ERROR at line 18:
    ORA-25198: only range, list, and hash partitioning are supported for index-organized table
    
    
    SQL>
  • 14. Re: IOT with Partitioned Table
    Pavan Kumar N Newbie
    Currently Being Moderated
    Hi,
    SQL> create table stu_history
      2   (
      3   year number(4),
      4   roll_no number(7),
      5   class varchar2(1),
      6   exam_type varchar2(1),
      7   student_name varchar2(45),
      8   father_name varchar2(45),
      9   mother_name varchar2(45),
     10   result varchar2(4),
     11   tot_marks number(3),
     12   date_of_birth date,
     13   primary key(year,roll_no,class,exam_type,student_name,father_name,mother_name,result,tot_marks,date_of_birth)
     14   )
     15    partition by range(year)
     16   subpartition by range(roll_no)
     17     --specify first partition and sub partiiton
     18    ( PARTITION part1 VALUES LESS THAN (2010)
     19      (
     20             SUBPARTITION subpart1 VALUES LESS THAN (1),
     21             SUBPARTITION subpart2 VALUES LESS THAN (10000),
     22             SUBPARTITION subpart3 VALUES LESS THAN (MAXVALUE)
     23      ),
     24      PARTITION part2 VALUES LESS THAN (2011)
     25      (
     26             SUBPARTITION subpart4 VALUES LESS THAN (1),
     27             SUBPARTITION subpart5 VALUES LESS THAN (10000),
     28             SUBPARTITION subpart6 VALUES LESS THAN (MAXVALUE)
     29      )
     30    );
    
    Table created.
    As you opting for IOT -composite partitions is not supported.
    Try to follow the same process and modify the create table structure (I have given a demo not exact one)

    - Pavan Kumar N
1 2 Previous Next

Legend

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