StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery taking hell lot of time
text
Body
copied!<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 > 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 > 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 > 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 > 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 > 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 >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 > 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload