Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: Performance between oracle nested table and normal table join operation.

    Hi,

    I'm building a billing software. I'm using oracle as a database.
    In my database design I'm planning to place all the billing information in one single table. And inside that billing table I will put the other information like (meter reading information) in a nested table. There may be 5-6 nested table data type in this billing table. I have chosen this concept to make the billing process faster. I know I can achieve that by joining table.

    Now my question is - is it a good decision to go with the nested table concept ?


    Will the performance be good when there will be 5-10 thousand rows for each month billing ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I have chosen this concept to make the billing process faster.
    Please post reproducible test case that proves it is faster.
    I bet that it is faster when it only exists between your ears.

    Nested tables only add complexity & delay.
    Can you prove me wrong?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2015
    Posts
    2
    I need to perform some predefined calculation on the billing data and then the final data will be stored in the nested table.
    If i plan to do the calculation during run time or want to put that data in a separate table, then I need some join operation during accessing the billing data in next time.

    But If I put them in nested table, I can access them directly at any time without any join complication and delay.

    Hope that it explain the use case properly.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But If I put them in nested table, I can access them directly at any time without any join complication and delay.

    You are free to rationalize all you want.
    I still challenge you to post reproducible test case that shows your nested tables are REALLY, Really, really faster.
    Please be aware that none of Oracle Performance Guides advocate that nested tables provide any performance benefits.

    You are entitled to your opinions; regardless of how unrealistic they actually are.

    Only you & you poor application users will suffer the consequences.


    >5-10 thousand rows for each month billing ?
    BTW, above is a TINY, Tiny, extremely small number of row for any Production Oracle table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    From my point of view, I prefer "ordinary" tables, normalized, having referential integrity constraints properly set. I *tried* to force myself to use nested tables (what a nice feature!), but didn't see any advantage - only disadvantage.

    For example, if you use Forms (as I do), there's nothing much you can do with a nested table. Yes, you CAN display that data, but you have to write a stored procedure which will "extract" nested table's data and present it in a data block. You have to write a procedure which will save data into a nested table. That just makes things (too) complex to handle. It's just too much coding for something Forms' wizard normally does in a matter of seconds.

    Therefore, I believe that it is wonderful that Oracle supports nested tables, but I won't use them.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    ive been both an oracle dba and software developer since Orace 5 and While A nice feature, I have never found a reason for nested tables that joined tables didn't do better.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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