Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    35

    Unanswered: Reorder a Hierarchy?

    Well, I'm stuck in a rut, need help with some ideas!

    I've been looking over all sorts of hierarchical methods for Oracle, namely using CONNECT BY / STARTING WITH.

    Using the common Org Chart as an example... I have a table that has this information stored in it:


    Code:
    emp_id   name    manager   seniority
    ------------------------------------
    1        Jake              20
    2         Tom    Jake      13
    3         Anne   Jake      4
    4         Gary   Jake      8
    5          Bob   Gary      11
    6          Ace   Gary      12
    7         Allen  Jake      16
    (my data is not already text padded but I went ahead and added it to make it easier to see the hierarchy)

    OK, so I already have that data, and it is already formed into a hiearchy. But, I want to now sort this table by the seniority column, but still keep the trees seperated. Basically much like the ORDER SIBLINGS BY functionality, so my end result would be:

    Code:
    emp_id   name    manager   seniority
    ------------------------------------
    1        Jake              20
    7         Allen  Jake      16
    2         Tom    Jake      13
    4         Gary   Jake      8
    6          Ace   Gary      12
    5          Bob   Gary      11
    3         Anne   Jake      4


    What I've tried to do thusfar is, actually run a query that puts the hierarchy back together (even though it is already intact), then resort the siblings and return the result.

    This works!

    But, the problem is that, with the mass amount of data I'm actually dealing with, the run time is huge! We're talking like.. 5-10 minutes to run one query that needs to be (ideally) <10 seconds processing time.

    So I am asking, is there a better way that I could do this?

    My data is already in a hiearchy, I just need to sort the trees by their leaves, so to speak.


    If anyone has ANY ideas, please feel free to share!

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    how about: order by sys_connect_By_path(...), seniority
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    May 2004
    Posts
    35
    Well, I actually want to get away from using CONNECT BY if at all possible. Perhaps there is a way to do what I'm doing with some nested subqueries or something? Anything with better performance then I am getting now by using CONNECT BY method.

    It may help if I let you in on the rest of the data that I have available to me. The data is actually a bill of material for building a product. To try and relate to the tech crowd, I'll use building a computer as my "final product" or level 0. A computer is made up of many things. For example, a case, a motherboard, etc. Well, a case has steel, screws, lighting system, and a few other things, that you have to have to build a case. A motherboard has to have all of the parts that are required for the motherboard! .. and so on..

    So just as a sample, say I have a table called BOM

    Code:
    id   item   level   part          days_late
    ------------------------------------------
    1    0.0    0       computer      100
    2    1.0    1        case         20
    3    1.1    2         steel       0
    4    1.1    2         led system  50
    5    1.2    3          led light  10
    6    1.2    3          led wire   50
    7    1.3    4           copper    50
    8    1.3    4           shielding 5
    9    1.2    3          connectors 20
    10   1.1    2         screws      0
    11   2.0    1        motherboard  100
    12   2.1    2         northbridge 50
    13   2.2    3          nb_part_1  30
    14   2.2    3          nb_part_2  50
    15   2.1    2         southbridge 100
    16   2.2    3          sb_part_1  0
    17   2.2    3          sb_part_2  75
    18   2.2    3          sb_part_3  100
    19   2.1    2         apu         10
    ...
    As you can see my data is already structured hiearchically. I also have a level column and a "path" , so to speak (the 1.1, 1.2, etc). However, I want to order the branches/leaves by the "days_late" field. I would want to resort the query in such a way that kept all of the branches intact but only the parts within a part get sorted while still keeping the same structure. For this example, my end-result goal would want to look like this:

    Code:
    id   item   level   part          days_late
    ------------------------------------------
    1    0.0    0       computer      100
    11   2.0    1        motherboard  100
    15   2.1    2         southbridge 100
    18   2.2    3          sb_part_3  100
    17   2.2    3          sb_part_2  75
    16   2.2    3          sb_part_1  0
    12   2.1    2         northbridge 50
    14   2.2    3          nb_part_2  50
    13   2.2    3          nb_part_1  30
    19   2.1    2         apu         10
    2    1.0    1        case         20
    4    1.1    2         led system  50
    6    1.2    3          led wire   50
    7    1.3    4           copper    50
    8    1.3    4           shielding 5
    9    1.2    3          connectors 20
    5    1.2    3          led light  10
    3    1.1    2         steel       0
    10   1.1    2         screws      0
    ...
    So.. can anyone look from the outside and maybe give me a tip on how I could do this by avoiding (or increasing performance) CONNECT BY method? Like I said above, it works great, but it is MUCH MUCH too slow for what I'm using it for...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Rhetorical question.....
    What is the good way to reduce run time for any SQL statement?
    Answer -
    execute dbms_system.set_ev(sid, serial#, 10046, 8, '');
    Then run the SQL in question
    Then run the *trc file thru TKPROF EXPLAIN=username/password
    to actually SEE where the time is being spent.
    Ideally the EXPLAIN PLAN will provide visibilty to add an appropriate index or such.
    Without facts, you might as well throw darts at board while hoping to hit something that will actually make the query faster.
    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
    May 2004
    Posts
    35
    Thanks for the tip! Unfortunately in my organization the folks that actually do the application developing using the Oracle server aren't the same folks as administor or have easy access to an admin. We are granted select / update / etc on a very limited basis.

    I won't go into the long answer, but the short answer is that I won't really be able to do anything like this. So what I'm looking for are the ideas as to what I could do to speed things up! If one thing doesn't work, I will try another, etc., until I can get it to work.

    So, what are some ideas?

    My front end for the time being is MS Access forms / reports. I was thinking, if I could find a way to execute PL/SQL statements from an MS Access passthrough query, I may be able to assign a new index number to each row based on the hierarhcical structure, but sorted the way I want to see the data. But the problem is, I can't figure out how to have Access to run the PL/SQL, it only wants to run SQL statements? Then I would have to learn enough PL/SQL to be able to do this... (but it should not be too much trouble I think)

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    Performance... well that's something else. You need to make sure that indexes exist for the columns in the start with and the connect by. Assuming:
    select...
    from ...
    start with product = 'computer'
    connect by prior child_part = parent_part

    in this case, you would want 2 indexes - one on product (to speed up the start with), and one on parent_part (to speed up the connect by). an index on child_part would not help the above query.

    if your connect by uses mulitple columns, DO NOT CONCATENATE them (common mistake)
    good: connect by prior c_part = p_part and prior c_loc = p_loc
    bad: connect by prior c_part||c_loc = p_part||p_loc
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    May 2004
    Posts
    35
    Thanks for the tips!

    I did a select on ALL_INDEXES and the fields I'm connecting on are not indexed. I've just sent in a DB Action Request to get CREATE INDEX ran on the fields that I will be needing.

    Also yes I'm running seperate = condition for every field, instead of concatenating the text.

    Thanks for the tip, though, I was unaware it could cause such a significant performance hit!

    Hopefully they will work the request ASAP and we can see how the performance goes!

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    make sure they analyze the tables and indexes after they create the indexes.
    otherwise you definitely will not see any improvement since Oracle will not
    know that indexes are available without the analyzing.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    May 2004
    Posts
    35
    Great news! They've already completed my request and the difference is huge

    Now the query executes in average time 0.8 seconds, whereas before it took average 13 minutes.



    Thanks again!

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    Oracle will know that the indexes are available, regardless of analyzing. It just won't be able to make intelligent decisions about choosing the optimal index, or which join order to use.

    The optimizer first checks that the tables in the sql exist, then checks which indexes exist, then starts looking at stats (and using assumptions when stats don't exist - the method varies by version), then weighs the cost of the permutations of join order, join method, indexes..., all as it relates to the current optimizer mode, many init.ora settings, and other things.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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