Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Unanswered: Table Partitioning

    Hi,

    I have a non partitioned table with 415 million rows. I am working on Partitioning the Table based on END_DATE as RANGE PARTITION.
    Initially the Table was partitioned based on another date column and the performance was poorer compared to Non Partitioned Table(NPT).
    The number of data files allocated to the NPT tablespace is 166 but to that of the tablespace for each partition was 6-7 files.
    My question is Will the Performance increaese if I increase the number of datafiles in each Tablespace to say 60-70?
    We are not using ASM and segment_management is manual.
    And to size the space for each partition tablespace Can I check the avg rowsize in bytes and multiply by number of rows?

    to find the avg.row size in bytes I am using
    select bytes from dba_segments where segment_name=''/select num_rows from dba_tables where table_name=''

    Please advice

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think the number of data files is a red herring (not relevant).

    More likely is that you have sql which isnt working well on your partitioned table. For example if the sql is not using your end_date and say you have local indexes which it is using, on a partitioned table it might have to probe all of the local indexes which could be much slower than probing a single index on a non partitioned table.

    Alan

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    F/s

    The number of files would be of consequence and dependent only on the type and setup of the storage you are using.

    Also this statement makes no sense:
    to find the avg.row size in bytes I am using
    select bytes from dba_segments where segment_name=''/select num_rows from dba_tables where table_name=''
    If you are partitioning by month, then you could estimate the size of each tablespace as:
    Code:
    SELECT yyyymm, rec_cnt, (rec_cnt * avg_row_len) / 1024 / 1024 mb
      FROM (SELECT   TO_CHAR (end_date, 'YYYYMM') yyyymm, COUNT (*) rec_cnt
                FROM mytable
            GROUP BY TO_CHAR (end_date, 'YYYYMM')),
           (SELECT avg_row_len
              FROM user_tables
             WHERE table_name = 'MYTABLE');
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2009
    Posts
    5
    Thanks for your replies,
    Alan,
    The explain plan looks good for the partiioned table and i could find partition pruning on the explain plan. My point was if we spread the data across files and disks the parallelism could be achieved.

    Thanks,

  5. #5
    Join Date
    Feb 2009
    Posts
    5
    LK Brwn,
    I have a question
    the avg_row_len includes actual row size + header also

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •