Keeping rows together in a partition - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Keeping rows together in a partition

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    I disagree on the composite partitioning -- maybe you are thinking of hash clusters, which would group the values.

    Well i said of composite partitioning for his query as to "is there some way of using 2 levels of partitioning" and not my suggestion ..

    They don't necessarily have to be -- the point is to get them clustered, so if on average you have 30 records per customer per partition you might fit them into one or two Oracle blocks instead of 30 Oracle blocks. Even if each Oracle block were split over 2 file system blocks you have still reduced the i/o's (on average) from the table by a factor of somewhere between 10 and 30

    Well what if the smallest OS Blocks (.5K), which would mean 16 OS Blocks for a 8K Oracle Block, be split across N Disks?

    Then do you still find any i/o reduction? -- Not neccessarly.

    For this I would see no gain in ordering data by any field unless its a ordered structure.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    Well what if the smallest OS Blocks (.5K), which would mean 16 OS Blocks for a 8K Oracle Block, be split across N Disks?

    Then do you still find any i/o reduction? -- Not neccessarly.

    For this I would see no gain in ordering data by any field unless its a ordered structure.

    Abhay. [/B]
    Well the question is, "does it matter whether you have to read 30 Oracle blocks or just one or two?". Regardless of the relationship between o/s blocks and Oracle blocks, Oracle is going to request that less o/s blocks be read if it only requires that a single Oracle block be read, right?

    Doesn't have to be an ordered structure at all. Oracle knows whether the rows are physically order by reading the indexes clustering factor, and when accessing the table based on an index scan it will request reads on a smaller number of Oracle (and therefore o/s) blocks if the rows are physically ordered.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    ====

    Oracle is going to request that less o/s blocks be read if it only requires that a single Oracle block be read, right?

    ====

    For this it would not necessarly mean more physical I/O calls (comapred to my former analogy) but suerly will mean more LIO calls..

    Yes dave.. i do agree with reading more blocks here, no issues abt it.. and we have debated on the pros/cons of ordering by any column.. didnt we?

    And the conclusion was it solely depends on how ur application is quering.. wasnt it?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ========
    Well what if the smallest OS Blocks (.5K), which would mean 16 OS Blocks for a 8K Oracle Block, be split across N Disks?

    Then do you still find any i/o reduction? -- Not neccessarly.

    For this I would see no gain in ordering data by any field unless its a ordered structure.

    Abhay.
    =========

    Today, there will be many blocks in the UNIX DB Server.
    If you use LVM, then probably the page size (block size) will be 8k.

    So, 16 OS blocks of size 0.5K is ruled out with respect to Oracle data files. The 0.5K is applicable to only in redo log files.

    Physically ordering of rows prior to load is very important to get optimum performance. Also number of disks/controllers and stripe size matters.

    Tamil

  5. #15
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by tamilselvan
    Physically ordering of rows prior to load is very important to get optimum performance. Also number of disks/controllers and stripe size matters.

    Tamil
    Well you would like to have a look to the post where there was a pretty good debate & brief ups of many scnaerio.. which i mentioned in my previous post also..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #16
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    And the conclusion was it solely depends on how ur application is quering.. wasnt it?
    Sure -- it's only worth physically clustering if the performance of the application's queries will be improved. From Amir's original posting this seems to be the case.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width