This discussion is archived
6 Replies Latest reply: Feb 19, 2013 10:41 AM by Omega3 RSS

Two 'blunt' questions on Table Partitioning ?

Omega3 Newbie
Currently Being Moderated
Version: 10.2, 11.2

I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

Question1.
What is the most common form of partitioning you have come across ?

Question2.
Generally, would you prefer creating a global index or a local index for a partitioned table ?
I mean: Global Index for particular type of partitioning or a particular column data type...etc
  • 1. Re: Two 'blunt' questions on Table Partitioning ?
    sb92075 Guru
    Currently Being Moderated
    Omega3 wrote:
    Version: 10.2, 11.2

    I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

    Question1.
    What is the most common form of partitioning you have come across ?
    non-partitioned tables

    >
    Question2.
    Generally, would you prefer creating a global index or a local index for a partitioned table ?
    I mean: Global Index for particular type of partitioning or a particular column data type...etc
    It depends.
    If one was ALWAYS better than the other, then the other should NOT exist.
  • 2. Re: Two 'blunt' questions on Table Partitioning ?
    vlethakula Expert
    Currently Being Moderated
    It depends, I have seen till now RANGE PARTITIONING (partitioned by date)

    http://richard-e-hall.blogspot.com/2007/08/global-vs-local-indexes.html
  • 3. Re: Two 'blunt' questions on Table Partitioning ?
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    1. Most common approach I've seen is range partitioning on date. Very common because it means you can drop old data very easily with little overhead when it expires without having to delete it slowly and resource intensively.

    2. I would create locally if I could. This would avoid the need to update the indexes as partition maintenance was going on (dropping, adding, etc). If you need to do partition work and you are going to invalidate the global index look at using the UPDATE GLOBAL INDEXES clause.

    There is some more information about [url http://www.ora00600.com/articles/oracle-partitioning.html]Partitioning you can have a read through and I would definitely recommend reading the documenation.

    Rob
  • 4. Re: Two 'blunt' questions on Table Partitioning ?
    rp0428 Guru
    Currently Being Moderated
    >
    I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

    Question1.
    What is the most common form of partitioning you have come across ?

    Question2.
    Generally, would you prefer creating a global index or a local index for a partitioned table ?
    I mean: Global Index for particular type of partitioning or a particular column data type...etc
    >
    Sounds like interview questions to me. What answers to you give to those questions?

    If you are new to partitioning I suggest you review the entire VLDB and Partitioning Guide
    http://docs.oracle.com/cd/B28359_01/server.111/b32024/toc.htm

    That doc covers all aspects of partitioning including the different types of partitioning and types of indexes and when to use each of them.

    Partitioning should only be used when it solves a demonstrated problem that can't be solved using traditional techniques.

    Common forms are RANGE, LIST, HASH, and composites of those. REF partitioning is also used as needed.

    The type of index to use depends on the data and the reason partitioning was used to begin with. Global indexes can degrade the performance of partition maintenance.
  • 5. Re: Two 'blunt' questions on Table Partitioning ?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Omega3 wrote:
    Version: 10.2, 11.2

    I wasn't fortunate enough to work in Partitioning in my DBA career. So , I have 2 basic questions.

    Question1.
    What is the most common form of partitioning you have come across ?
    Mostly range by time
    Some list
    Some range/list or range/hash composite (ranged by time, again)
    Question2.
    Generally, would you prefer creating a global index or a local index for a partitioned table ?
    I mean: Global Index for particular type of partitioning or a particular column data type...etc
    Mostly local, with a minimum number of global (usually for uniqueness) on D/W
    More likely to be global with some local for OLTP
    Some globally hash partitioned indexes on non-partitioned tables

    Regards
    Jonathan Lewis
  • 6. Re: Two 'blunt' questions on Table Partitioning ?
    Omega3 Newbie
    Currently Being Moderated
    Thank you Rob, Jonathan, rp0428

    sb92075,


    Question1.
    What is the most common form of partitioning you have come across ?

    non-partitioned tables

    Question2.
    Generally, would you prefer creating a global index or a local index for a partitioned table ?
    I mean: Global Index for particular type of partitioning or a particular column data type...etc

    It depends.
    If one was ALWAYS better than the other, then the other should NOT exist.



    You are a star.
    You are like the opposite of Billy Verreyne, Frank Kulash, Hemant Chitale, Jonathan Lewis, rp0428, EdStevens, Levi periera, sebastian solbach, Uwe Hesse, Aman, BluShadow, Solomon Yakobsson ..etc

    Are you from Israel by any chance ?

    Just don't waste the disk space dude especially when we are going to face the dollar collapse/Depression

Legend

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