Skip to content

Instantly share code, notes, and snippets.

@aryangoti

aryangoti/txt

Created June 14, 2022 08:38
Show Gist options
  • Save aryangoti/03bd03b67969a13f159a4473cf653b1c to your computer and use it in GitHub Desktop.
Save aryangoti/03bd03b67969a13f159a4473cf653b1c to your computer and use it in GitHub Desktop.
Download ZIP
2018_good.
Raw
txt
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID at1d6u6g776b4, child number 0
-------------------------------------
SELECT /*+ PARALLEL(8) */ DISTINCT rd.document_id AS doc_id
,'firm_id' AS criteria_key ,opf.ultimate_parent_firm_id AS series_id
,period_number FROM ( SELECT /*+ PARALLEL(8) no_merge */ DISTINCT
rd.document_id ,rd.client_role_id ,( CASE WHEN
MONTHS_BETWEEN(TO_DATE('04/28/2022', 'MM/DD/YYYY'),
TO_DATE('04/01/2017', 'MM/DD/YYYY')) > 12 THEN
TRUNC(dc.date_value, 'YEAR') ELSE TRUNC(dc.date_value, 'MONTH')
END ) period_number FROM REPORT_ENGINE.date_code dc
,REPORT_ENGINE.lit_fact_bd rd INNER JOIN report_engine.firm FIRM ON
rd.firm_id = FIRM.firm_id WHERE dc.date_value BETWEEN
TO_DATE('04/01/2017', 'MM/DD/YYYY') AND TO_DATE('04/28/2022',
'MM/DD/YYYY') AND rd.publication_date_id = dc.date_id AND
rd.year_number = to_number('2018') AND (FIRM.ultimate_parent_firm_id
IN (to_number('252094'))) ) rd INNER JOIN
report_engine.opposing_counsel op ON rd.client_role_id =
op.legal_role_id INNER JOIN report_engine.lit_fact_bd opclf ON
opclf.document_id
Plan hash value: 1467675550
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Dist
rib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | | | 308 (100)| | | | | |
|
| 1 | PX COORDINATOR | | | | | | | | | |
|
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 17 | 1921 | 308 (1)| 00:00:06 | | | Q1,05 | P->S | QC (RAN
D) |
| 3 | HASH UNIQUE | | 17 | 1921 | 308 (1)| 00:00:06 | | | Q1,05 | PCWP |
|
| 4 | PX RECEIVE | | 17 | 1921 | 308 (1)| 00:00:06 | | | Q1,05 | PCWP |
|
| 5 | PX SEND HASH | :TQ10004 | 17 | 1921 | 308 (1)| 00:00:06 | | | Q1,04 | P->P | HASH
|
| 6 | HASH UNIQUE | | 17 | 1921 | 308 (1)| 00:00:06 | | | Q1,04 | PCWP |
|
| 7 | NESTED LOOPS | | 17 | 1921 | 307 (1)| 00:00:06 | | | Q1,04 | PCWP |
|
| 8 | NESTED LOOPS | | 17 | 1921 | 307 (1)| 00:00:06 | | | Q1,04 | PCWP |
|
| 9 | NESTED LOOPS | | 17 | 1717 | 305 (1)| 00:00:06 | | | Q1,04 | PCWP |
|
|* 10 | HASH JOIN | | 381 | 20955 | 146 (2)| 00:00:03 | | | Q1,04 | PCWP |
|
| 11 | PX RECEIVE | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP |
|
| 12 | PX SEND BROADCAST | :TQ10002 | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCA
ST |
| 13 | PX BLOCK ITERATOR | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWC |
|
|* 14 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP |
|
| 15 | VIEW | | 819 | 38493 | 144 (2)| 00:00:03 | | | Q1,04 | PCWP |
|
| 16 | HASH UNIQUE | | 819 | 62244 | 144 (2)| 00:00:03 | | | Q1,04 | PCWP |
|
| 17 | PX RECEIVE | | 819 | 62244 | 143 (1)| 00:00:03 | | | Q1,04 | PCWP |
|
| 18 | PX SEND HASH | :TQ10003 | 819 | 62244 | 143 (1)| 00:00:03 | | | Q1,03 | P->P | HASH
|
|* 19 | HASH JOIN BUFFERED | | 819 | 62244 | 143 (1)| 00:00:03 | | | Q1,03 | PCWP |
|
| 20 | BUFFER SORT | | | | | | | | Q1,03 | PCWC |
|
| 21 | PX RECEIVE | | 819 | 51597 | 133 (0)| 00:00:03 | | | Q1,03 | PCWP |
|
| 22 | PX SEND HASH | :TQ10000 | 819 | 51597 | 133 (0)| 00:00:03 | | | | S->P | HASH
|
| 23 | NESTED LOOPS | | 819 | 51597 | 133 (0)| 00:00:03 | | | | |
|
| 24 | NESTED LOOPS | | 819 | 51597 | 133 (0)| 00:00:03 | | | | |
|
| 25 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 2 (0)| 00:00:01 | | | | |
|
|* 26 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | | 1 (0)| 00:00:01 | | | | |
|
| 27 | PARTITION RANGE SINGLE | | | | | | 29 | 29 | | |
|
| 28 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | |
|
|* 29 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | | | 29 | 29 | | |
|
|* 30 | TABLE ACCESS BY LOCAL INDEX ROWID| LIT_FACT_BD | 587 | 29937 | 133 (0)| 00:00:03 | 29 | 29 | | |
|
| 31 | PX RECEIVE | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,03 | PCWP |
|
| 32 | PX SEND HASH | :TQ10001 | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | P->P | HASH
|
| 33 | PX BLOCK ITERATOR | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | PCWC |
|
|* 34 | TABLE ACCESS FULL | DATE_CODE | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | PCWP |
|
| 35 | PARTITION RANGE SINGLE | | 1 | 46 | 0 (0)| | 29 | 29 | Q1,04 | PCWP |
|
|* 36 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 1 | 46 | 0 (0)| | 29 | 29 | Q1,04 | PCWP |
|
|* 37 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 0 (0)| | 29 | 29 | Q1,04 | PCWP |
|
|* 38 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 0 (0)| | | | Q1,04 | PCWP |
|
| 39 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 0 (0)| | | | Q1,04 | PCWP |
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("RD"."CLIENT_ROLE_ID"="OP"."LEGAL_ROLE_ID")
14 - access(:Z>=:Z AND :Z<=:Z)
19 - access("RD"."PUBLICATION_DATE_ID"="DC"."DATE_ID")
26 - access("FIRM"."ULTIMATE_PARENT_FIRM_ID"=252094)
29 - access("RD"."FIRM_ID"="FIRM"."FIRM_ID")
30 - filter("RD"."YEAR_NUMBER"=2018)
34 - access(:Z>=:Z AND :Z<=:Z)
filter(("DC"."DATE_VALUE">=TO_DATE(' 2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DC"."DATE_VALUE"<=TO_DATE(' 2022-04-28 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
36 - filter("OPCLF"."YEAR_NUMBER"=2018)
37 - access("OPCLF"."DOCUMENT_ID"="RD"."DOCUMENT_ID" AND "OPCLF"."FIRM_ID">=1000 AND "OP"."OPPOSING_COUNSEL_ROLE_ID"="OPCLF"."CLIENT_ROLE_ID")
filter("OP"."OPPOSING_COUNSEL_ROLE_ID"="OPCLF"."CLIENT_ROLE_ID")
38 - access("OPCLF"."FIRM_ID"="OPF"."FIRM_ID")
filter("OPF"."FIRM_ID">=1000)
Note
-----
- dynamic sampling used for this statement (level=6)
- Degree of Parallelism is 8 because of hint
90 rows selected.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment