Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    9

    Unanswered: regarding partition of table in Oracle Database

    hi,

    I have a very huge data in a table with the size of tera plus.. basically it is a call details table where date is the one criteria to get the details.. from the table..

    scenario.

    Table having data for past 6 months.
    index is there on key fields and date column as well..
    content size should be very huge

    I have two solutions..

    creating partition day wise
    creating partition month wise

    I want to know which one is effective and easy to retrieve..

    I have a similar kind of table currently.. which has day wise partition but still the data retrieval based on date is taking very long time..

    Pls suggest.. which one would be better and how it works..

    Thanks..

  2. #2
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    hi... here can be more problems in group:
    - check execution plan, if does not executed "full table scan"
    - when you have another partitioned table in same query, with not same partition range, can be scanned more partitions as is necessary.
    - indexes are local and prefixed? the worst solution is global non prefixed index
    - when your where clause is not selective and returns too many rows, can database ignore indexes and executes full table(partition) scan
    - exam your pga size
    -------
    to daily / monthly partitioning
    depends from your query- when often you need whole month data, is better partitioning at monthly base

  3. #3
    Join Date
    Apr 2007
    Posts
    9

    one more clarification

    does partitioning the table will influence while inserting data??

  4. #4
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    when you have compressed table than yes... (in non - partitioned table too )
    grater problem can be indexes especially bitmap indexes in insert operation,
    when you insert data from two and more sessions.
    some small overhead against non partitioned table must exists. check your application, if use bind variables.
    you can improve performance in massive insert with append hint or "nologging" option. but this please consult with your dba. after this operation must be backuped tablespace.

  5. #5
    Join Date
    Feb 2007
    Posts
    71
    Quote Originally Posted by baloo99
    when you have compressed table than yes... (in non - partitioned table too )
    grater problem can be indexes especially bitmap indexes in insert operation,
    when you insert data from two and more sessions.
    some small overhead against non partitioned table must exists. check your application, if use bind variables.
    you can improve performance in massive insert with append hint or "nologging" option. but this please consult with your dba. after this operation must be backuped tablespace.
    hi baloo99

    isn't it faster when inserting into tables with bitmapped indexes because it doesn't generate redo logs right?

    You can also tell the dba to extend the segment so that dynamic extending wouldn't occur

  6. #6
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    As with all things Oracle the best advice I can give is for you to create a test database and try out different partitioning schemes like range, hash, sub partitioning, local indexes, global indexes etc. Then point your test application against it and benchmark it, then you will get a much better idea.

    Also try to factor in not just performance but also maintainance effort, you may find the DBA workload cancels out the peroformance advantage of say daily versus monthly partitions.

    Of course the closer your test environment (in terms of hardware, OS, data volumes etc) is to your production environment the more accurate your results will be

    Alan

Posting Permissions

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