Details
-
Bug
-
Resolution: Done
-
Major
-
None
-
None
-
2020 Week 19-21 (from May 4), 2020 Week 22-24 (from May 25)
Description
Current version of oracle has a problem trying to execute this query
SELECT po.task_id
FROM PeopleAssignments_PotOwners po
WHERE po.entity_id = 'salaboy'
GROUP BY po.task_id
HAVING COUNT(po.entity_id) = 1;
current execution plan shows
EXPLAIN PLAN SET STATEMENT_ID = 'perf_analysis' into plan_table FOR SELECT po.task_id FROM PeopleAssignments_PotOwners po WHERE po.entity_id = 'salaboy' GROUP BY po.task_id HAVING COUNT(po.entity_id) = 1; SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'perf_analysis','ALL'));
current
PLAN_TABLE_OUTPUT
----------------------------------------------------
Plan hash value: 2089586768
--------------------------------------------------
| Id | Operation | Name
--------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | FILTER |
| 2 | SORT GROUP BY NOSORT |
|* 3 | TABLE ACCESS BY INDEX ROWID| PEOPLEASSI
| 4 | INDEX FULL SCAN | IDX_PASPOT
--------------------------------------------------
Query Block Name / Object Alias (identified by ope
--------------------------------------------------
1 - SEL$1
3 - SEL$1 / PO@SEL$1
4 - SEL$1 / PO@SEL$1
Predicate Information (identified by operation id)
--------------------------------------------------
1 - filter(COUNT(*)=1)
3 - filter("PO"."ENTITY_ID"='salaboy')
Column Projection Information (identified by opera
--------------------------------------------------
1 - "PO"."TASK_ID"[NUMBER,22]
2 - (#keys=1) "PO"."TASK_ID"[NUMBER,22], COUNT(
3 - "PO"."TASK_ID"[NUMBER,22]
4 - "PO".ROWID[ROWID,10], "PO"."TASK_ID"[NUMBER
current ddl index
CREATE INDEX idx_paspot_entity ON PeopleAssignments_PotOwners (entity_id); CREATE INDEX idx_paspot_task ON PeopleAssignments_PotOwners (task_id);