| SQL_ID g1y807c7cp5z1, child number 0 |
| ------------------------------------- |
| select /*+ gather_plan_statistics */ q.question_id activity_id, |
| ot.resource_name title, u.display_name, t.text AS |
| description, '<span class="init-List-badge u-color">Quiz</span>' |
| activity_type, 'javascript:apex.submit({request:''CREATE_PRACT |
| ICE'',set:{''P300_QUESTION_ID'':' || q.question_id || |
| ',''P300_TOPIC_TITLE'':''' || t.text || |
| ''',''P300_DOMAIN_ID'':' || t.domain_id || '}});' |
| link, '<span class="init-List-badge ' || |
| d.abbreviation || '">' || d.abbreviation || |
| '</span>' domain, dl.text difficulty FROM |
| qdb_questions q, ov.ov_resource_topics_mv ot, qdb_users |
| u, qdb_topics t, qdb_domains d, |
| qdb_difficulty_levels dl WHERE q.ch_content_id = ot.resource_id |
| AND q.author_id = u.user_id AND q.topic_id = t.topic_id |
| AND t.domain_id = d.domain_id AND q.difficulty_id = |
| dl.difficulty |
| |
| Plan hash value: 2518036998 |
| |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | | 21 |00:00:26.07 | 52613 | 9557 | | | | |
| | 1 | UNION-ALL | | 1 | | 21 |00:00:26.07 | 52613 | 9557 | | | | |
| | 2 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52585 | 9557 | | | | |
| | 3 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52576 | 9557 | | | | |
| | 4 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52567 | 9557 | | | | |
| | 5 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52558 | 9557 | | | | |
| | 6 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52549 | 9557 | | | | |
| |* 7 | HASH JOIN | | 1 | 9 | 21 |00:00:26.07 | 52511 | 9557 | 840K| 840K| 411K (0)| |
| |* 8 | HASH JOIN | | 1 | 9 | 21 |00:00:26.07 | 52509 | 9557 | 876K| 876K| 418K (0)| |
| | 9 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52502 | 9557 | | | | |
| | 10 | NESTED LOOPS | | 1 | 9 | 21 |00:00:26.07 | 52481 | 9557 | | | | |
| |* 11 | HASH JOIN | | 1 | 9 | 21 |00:00:26.07 | 52458 | 9557 | 1015K| 1015K| 659K (0)| |
| |* 12 | HASH JOIN | | 1 | 9 | 21 |00:00:26.04 | 52450 | 9557 | 1096K| 1096K| 1021K (0)| |
| |* 13 | MAT_VIEW ACCESS BY INDEX ROWID| OV_RESOURCE_TOPICS_MV | 1 | 20 | 24 |00:00:26.05 | 44267 | 3504 | | | | |
| |* 14 | DOMAIN INDEX | OV_RES_TOPICS_MV_CTX1 | 1 | | 25 |00:00:26.05 | 44256 | 3504 | | | | |
| |* 15 | TABLE ACCESS FULL | QDB_QUESTIONS | 1 | 2429 | 2626 |00:00:00.02 | 8183 | 6053 | | | | |
| | 16 | VIEW | index$_join$_006 | 1 | 7 | 7 |00:00:00.01 | 8 | 0 | | | | |
| |* 17 | HASH JOIN | | 1 | | 7 |00:00:00.01 | 8 | 0 | 1483K| 1483K| 1106K (0)| |
| | 18 | INDEX FAST FULL SCAN | PK_QDB_DIFFICULTY_LEVELS | 1 | 7 | 7 |00:00:00.01 | 4 | 0 | | | | |
| | 19 | INDEX FAST FULL SCAN | UN_QDB_DIFF_LEVELS_TEXT | 1 | 7 | 7 |00:00:00.01 | 4 | 0 | | | | |
| |* 20 | INDEX UNIQUE SCAN | OV_TOPICS_PK | 21 | 1 | 21 |00:00:00.01 | 23 | 0 | | | | |
| | 21 | TABLE ACCESS BY INDEX ROWID | OV_TOPICS | 21 | 1 | 21 |00:00:00.01 | 21 | 0 | | | | |
| | 22 | TABLE ACCESS FULL | OV_CATEGORIES | 1 | 39 | 39 |00:00:00.01 | 7 | 0 | | | | |
| | 23 | TABLE ACCESS BY INDEX ROWID BATCHED| SEC_LOV_VALUES | 1 | 2 | 2 |00:00:00.01 | 2 | 0 | | | | |
| |* 24 | INDEX RANGE SCAN | SEC_LOV_VALUES_C4 | 1 | 2 | 2 |00:00:00.01 | 1 | 0 | | | | |
| | 25 | TABLE ACCESS BY INDEX ROWID | QDB_USERS | 21 | 1 | 21 |00:00:00.01 | 38 | 0 | | | | |
| |* 26 | INDEX UNIQUE SCAN | QDB_USERS | 21 | 1 | 21 |00:00:00.01 | 17 | 0 | | | | |
| |* 27 | INDEX UNIQUE SCAN | SEC_USERS_PK | 21 | 1 | 21 |00:00:00.01 | 9 | 0 | | | | |
| |* 28 | INDEX UNIQUE SCAN | SEC_USERS_PK | 21 | 1 | 21 |00:00:00.01 | 9 | 0 | | | | |
| |* 29 | INDEX UNIQUE SCAN | SEC_USERS_PK | 21 | 1 | 21 |00:00:00.01 | 9 | 0 | | | | |
| |* 30 | INDEX UNIQUE SCAN | SEC_USERS_PK | 21 | 1 | 21 |00:00:00.01 | 9 | 0 | | | | |
| | 31 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 32 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 33 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 34 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 35 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 36 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 37 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 38 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 39 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| |* 40 | TABLE ACCESS BY INDEX ROWID | DG_WORKOUTS | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| |* 41 | DOMAIN INDEX | DG_WORKOUTS_CTX1 | 1 | | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 42 | TABLE ACCESS BY INDEX ROWID | QDB_DIFFICULTY_LEVELS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 43 | INDEX UNIQUE SCAN | PK_QDB_DIFFICULTY_LEVELS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 44 | TABLE ACCESS BY INDEX ROWID | OV_TOPICS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 45 | INDEX UNIQUE SCAN | OV_TOPICS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 46 | TABLE ACCESS BY INDEX ROWID | OV_CATEGORIES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 47 | INDEX UNIQUE SCAN | OV_CATEGORIES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 48 | TABLE ACCESS BY INDEX ROWID | SEC_LOV_VALUES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 49 | INDEX UNIQUE SCAN | SEC_LOV_VALUES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 50 | TABLE ACCESS BY INDEX ROWID | QDB_USERS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 51 | INDEX UNIQUE SCAN | QDB_USERS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 52 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 53 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 54 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 55 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 56 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 57 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 58 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 59 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 60 | NESTED LOOPS ANTI | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 61 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 62 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 63 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 64 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 65 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| |* 66 | TABLE ACCESS BY INDEX ROWID | DG_CLASSES | 1 | 1 | 0 |00:00:00.01 | 14 | 0 | | | | |
| |* 67 | DOMAIN INDEX | DG_CLASSES_CTX1 | 1 | | 0 |00:00:00.01 | 14 | 0 | | | | |
| | 68 | TABLE ACCESS BY INDEX ROWID | QDB_DIFFICULTY_LEVELS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 69 | INDEX UNIQUE SCAN | PK_QDB_DIFFICULTY_LEVELS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 70 | TABLE ACCESS BY INDEX ROWID | OV_TOPICS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 71 | INDEX UNIQUE SCAN | OV_TOPICS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 72 | TABLE ACCESS BY INDEX ROWID | OV_CATEGORIES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 73 | INDEX UNIQUE SCAN | OV_CATEGORIES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 74 | TABLE ACCESS BY INDEX ROWID | SEC_LOV_VALUES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 75 | INDEX UNIQUE SCAN | SEC_LOV_VALUES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 76 | TABLE ACCESS BY INDEX ROWID | QDB_USERS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 77 | INDEX UNIQUE SCAN | QDB_USERS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 78 | VIEW PUSHED PREDICATE | VW_NSO_1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 79 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 80 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 81 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 82 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 83 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 84 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 85 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 86 | TABLE ACCESS BY INDEX ROWID | DG_CLASSES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 87 | INDEX UNIQUE SCAN | DG_CLASSES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 88 | TABLE ACCESS BY INDEX ROWID | OV_TOPICS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 89 | INDEX UNIQUE SCAN | OV_TOPICS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| | 90 | TABLE ACCESS BY INDEX ROWID | OV_CATEGORIES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 91 | INDEX UNIQUE SCAN | OV_CATEGORIES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 92 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 93 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 94 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 95 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 96 | TABLE ACCESS BY INDEX ROWID | SEC_LOV_VALUES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 97 | INDEX UNIQUE SCAN | SEC_LOV_VALUES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 98 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |* 99 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |*100 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| |*101 | INDEX UNIQUE SCAN | SEC_USERS_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| |
| 7 - access("OC"."CATEGORY_STATUS_ID"="SLV"."LOV_VALUE_ID") |
| 8 - access("CATEGORY_ID"="CATEGORY_ID") |
| 11 - access("Q"."DIFFICULTY_ID"="DL"."DIFFICULTY_ID") |
| 12 - access("Q"."CH_CONTENT_ID"="OT"."RESOURCE_ID") |
| 13 - filter("OT"."RESOURCE_ID" IS NOT NULL) |
| 14 - access("CTXSYS"."CONTAINS"("OT"."RESOURCE_NAME",'too_many_rows',1)>0) |
| 15 - filter("Q"."CH_CONTENT_ID" IS NOT NULL) |
| 17 - access(ROWID=ROWID) |
| 20 - access("Q"."TOPIC_ID"="TOPIC_ID") |
| 24 - access("SLV"."LOV_NAME"='OV_CATEGORIES_STATUS' AND "SLV"."ENABLED_FLAG"='Y') |
| filter("SLV"."ENABLED_FLAG"='Y') |
| 26 - access("Q"."AUTHOR_ID"="U"."USER_ID") |
| 27 - access("OT"."CREATED_BY"="SU_C"."USER_ID") |
| 28 - access("OT"."UPDATED_BY"="SU_U"."USER_ID") |
| 29 - access("OC"."CREATED_BY"="SU_C"."USER_ID") |
| 30 - access("OC"."UPDATED_BY"="SU_U"."USER_ID") |
| 40 - filter(("WO"."INSTRUCTOR_ID" IS NOT NULL AND "WO"."GOAL_ID" IS NULL AND (("WO"."END_DATE" IS NULL AND "WO"."START_DATE"<=SYSDATE@!) OR |
| ("WO"."END_DATE">=SYSDATE@! AND "WO"."START_DATE"<=SYSDATE@!)) AND "WO"."APPROVED_BY_USER_ID" IS NOT NULL)) |
| 41 - access("CTXSYS"."CONTAINS"("WO"."WORKOUT_NAME",'too_many_rows',1)>0) |
| 43 - access("WO"."DIFFICULTY_ID"="DL2"."DIFFICULTY_ID") |
| 45 - access("WO"."TOPIC_ID"="TOPIC_ID") |
| 47 - access("CATEGORY_ID"="CATEGORY_ID") |
| 48 - filter(("SLV"."LOV_NAME"='OV_CATEGORIES_STATUS' AND "SLV"."ENABLED_FLAG"='Y')) |
| 49 - access("OC"."CATEGORY_STATUS_ID"="SLV"."LOV_VALUE_ID") |
| 51 - access("WO"."INSTRUCTOR_ID"="U2"."USER_ID") |
| 52 - access("OT"."UPDATED_BY"="SU_U"."USER_ID") |
| 53 - access("OT"."CREATED_BY"="SU_C"."USER_ID") |
| 54 - access("OC"."UPDATED_BY"="SU_U"."USER_ID") |
| 55 - access("OC"."CREATED_BY"="SU_C"."USER_ID") |
| 66 - filter(("C"."PARENT_CLASS_ID" IS NULL AND "C"."APPROVED_BY_USER_ID" IS NOT NULL AND "C"."DIFFICULTY_ID" IS NOT NULL AND "C"."TEACHER_ID" IS NOT NULL |
| AND NVL("C"."ABBREVIATION",'*NO ABBREV*')<>'ATOH')) |
| 67 - access("CTXSYS"."CONTAINS"("C"."CLASS_NAME",'too_many_rows',1)>0) |
| 69 - access("C"."DIFFICULTY_ID"="DL3"."DIFFICULTY_ID") |
| 71 - access("C"."TOPIC_ID"="TOPIC_ID") |
| 73 - access("CATEGORY_ID"="CATEGORY_ID") |
| 74 - filter(("SLV"."LOV_NAME"='OV_CATEGORIES_STATUS' AND "SLV"."ENABLED_FLAG"='Y')) |
| 75 - access("OC"."CATEGORY_STATUS_ID"="SLV"."LOV_VALUE_ID") |
| 77 - access("C"."TEACHER_ID"="U3"."USER_ID") |
| 86 - filter(("C"."ABBREVIATION"='ATOH' AND "C"."CLASS_NAME"<>'ATOH-DO-NOT-MODIFY')) |
| 87 - access("C"."CLASS_ID"="C"."CLASS_ID") |
| 89 - access("C"."TOPIC_ID"="TOPIC_ID") |
| 91 - access("CATEGORY_ID"="CATEGORY_ID") |
| 92 - access("OT"."UPDATED_BY"="SU_U"."USER_ID") |
| 93 - access("OT"."CREATED_BY"="SU_C"."USER_ID") |
| 94 - access("OC"."UPDATED_BY"="SU_U"."USER_ID") |
| 95 - access("OC"."CREATED_BY"="SU_C"."USER_ID") |
| 96 - filter(("SLV"."LOV_NAME"='OV_CATEGORIES_STATUS' AND "SLV"."ENABLED_FLAG"='Y')) |
| 97 - access("OC"."CATEGORY_STATUS_ID"="SLV"."LOV_VALUE_ID") |
| 98 - access("OT"."UPDATED_BY"="SU_U"."USER_ID") |
| 99 - access("OT"."CREATED_BY"="SU_C"."USER_ID") |
| 100 - access("OC"."UPDATED_BY"="SU_U"."USER_ID") |
| 101 - access("OC"."CREATED_BY"="SU_C"."USER_ID") |
| |
| Note |
| ----- |
| - dynamic statistics used: dynamic sampling (level=2) |
| - 3 Sql Plan Directives used for this statement |
| |