Hi Friends ,
Could you please help me reduce the cost of this query or how more effectively can i rewrite this query .
db :- 12.1 , OS - OEL 7
WITH tmp1 AS (
SELECT /*+ FIRST_ROWS(10) */
project_template_link.project_id
FROM
wfmt_data.project_template
inner join wfmt_data.project_template_link using(template_version,template_name)
where project_template.application = 'EES'
),
tmp2 AS (
SELECT /*+ FIRST_ROWS(10) */
pme.process processid
FROM
wfmt_core.process_model_entry pme,
tmp1
WHERE
pme.name = 'PROJ_ID'
AND tmp1.project_id = pme.value
)
SELECT /*+ FIRST_ROWS(10) */
q.queue_name,
q.description,
wi.item_status
FROM
wfmt_core.queue q,
wfmt_core.work_items wi,
wfmt_core.case c,
wfmt_core.process p,
wfmt_core.work_items_queue_map wiqm,
tmp2
WHERE
tmp2.processid = p.processid
AND p.processinstanceid = c.process_key
AND c.casenum = wi.casenum
AND wi.work_item_id = wiqm.work_item_id
AND wiqm.queue_name = q.queue_name
AND wi.item_status IN ( 'ASSIGNED', 'DELAYED', 'ESCALATED', 'NEW' );
Plan hash value: 1728370568
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 1660 | 2412 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 1660 | 2412 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PROJECT_TEMPLATE | 76 | 2356 | 36 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 67 | 12864 | 2376 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 67 | 12864 | 2376 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 6 | 996 | 2370 (1)| 00:00:01 |
| 6 | NESTED LOOPS | | 67 | 9715 | 2297 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 51 | 6426 | 2195 (1)| 00:00:01 |
| 8 | NESTED LOOPS | | 190 | 19570 | 1158 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 190 | 11970 | 398 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | WORK_ITEMS | 167K| 3598K| 18 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | CASE | 1 | 41 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | CASE_PK | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| PROCESS | 1 | 40 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_PROCESS_PROCINSTID | 1 | | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | PROCESS_MODEL_ENTRY | 1 | 23 | 6 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_PROCMODELENTRY_PROC | 57 | | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | WIS_Q_MAP_Q_PK | 1 | 19 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PROJECT_TEMPLATE_LINK | 1 | 21 | 2 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PROJECT_TEMPLATE_LINK_PK | 1 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | QUEUE_PK | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | QUEUE | 11 | 286 | 1 (0)| 00:00:01 |
Regards,
DBApps