title: Query taking hell lot of time
body: <p>our system is quite big having around 100+ tables in schema. There was one business requirement for which we decided to handle it in stored procedures. In our application we have java as well but we chosen to use Stored Procedure. IN order to fulfill requirement created 3 functions.
  These functions are part of SQL query which get fired to fetch count and display records. Now we have SQL timeout set in connection which is set to 60 seconds. 
  when we run this query for records of 40K, the operation gets timed out and we don't get any result. 
  I am pasting here actual implementation of all these 3 functions and below them is query who makes use of all these created function. </p>
  
  <p>In query below have marked function calling in Bold letter.</p>
  
  <p>I am looking at your expert advice by which i can tune this implementation to work on 100K of data. 
  we have oracle 11gR1..  Even this implementation is access from web in Ajax call.</p>
  
  <p>Do let me know if you need anything else.</p>
  
  <pre><code>CREATE INDEX IDX_SNM_ENTITY_NAME
    ON SML_NOTIFICATION_MAIN(ENTITY_NAME)
    TABLESPACE CM_INDEX;
  
  CREATE OR REPLACE FUNCTION is_Users_In_Same_Business_Unit
      (UserMasterId varchar2,
       CtoCode varchar2)       
    RETURN boolean
  IS 
    v_count number(4);       
  BEGIN
    if CtoCode is not null then       
      select count(*) into v_count
        from sml_user u,
             sml_team_member tm,
             sml_usrprof_cto_map ctomap,
             code_value cv
        where u.status = 'ACTIVE'
          and u.master_id is null
          and u.id = tm.user_id
          and tm.profile_id = ctomap.user_profile_id
          and ctomap.cto_code_id = cv.id
          and cv.code_value = CtoCode
          and tm.team_id in
                (select child_id
                   from vw_team_relation_master a
                   connect by prior a.child_id = a.parent_id
                   start with a.child_id =
                     (select child_id
                        from vw_team_relation_master c
                        where parent_id is null
                        start with c.child_id =
                          (select t.id
                             from sml_team t,
                                  sml_team_member tm
                             where t.id = tm.team_id
                               and t.status = 'ACTIVE'
                               and t.master_id is null
                               and tm.user_id = UserMasterId) 
                        connect by prior c.parent_id = c.child_id));
  
      if v_count is not null and v_count &gt; 0 then
        return TRUE;
      else
        return FALSE;
      end if;       
    end if;        
  
    return FALSE;           
  END;
  
  CREATE OR REPLACE FUNCTION Can_User_Access_Customer(
        NotificationDesc            VARCHAR2,
        UserId                      VARCHAR2,
        NotificationType            VARCHAR2,
        UserRoleCode                VARCHAR2,
        UserMasterId                VARCHAR2,
        CtoCode                     VARCHAR2,
        SecCtoCode                  VARCHAR2,
        UserProfTempIdCommercialInd VARCHAR2,
        UserProfTempIdCorporateInd  VARCHAR2,
        UserProfTempStcfInd         VARCHAR2,
        UserProfTempIdGsamInd       VARCHAR2)
      RETURN BOOLEAN
  IS
    -- function to test is user can access customer
    regSearchCount                number(2);
    in_same_unit                  boolean;
    count_v                       number(2);
  BEGIN
    in_same_unit := is_Users_In_Same_Business_Unit(UserMasterId, CtoCode);
  
    -- Commercial    
  
    select count(*)
      into regSearchCount
      from dual
      where regexp_like (CtoCode,'200[3-9]|20[1-4][0-9]|2[1-6][0-6][0-9]|29[0-6][0-9]|300[1-9]|30[1-6][0-9]|3[1-9][0-6][0-9]');        
  
    IF regSearchCount &gt; 0 then -- user is commercial
      -- check secondary cto code holder
      select count(*)
        into count_v
        from dual
        where SecCtoCode IN
          (SELECT DISTINCT code_value
             FROM sml_user,
                  sml_team_member,
                  sml_user_profile,
                  sml_usrprof_cto_map,
                  code_value
             WHERE sml_user.id = sml_team_member.user_id
               AND sml_team_member.profile_id = sml_user_profile.id
               AND sml_user_profile.id = sml_usrprof_cto_map.user_profile_id
               AND sml_usrprof_cto_map.cto_code_id = code_value.id
               AND sml_user.login_id = UserId);
  
      IF count_v is not null AND
         count_v &gt; 0
      then
        return TRUE;
      END IF;
  
      IF NotificationType='Collateral' AND
         UserRoleCode is not null AND
         UserRoleCode='RLOC'
      then -- user is rloc
        RETURN TRUE;
      END IF;
  
      IF in_same_unit AND
         UserRoleCode is not null AND
         UserRoleCode IN ('RMTL','GRPHD','SECHD')
      then -- user belongs to GH, SH etc          
        RETURN TRUE;
      END IF;
    END IF;     
  
    -- corporate
  
    select count(*)
      into regSearchCount
      from dual
      where regexp_like (CtoCode,'1[0-9][0-6][0-9]');
  
    IF regSearchCount &gt; 0 then
      IF in_same_unit and UserRoleCode is not null AND
         UserRoleCode='GRPHD' AND
         NotificationDesc!='0 days Due for expiry of Collateral'
       THEN -- user belongs to GH          
          RETURN TRUE;                    
      END IF;
  
      IF in_same_unit AND
         UserRoleCode is not null AND
         UserRoleCode='RMTL' AND
         NotificationDesc='0 days Due for expiry of Collateral'
      THEN
          RETURN TRUE;                    
      END IF;
    END IF;    
  
    -- STCF     
  
    select count(*)
      into regSearchCount
      from dual
      where regexp_like (CtoCode, '20[5-6][0-9]');
  
    IF regSearchCount &gt; 0 then      
      IF in_same_unit AND
         UserRoleCode is not null AND
         UserRoleCode = 'RMTL'
      then
        RETURN TRUE;          
      END IF;
    END IF;    
  
    RETURN FALSE ;
  END;
  
  create or replace FUNCTION IS_NOTIFICATION_ACCESS_PASSED(
        NotificationDesc            VARCHAR2,
        UserId                      VARCHAR2,
        NotificationType            VARCHAR2,
        UserMasterId                VARCHAR2,
        UserRoleCode                VARCHAR2,
        CtoCode                     VARCHAR2,
        SecCtoCode                  VARCHAR2,
        UserProfTempIdCommercialInd VARCHAR2,
        UserProfTempIdCorporateInd  VARCHAR2,
        UserProfTempStcfInd         VARCHAR2,
        UserProfTempIdGsamInd       VARCHAR2)
      RETURN VARCHAR2 DETERMINISTIC
  IS
    -- functions to test, is instance of notification viewable for the logged in user, on the basis of notification type
    -- and other user details etc, currently defined for only type 'Collateral' and 'Implementation Instruction'
  
    regSearchCount NUMBER(2);
    count_v NUMBER(2);       
  BEGIN
    IF NotificationType = 'Implementation Instruction' THEN
      IF Can_User_Access_Customer(NotificationDesc,UserId,
                                  NotificationType
                                  UserRoleCode,UserMasterId
                                  CtoCode,SecCtoCode,
                                  UserProfTempIdCommercialInd,
                                  UserProfTempIdCorporateInd,
                                  UserProfTempStcfInd,
                                  UserProfTempIdGsamInd)
      THEN
        IF UserRoleCode = 'LDU' THEN
          SELECT COUNT(*)
            INTO regSearchCount
            FROM dual
            WHERE regexp_like (CtoCode,'1[0-9][0-6][0-9]');
  
          IF regSearchCount &gt;0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'FALSE';
          END IF;
        ELSE
          RETURN 'TRUE';
        END IF;
      END IF;
    END IF;
  
    IF NotificationType = 'Collateral' THEN
      IF UserRoleCode   ='WLOC' THEN
        RETURN 'TRUE';
      END IF;
  
      -- check primary cto code holder
  
      select count(*)
        into count_v
        from dual
        where CtoCode IN
          (SELECT DISTINCT code_value
             FROM sml_user,
                  sml_team_member,
                  sml_user_profile,
                  sml_usrprof_cto_map,
                  code_value
             WHERE sml_user.id = sml_team_member.user_id
               AND sml_team_member.profile_id = sml_user_profile.id
               AND sml_user_profile.id = sml_usrprof_cto_map.user_profile_id
               AND sml_usrprof_cto_map.cto_code_id = code_value.id
               AND sml_user.login_id = UserId);
  
      IF count_v is not null and count_v &gt; 0 then
        return 'TRUE';
      END IF;
  
      -- check all other conditions
  
      IF Can_User_Access_Customer(NotificationDesc,UserId,NotificationType,
                                  UserRoleCode,UserMasterId,CtoCode,
                                  SecCtoCode,UserProfTempIdCommercialInd,
                                  UserProfTempIdCorporateInd,
                                  UserProfTempStcfInd,
                                  UserProfTempIdGsamInd) THEN
        RETURN 'TRUE';
      END IF;
    ELSE
      RETURN 'TRUE';
    END IF;
  
    RETURN 'FALSE';
  END;
  </code></pre>
  
  <p>The query:</p>
  
  <pre><code>select *
    FROM SML_NOTIFICATION_MAIN notification
    where notification.DEPRECATED='N'  and
          (MAKER_ID = 'see_rm'  OR
           TO_CHAR(CTO_CODE) IN
             (SELECT DISTINCT code_value
                FROM sml_user, 
                     sml_team_member,
                     sml_user_profile,
                     sml_usrprof_cto_map,
                     code_value
                WHERE sml_user.id = sml_team_member.user_id AND
                      sml_team_member.profile_id = sml_user_profile.id AND
                      sml_user_profile.id = sml_usrprof_cto_map.user_profile_id AND
                      sml_usrprof_cto_map.cto_code_id = code_value.id AND
                      sml_user.login_id = 'see_rm' AND
                      Entity_Name != 'Collateral') OR
          TO_CHAR(SEC_CTO_CODE) IN
            (SELECT DISTINCT code_value
               FROM sml_user, 
                    sml_team_member,
                    sml_user_profile,
                    sml_usrprof_cto_map,
                    code_value 
               WHERE sml_user.id = sml_team_member.user_id AND 
                     sml_team_member.profile_id = sml_user_profile.id AND
                     sml_user_profile.id = sml_usrprof_cto_map.user_profile_id AND
                     sml_usrprof_cto_map.cto_code_id = code_value.id AND
                     Entity_Name != 'Collateral' AND
                     sml_user.login_id ='see_rm') OR
          ROLE_CODE in (select distinct CODE
                          from sml_user a,
                               code_value b
                          where a.role_id = b.id AND
                                login_id = 'see_rm') OR
          SUBSTR(ROLE_CODE, 1, 4) in (select distinct CODE
                                      from sml_user a,
                                           code_value b
                                      where a.role_id = b.id AND
                                            login_id = 'see_rm') OR
          SUBSTR(ROLE_CODE, 6, 4) IN (select distinct CODE
                                        from sml_user a,
                                             code_value b
                                        where ENTITY_NAME = 'Insurance' OR
                                              ENTITY_NAME = 'Collateral' AND
                                              a.role_id = b.id AND
                                              login_id = 'see_rm') OR
          SUBSTR(ROLE_CODE, 11, 2) IN (select distinct CODE
                                         from sml_user a,
                                              code_value b
                                         where a.role_id = b.id AND
                                               login_id = 'see_rm') OR 
          TEAM_LEAD in (select to_char(a.id)
                          from sml_user a
                          where Entity_Name != 'Collateral' AND
                                a.login_id = 'see_rm') OR
          RM_ID in (select to_char(a.id)
                      from sml_user a
                      where Entity_Name != 'Collateral' AND
                            ENTITY_NAME = 'PostApproval CP/Covnent' AND
                            DESCRIPTION != '30 days before due date' AND
                            a.login_id = 'see_rm') OR
          (Entity_Name != 'Collateral' AND
           RM_ID in (Select Rm_Id
                       From Vw_Sm_Rm_Team_Lead
                       Where Vw_Sm_Rm_Team_Lead.Rmtl_Id IN
                         (Select To_Char(A.Id)
                            From Sml_User A
                            Where A.Login_Id = 'see_rm'))) OR
          (Entity_Name = 'Enquiry' AND
           RM_ID IN (SELECT BCA_ID
                       FROM VW_SM_BCA_TEAM_LEAD
                       WHERE VW_SM_BCA_TEAM_LEAD.BCATL_ID IN
                         (SELECT TO_CHAR(A.Id)
                            FROM Sml_User A 
                            WHERE A.Login_Id = 'see_rm'))) OR
           GROUP_HEAD in (select to_char(a.id)
                            from sml_user a
                            where a.login_id = 'see_rm' AND
                                  Entity_Name != 'Collateral') OR
          (Entity_name != 'Collateral' AND
           SECTOR_HEAD like ('%,'|| (select to_char(a.id) || ',' as ID
                                       from sml_user a
                                       where a.login_id = 'see_rm' AND
                                             status='ACTIVE' AND
                                             master_id is null) || '%')) OR
          (Entity_Name = 'Collateral' AND
           IS_NOTIFICATION_ACCESS_PASSED(DESCRIPTION,
                                         'see_rm',
                                         Entity_Name,
                                         '1176',
                                         'RM',
                                         TO_CHAR(CTO_CODE),
                                         TO_CHAR(SEC_CTO_CODE),
                                         'N','N','N','N') = 'TRUE') OR
          (RM_ID in (select rm_id
                       from VW_SM_RM_TEAM_LEAD
                       where rmtl_id IN (Select Distinct Su.Id As Rmtl_Id
                                           FROM sml_user su
                                           INNER JOIN sml_team_member MEMBER
                                             ON su.id=member.user_id
                                           Inner Join Sml_Team Team
                                             On Member.Team_Id=Team.Id AND
                                                Team.Team_Role_Id =
                                                  (SELECT ID
                                                     FROM code_value
                                                     WHERE code='RMTL' AND
                                                           status='ACTIVE' AND
                                                           master_id IS NOT NULL AND
                                                           code_set_value_id =
                                                             (SELECT ID
                                                                FROM code_set
                                                                WHERE CODE = 'TEAM_ROLE' AND
                                                                      master_id IS NOT NULL))
                                           Inner Join Sml_Team_Child PARENT
                                             On Team.Id=Parent.Child_Id
                                           INNER JOIN sml_team_member grop
                                             ON grop.team_id=parent.parent_id
                                           INNER JOIN sml_team st
                                             ON st.id=grop.team_id
                                           Where Su.Status = 'ACTIVE' AND
                                                 St.Status='ACTIVE' AND
                                                 grop.user_id =
                                                   (select to_char(a.id)
                                                      from sml_user a
                                                      where a.master_id is null AND
                                                            a.status='ACTIVE' AND
                                                            a.login_id = 'see_rm'))) AND
           Entity_Name != 'Collateral' AND
           ((Entity_Name = 'Implementation Instruction' AND
             IS_NOTIFICATION_ACCESS_PASSED(DESCRIPTION,
                                           'see_rm',
                                           Entity_Name,
                                           '1176',
                                           'RM',
                                           TO_CHAR(CTO_CODE),
                                           TO_CHAR(SEC_CTO_CODE),
                                           'N','N','N','N') = 'TRUE') OR 
            (Entity_Name = 'Post Disbursement') OR
            (ENTITY_NAME = 'PostApproval CP/Covnent' AND
             DESCRIPTION = '7 days before due date') OR
            (ENTITY_NAME = 'Facility' AND
             DESCRIPTION != '2 Days Due for expiry of Facility') OR
            (ENTITY_NAME = 'Insurance') OR
            (ENTITY_NAME = 'Call Report' AND
             DESCRIPTION = '2 days before exceeded by 11 months'))))
  
  ****************************************Execution plan of above query *****************
  
  Execution Plan
  ----------------------------------------------------------
     0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=294 Card=17 K Bytes=3 M)
     1    0    FILTER
     2    1      TABLE ACCESS FULL UOBCM.SML_NOTIFICATION_MAIN (Cost=294 Card=37 K Bytes=6 M)
     3    1      FILTER
     4    3        NESTED LOOPS
     5    4          NESTED LOOPS (Cost=47 Card=1 Bytes=80)
     6    5            HASH JOIN (Cost=23 Card=24 Bytes=1 K)
     7    6              NESTED LOOPS (Cost=8 Card=12 Bytes=432)
     8    7                HASH JOIN (Cost=8 Card=12 Bytes=384)
     9    8                  TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=3 Bytes=72)
    10    9                    INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    11    8                  INDEX FAST FULL SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=3 Card=1 K Bytes=8 K)
    12    7                INDEX UNIQUE SCAN UOBCM.PK_SML_USER_PROFILE (Cost=0 Card=1 Bytes=4)
    13    6              VIEW UOBCM.index$_join$_005 (Cost=15 Card=1 K Bytes=14 K)
    14   13                HASH JOIN
    15   14                  INDEX FAST FULL SCAN UOBCM.IDX_USRPROFCTO_USRPROF (Cost=6 Card=1 K Bytes=14 K)
    16   14                  INDEX FAST FULL SCAN UOBCM.IDX_USRPROFCTO_CTO (Cost=11 Card=1 K Bytes=14 K)
    17    5            INDEX UNIQUE SCAN UOBCM.PK_CODE_VALUE (Cost=0 Card=1)
    18    4          TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=1 Card=1 Bytes=36)
    19    1      FILTER
    20   19        NESTED LOOPS
    21   20          NESTED LOOPS (Cost=47 Card=1 Bytes=80)
    22   21            HASH JOIN (Cost=23 Card=24 Bytes=1 K)
    23   22              NESTED LOOPS (Cost=8 Card=12 Bytes=432)
    24   23                HASH JOIN (Cost=8 Card=12 Bytes=384)
    25   24                  TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=3 Bytes=72)
    26   25                    INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    27   24                  INDEX FAST FULL SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=3 Card=1 K Bytes=8 K)
    28   23                INDEX UNIQUE SCAN UOBCM.PK_SML_USER_PROFILE (Cost=0 Card=1 Bytes=4)
    29   22              VIEW UOBCM.index$_join$_010 (Cost=15 Card=1 K Bytes=14 K)
    30   29                HASH JOIN
    31   30                  INDEX FAST FULL SCAN UOBCM.IDX_USRPROFCTO_USRPROF (Cost=6 Card=1 K Bytes=14 K)
    32   30                  INDEX FAST FULL SCAN UOBCM.IDX_USRPROFCTO_CTO (Cost=11 Card=1 K Bytes=14 K)
    33   21            INDEX UNIQUE SCAN UOBCM.PK_CODE_VALUE (Cost=0 Card=1)
    34   20          TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=1 Card=1 Bytes=36)
    35    1      NESTED LOOPS
    36   35        NESTED LOOPS (Cost=5 Card=1 Bytes=39)
    37   36          TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=3 Card=3 Bytes=75)
    38   37            INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    39   36          INDEX UNIQUE SCAN UOBCM.PK_CODE_VALUE (Cost=0 Card=1)
    40   35        TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=1 Card=1 Bytes=14)
    41    1      NESTED LOOPS
    42   41        NESTED LOOPS (Cost=5 Card=1 Bytes=39)
    43   42          TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=3 Card=3 Bytes=75)
    44   43            INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    45   42          INDEX UNIQUE SCAN UOBCM.PK_CODE_VALUE (Cost=0 Card=1)
    46   41        TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=1 Card=1 Bytes=14)
    47    1      NESTED LOOPS (Cost=9 Card=3 Bytes=117)
    48   47        TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=4 Card=4 Bytes=56)
    49   48          INDEX RANGE SCAN UOBCM.IDX_CV_CDSTATUSID (Cost=2 Card=4)
    50   47        TABLE ACCESS FULL UOBCM.SML_USER (Cost=5 Card=2 Bytes=50)
    51    1      NESTED LOOPS
    52   51        NESTED LOOPS (Cost=5 Card=1 Bytes=39)
    53   52          TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=3 Card=3 Bytes=75)
    54   53            INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    55   52          INDEX UNIQUE SCAN UOBCM.PK_CODE_VALUE (Cost=0 Card=1)
    56   51        TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=1 Card=1 Bytes=14)
    57    1      FILTER
    58   57        TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=1 Bytes=24)
    59   58          INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    60    1      FILTER
    61   60        TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=1 Bytes=24)
    62   61          INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    63    1      FILTER
    64   63        TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=1 Bytes=24)
    65   64          INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    66    1      NESTED LOOPS
    67   66        NESTED LOOPS (Cost=28 Card=1 Bytes=95)
    68   67          HASH JOIN (Cost=27 Card=1 Bytes=76)
    69   68            NESTED LOOPS
    70   69              NESTED LOOPS (Cost=24 Card=7 Bytes=476)
    71   70                NESTED LOOPS (Cost=16 Card=8 Bytes=472)
    72   71                  HASH JOIN (Cost=10 Card=12 Bytes=612)
    73   72                    NESTED LOOPS (Cost=6 Card=3 Bytes=129)
    74   73                      TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=2 Card=1 Bytes=19)
    75   74                        INDEX UNIQUE SCAN UOBCM.PK_SML_USER (Cost=1 Card=1)
    76   73                      TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=3 Bytes=72)
    77   76                        INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    78   72                    INDEX FAST FULL SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=3 Card=1 K Bytes=8 K)
    79   71                  TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM_CHILD (Cost=1 Card=1 Bytes=8)
    80   79                    INDEX RANGE SCAN UOBCM.IDX_TCHILD_TEAM (Cost=0 Card=1)
    81   70                INDEX UNIQUE SCAN UOBCM.PK_SML_TEAM (Cost=0 Card=1)
    82   69              TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM (Cost=1 Card=1 Bytes=9)
    83   82                TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=3 Card=1 Bytes=35)
    84   83                  INDEX RANGE SCAN UOBCM.IDX_CV_CS (Cost=1 Card=19)
    85   84                    TABLE ACCESS FULL UOBCM.CODE_SET (Cost=18 Card=1 Bytes=38)
    86   68            INDEX FAST FULL SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=3 Card=4 Bytes=32)
    87   67          INDEX UNIQUE SCAN UOBCM.PK_SML_TEAM (Cost=0 Card=1)
    88   66        TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM (Cost=1 Card=1 Bytes=19)
    89    1      TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=1 Bytes=43)
    90   89        INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
    91    1      NESTED LOOPS
    92   91        NESTED LOOPS (Cost=31 Card=1 Bytes=142)
    93   92          NESTED LOOPS (Cost=30 Card=1 Bytes=123)
    94   93            NESTED LOOPS (Cost=29 Card=1 Bytes=104)
    95   94              NESTED LOOPS (Cost=26 Card=3 Bytes=288)
    96   95                NESTED LOOPS (Cost=22 Card=4 Bytes=348)
    97   96                  HASH JOIN (Cost=20 Card=5 Bytes=395)
    98   97                    NESTED LOOPS
    99   98                      NESTED LOOPS (Cost=16 Card=4 Bytes=284)
   100   99                        NESTED LOOPS (Cost=12 Card=4 Bytes=208)
   101  100                          NESTED LOOPS (Cost=10 Card=2 Bytes=88)
   102  101                            NESTED LOOPS (Cost=7 Card=3 Bytes=105)
   103  102                              NESTED LOOPS (Cost=5 Card=4 Bytes=108)
   104  103                                TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=2 Card=1 Bytes=19)
   105  104                                  INDEX UNIQUE SCAN UOBCM.PK_SML_USER (Cost=1 Card=1)
   106  103                                INDEX FAST FULL SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=3 Card=4 Bytes=32)
   107  106                                  TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=4 Card=1 Bytes=43)
   108  107                                    INDEX RANGE SCAN UOBCM.IDX_USR_LOGINID (Cost=1 Card=3)
   109  102                              TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM_CHILD (Cost=1 Card=1 Bytes=8)
   110  109                                INDEX RANGE SCAN UOBCM.IDX_TCHILD_TEAM (Cost=0 Card=1)
   111  101                            TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM (Cost=1 Card=1 Bytes=9)
   112  111                              INDEX UNIQUE SCAN UOBCM.PK_SML_TEAM (Cost=0 Card=1)
   113  111                              TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=3 Card=1 Bytes=35)
   114  113                                INDEX RANGE SCAN UOBCM.IDX_CV_CS (Cost=1 Card=19)
   115  114                                  TABLE ACCESS FULL UOBCM.CODE_SET (Cost=18 Card=1 Bytes=38)
   116  100                          INDEX RANGE SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=1 Card=2 Bytes=16)
   117   99                        INDEX UNIQUE SCAN UOBCM.PK_SML_USER (Cost=0 Card=1)
   118   98                      TABLE ACCESS BY INDEX ROWID UOBCM.SML_USER (Cost=1 Card=1 Bytes=19)
   119   97                    INDEX FAST FULL SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=3 Card=1 K Bytes=8 K)
   120   96                  TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM_CHILD (Cost=1 Card=1 Bytes=8)
   121  120                    INDEX RANGE SCAN UOBCM.IDX_TCHILD_TEAM (Cost=0 Card=1)
   122   95                TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM (Cost=1 Card=1 Bytes=9)
   123  122                  INDEX UNIQUE SCAN UOBCM.PK_SML_TEAM (Cost=0 Card=1)
   124  122                  TABLE ACCESS BY INDEX ROWID UOBCM.CODE_VALUE (Cost=3 Card=1 Bytes=35)
   125  124                    INDEX RANGE SCAN UOBCM.IDX_CV_CS (Cost=1 Card=19)
   126  125                      TABLE ACCESS FULL UOBCM.CODE_SET (Cost=18 Card=1 Bytes=38)
   127   94              INDEX RANGE SCAN UOBCM.IDX_TMBR_TMUSERPF (Cost=1 Card=1 Bytes=8)
   128   93            TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM (Cost=1 Card=1 Bytes=19)
   129  128              INDEX UNIQUE SCAN UOBCM.PK_SML_TEAM (Cost=0 Card=1)
   130   92          INDEX UNIQUE SCAN UOBCM.PK_SML_TEAM (Cost=0 Card=1)
   131   91        TABLE ACCESS BY INDEX ROWID UOBCM.SML_TEAM (Cost=1 Card=1 Bytes=19)
   132    1      NESTED LOOPS
   133  132        NESTED LOOPS (Cost=28 Card=1 Bytes=95)
   134  133          HASH JOIN (Cost=27 Card=1 Bytes=76)
   135  134            NESTED LOOPS
   136  135              NESTED LOOPS (Cost=24 Card=7 Bytes=476)
   137  136                NESTED LOOPS (
  </code></pre>