DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Efficiency - One table vs Nested table/resultset

  1. #1
    Join Date
    Dec 2000
    Posts
    22
    Hello friends,

    I would like to discuss some ways of reporting, and their efficiency. Classical example: we have two tabels - orders and orderitems, where orders has for example two fields: clientno, orderno, and orderitems has orderno, itemno, qty fields.
    For example we have to make a huge report that shows all orders and their corresponding items.
    Whe have two options:
    1. Have a join, that would return one table:
    select <needed fields> from orders,orderitems where orders.orderno = orderitem.orderno order by orderno

    this way we get redundant fields for every order.

    2. Return a cursor with one field as a nested table that would contain all order items.

    It looks like a more efficient way to me.

    Please, share your thoughts on both ways to create such a report.

    I've worked with MS ADO for quite a while, and such a report is easy to make using MS DataShape provider. I've read somewhere that in PL/SQL such things are done using CONNECT BY clause. Could you paste this statement for the problem above? If it's not CONNECT BY, please give a hint how to assign orderitems to nested table field

    Thank you very much for your replies.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The CONNECT BY clause is typically used in a tree-structured query.

    It sounds like you will have to create an object type that contains the nested table.
    Jeff Hunter

  3. #3
    Join Date
    Dec 2000
    Posts
    22
    Hi.

    Could you please paste an example of CONNECT BY statement?

    Thank you.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Using a plan_table created by utlxplan:
    select lpad(' ',2*(level -1)) || level || '.' || nvl(position,0)
    || ' ' || operation || ' ' || options || ' ' || object_name || ' '
    || object_type || ' ' || decode(id,0,statement_id || ' Cost = ' || position)
    || ' ' || object_node "Query Plan"
    from plan_table
    start with id=0 and statement_id = '&Statement_id'
    connect by prior id=parent_id
    and statement_id = '&Statement_id'
    Jeff Hunter

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