-
Type: Improvement
-
Status: Resolved
-
Priority: Minor
-
Resolution: Fixed
-
Affects Version/s: 5.9.1
-
Fix Version/s: 5.8.0-HF07, 5.9.2
-
Component/s: Core VCS
-
Tags:
A part of the nx_update_read_acls is doing TABLE ACCESS FULL on hierarchy and hierarchy_read_acls.
SELECT /*+ gather_plan_statistics */ r.id FROM nuxeo.hierarchy_read_aclr JOIN nuxeo.hierarchy h ON h.id = r.id JOIN nuxeo.hierarchy_read_acl rr ON rr.id = h.parentid WHERE r.acl_id <> '-' AND rr.acl_id = '-'
Plan hash value: 4002556013 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:12.55 | 77143 | 70036 | | | | |* 1 | HASH JOIN | | 1 | 18549 | 0 |00:00:12.55 | 77143 | 70036 | 894K| 894K| 352K (0)| |* 2 | HASH JOIN | | 1 | 19538 | 1 |00:00:07.74 | 48782 | 41703 | 894K| 894K| 355K (0)| | 3 | TABLE ACCESS BY INDEX ROWID| HIERARCHY_READ_ACL | 1 | 9014 | 1 |00:00:00.01 | 5 | 0 | | | | |* 4 | INDEX RANGE SCAN | HIERARCHY_READ_ACL_ACL_ID_IDX | 1 | 9014 | 1 |00:00:00.01 | 4 | 0 | | | | | 5 | TABLE ACCESS FULL | HIERARCHY | 1 | 2746K| 2873K|00:00:14.37 | 48777 | 41703 | | | | |* 6 | TABLE ACCESS FULL | HIERARCHY_READ_ACL | 1 | 2607K| 2715K|00:00:21.74 | 28361 | 28333 | | | |
We can try to use CONNECT BY to prevent this.