Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: finding the names of all nested tables effeciently

    Hi,

    i would like to find the name of all the nested tables under a parent table. In other words I want a hierarchical query which would give me all the nested tables under the root parent table hierarchy (children, grandchildren etc.). The input to the query would be the parent table name.

    I tried using a simple 'connect by' on the avaialable RDBMS views (eg. USER_NESTED_TABLES), but the performance was poor. Is there a known solution for this, or any other way to improve the performance of "connect by" queries in general?

    Thanks,
    -cchak

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why do you expect anyone to tune SQL that they can not see?

    post both SQL & EXPLAIN PLAN for query
    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
    Oct 2010
    Posts
    2

    finding the names of all nested tables effeciently

    Its a simple connect-by hierarchical query which you can find anywhere in the net:

    for eg:
    select TABLE_NAME NESTED_TABLE_NAME,
    connect_by_root PARENT_TABLE_NAME PARENT_TABLE_NAME, OWNER
    from USER_NESTED_TABLES nt
    connect by prior TABLE_NAME = PARENT_TABLE_NAME
    and OWNER = OWNER

    I don't want a fine grain tuning of this specific query as it might change in the future. I just need some pointers which talks about improving connect-by queries in general (not necessarily this specific query).

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
  •