Excluding results that appear in another column of a CONNECT BY query

Question

Have a heavy query (takes 15 minutes to run), but it's returning more results than I need. It's a CONNECT BY query, and I'm getting nodes that are descendants in the root node results. I.E.:

Ted
  Bob
    John
Bob
  John
John

Normally, the way to resolve this is using a START WITH condition, typically requiring the parent of a node to be null. But due to the nature of the query, I don't have the START WITH values I need to compare to until I have the full resultset. I'm basically trying to double-query my results to say QUERY STUFF START WITH RECORDS THAT AREN'T IN THAT STUFF.

Here's the query (built with the help of Nicholas Krasnov, here: Oracle Self-Join on multiple possible column matches - CONNECT BY?):

select cudroot.root_user, cudroot.node_level, cudroot.user_id, cudroot.new_user_id,
       cudbase.*  -- Not really, just simplyfing
from   css.user_desc cudbase
  join (select connect_by_root(user_id) root_user,   
               user_id                  user_id,        
               new_user_id              new_user_id,
               level                    node_level
        from   (select cudordered.user_id,      
                       coalesce(cudordered.new_user_id, cudordered.nextUser) new_user_id
                from   (select cud.user_id, 
                               cud.new_user_id, 
                               decode(cud.global_hr_id, null, null, lead(cud.user_id ignore nulls) over (partition by cud.global_hr_id order by cud.user_id)) nextUser
                        from   css.user_desc cud
                          left join gsu.stg_userdata gstgu
                            on (gstgu.user_id = cud.user_id 
                                or (gstgu.sap_asoc_global_id = cud.global_hr_id))
                        where  upper(cud.user_type_code) in ('EMPLOYEE','CONTRACTOR','DIV_EMPLOYEE','DIV_CONTRACTOR','DIV_MYTEAPPROVED')) cudordered)
        connect by nocycle user_id = prior new_user_id) cudroot
    on cudbase.user_id = cudroot.user_id
order by 
       cudroot.root_user, cudroot.node_level, cudroot.user_id;

This gives me results about related users (based off of user_id renames or associated SAP IDs) that look like this:

ROOT_ID     LEVEL   USER_ID         NEW_USER_ID
------------------------------------------------
A5093522    1       A5093522        FG096489
A5093522    2       FG096489        A5093665
A5093522    3       A5093665        
FG096489    1       FG096489        A5093665
FG096489    2       A5093665
A5093665    1       A5093665

What I need is a way to filter the first join (select connect_by_root(user_id)... to exclude FG096489 and A5093665 from the root list.

The best START WITH I can think of would look like this (not tested yet):

start with user_id not in (select new_user_id 
                           from   (select coalesce(cudordered.new_user_id, cudordered.nextUser) new_user_id
                                   from   (select cud.new_user_id, 
                                                  decode(cud.global_hr_id, null, null, lead(cud.user_id ignore nulls) over (partition by cud.global_hr_id order by cud.user_id)) nextUser
                                           from   css.user_desc cud
                                           where  upper(cud.user_type_code) in ('EMPLOYEE','CONTRACTOR','DIV_EMPLOYEE','DIV_CONTRACTOR','DIV_MYTEAPPROVED')) cudordered)
                           connect by nocycle user_id = prior new_user_id)

... but I'm effectively executing my 15 minute query twice.

I've looked at using partitions in the query, but there's not really a partition... I want to look at the full resultset of new_user_ids. Have also explored analytical functions such as rank()... my bag of tricks is empty.

Any ideas?

Clarification

The reason I don't want the extra records in the root list is because I only want one group of results for each user. I.E., if Bob Smith has had four accounts during his career here (people come and go frequently, as employees and/or contractors), I want to work with a set of accounts that all belong(ed) to Bob Smith.

If Bob came here as an contractor, converted to an employee, left, came back as a contractor in another country, and left/returned to a legal org that is now in our SAP system, his account rename/chain might look like:

Bob Smith  CONTRACTOR   ----    US0T0001  ->  US001101  (given a new ID as an employee)
Bob Smith  EMPLOYEE     ----    US001101  ->  EB0T0001  (contractor ID for  the UK)
Bob Smith  CONTRACTOR  SAP001   EB0T000T                (no rename performed)
Bob Smith  EMPLOYEE    SAP001   TE110001                (currently-active ID)

In the above example, the four accounts are linked by either a new_user_id field that was set when the user was renamed or through having the same SAP ID.

Because HR frequently fails to follow the business process, returning users may end up with any of those four ID being restored to them. I have to analyze all the IDs for Bob Smith and say "Bob Smith can only have TE110001 restored", and kick back an error if they try to restore something else. I have to do it for 90,000+ records.

The first column, "Bob Smith", is just an identifier to the group of associated accounts. In my original example, I'm using the root User ID as the identifier (e.g. US0T0001). If I use first/last names to identify users, I end up with collisions.

So Bob Smith would look like this:

US0T0001  1  CONTRACTOR   ----    US0T0001  ->  US001101  (given a new ID as an employee)
US0T0001  2  EMPLOYEE     ----    US001101  ->  EB0T0001  (contractor ID for  the UK)
US0T0001  3  CONTRACTOR  SAP001   EB0T0001                (no rename performed)
US0T0001  4  EMPLOYEE    SAP001   TE110001                (currently-active ID)

... where 1, 2, 3, 4 are the levels in the heirarchy.

Since US0T0001, US001101, EB0T0001, and TE110001 are all accounted for, I don't want another group for them. But the results I have now have those accounts listed in multiple groups:

US001101  1  EMPLOYEE     ----    US001101  ->  EB0T0001  (
US001101  2  CONTRACTOR  SAP001   EB0T0001                
US001101  3  EMPLOYEE    SAP001   TE110001               

EB0T0001  1  CONTRACTOR  SAP001   EB0T0001               
EB0T0001  2  EMPLOYEE    SAP001   TE110001                

US001101  1  EMPLOYEE    SAP001   TE110001                 

This causes two problems:

1. When I query the results for a User ID, I get hits from multiple groups
2. Each group will report a different expected user ID for Bob Smith.

You asked for an expanded set of records... here are some actual data:

-- NumRootUsers tells me how many accounts are associated with a user.
-- The new user ID field is explicitly set in the database, but may be null.
-- The calculated new user ID analyzes records to determine what the next related record is

          NumRoot                   New User    Calculated
RootUser  Users    Level  UserId    ID Field    New User ID   SapId       LastName        FirstName
-----------------------------------------------------------------------------------------------
BG100502  3        1      BG100502  BG1T0873    BG1T0873                  GRIENS VAN      KION
BG100502  3        2      BG1T0873  BG103443    BG103443                  GRIENS VAN      KION
BG100502  3        3      BG103443                            41008318    VAN GRIENS      KION

-- This group causes bad matches for Kion van Griens... the IDs are already accounted for,
-- and this group doesn't even grab all of the accounts for Kion.  It's also using a new 
-- ID to identify the group
BG1T0873  2        1      BG1T0873  BG103443    BG103443                  GRIENS VAN      KION
BG1T0873  2        2      BG103443                            41008318    VAN GRIENS      KION

-- Same here...
BG103443  1        1      BG103443                            41008318    VAN GRIENS      KION

-- Good group of records 
BG100506  3        1      BG100506              BG100778      41008640    MALEN VAN       LARS
BG100506  3        2      BG100778              BG1T0877      41008640    MALEN VAN       LARS
BG100506  3        3      BG1T0877                            41008640    VAN MALEN       LARS

-- Bad, unwanted group of records
BG100778  2        1      BG100778              BG1T0877      41008640    MALEN VAN       LARS
BG100778  2        2      BG1T0877                            41008640    VAN MALEN       LARS

-- Third group for Lars
BG1T0877  1        1      BG1T0877                            41008640    VAN MALEN       LARS


-- Jan... fields are set differently than the above examples, but the chain is calculated correctly
BG100525  3        1      BG100525              BG1T0894      41008651    ZANWIJK VAN     JAN
BG100525  3        2      BG1T0894  TE035165    TE035165      41008651    VAN ZANWIJK     JAN
BG100525  3        3      TE035165                            41008651    VAN ZANWIJK     JAN

-- Bad
BG1T0894  2        1      BG1T0894  TE035165    TE035165      41008651    VAN ZANWIJK     JAN
BG1T0894  2        2      TE035165                            41008651    VAN ZANWIJK     JAN

-- Bad bad
TE035165  1        1      TE035165                            41008651    VAN ZANWIJK     JAN


-- Somebody goofed and gave Ziano a second SAP ID... but we still matched correctly
BG100527  3        1      BG100527              BG1T0896      41008652    STEFANI DE      ZIANO
BG100527  3        2      BG1T0896  TE033030    TE033030      41008652    STEFANI DE      ZIANO
BG100527  3        3      TE033030                            42006172    DE STEFANI      ZIANO

-- And we still got extra, unwanted groups
BG1T0896  3        2      BG1T0896  TE033030    TE033030      41008652    STEFANI DE      ZIANO
BG1T0896  3        3      TE033030                            42006172    DE STEFANI      ZIANO

TE033030  3        3      TE033030                            42006172    DE STEFANI      ZIANO


-- Mark's a perfect example of the missing/frustrating data I'm dealing with... but we still matched correctly
BG102188  3        1      BG102188              BG1T0543      41008250    BULINS          MARK
BG102188  3        2      BG1T0543              TE908583      41008250    BULINS          R.J.M.A.
BG102188  3        3      TE908583                            41008250    BULINS          RICHARD JOHANNES MARTINUS ALPHISIUS

-- Not wanted
BG1T0543  3        2      BG1T0543              TE908583      41008250    BULINS          R.J.M.A.
BG1T0543  3        3      TE908583                            41008250    BULINS          RICHARD JOHANNES MARTINUS ALPHISIUS

TE908583  3        3      TE908583                            41008250    BULINS          RICHARD JOHANNES MARTINUS ALPHISIUS


-- One more for good measure
BG1T0146  3        1      BG1T0146  BG105905    BG105905                  LUIJENT         VALERIE
BG1T0146  3        2      BG105905              TE034165      42006121    LUIJENT         VALERIE
BG1T0146  3        3      TE034165                            42006121    LUIJENT         VALERIE

BG105905  3        2      BG105905              TE034165      42006121    LUIJENT         VALERIE
BG105905  3        3      TE034165                            42006121    LUIJENT         VALERIE

TE034165  3        3      TE034165                            42006121    LUIJENT         VALERIE

Not sure if all that info makes it clearer or will make your eyes roll back into your head : )

Thanks for looking at this!

Answer 1

I think I have it. We have allowed ourselves to become fixated on the chronological order whereas in fact it doesn't matter. Your START WITH clause should be 'NEW_USER_ID IS NULL'.

To get chronological order you could 'ORDER BY cudroot.node_level * -1'.

I would also recommend that you look at using a WITH clause to form your base data and perform the heirarchical query on that.