使用distinct和不使用distinct到底可以造成多大损耗呢
一个稍微复杂一点的SQL，连接了5张表
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6685 | 4119K| | 46354 (1)| 00:09:17 |
|* 1 | FILTER | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | PSP_GROUP_CAT_MASTER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PSP_GROUP_CAT_MASTER_PK | 1 | | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | PSP_GROUP_CAT_MASTER | 1 | 14 | | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PSP_GROUP_CAT_MASTER_PK | 1 | | | 1 (0)| 00:00:01 |
| 7 | SORT UNIQUE | | 6685 | 4119K| 4472K| 45461 (1)| 00:09:06 |
|* 8 | COUNT STOPKEY | | | | | | |
|* 9 | FILTER | | | | | | |
|* 10 | HASH JOIN RIGHT OUTER | | 6685 | 4119K| | 43424 (1)| 00:08:42 |
| 11 | TABLE ACCESS FULL | PSP_IND_CAT_MASTER | 16 | 304 | | 3 (0)| 00:00:01 |
|* 12 | FILTER | | | | | | |
|* 13 | HASH JOIN RIGHT OUTER | | 15280 | 9132K| | 43420 (1)| 00:08:42 |
| 14 | TABLE ACCESS FULL | PSP_IND_CAT_MASTER | 16 | 304 | | 3 (0)| 00:00:01 |
|* 15 | HASH JOIN RIGHT OUTER | | 34925 | 19M| | 43417 (1)| 00:08:42 |
| 16 | TABLE ACCESS FULL | PSP_CORE_CSIS | 41677 | 447K| | 144 (0)| 00:00:02 |
|* 17 | HASH JOIN OUTER | | 34925 | 19M| 8736K| 43272 (1)| 00:08:40 |
|* 18 | FILTER | | | | | | |
|* 19 | HASH JOIN RIGHT OUTER | | 34925 | 8321K| 2472K| 39196 (1)| 00:07:51 |
| 20 | TABLE ACCESS FULL | PSP_EXISTED_HR_CATEGORY_LIST | 84261 | 1481K| | 585 (1)| 00:00:08 |
| 21 | VIEW | | 105K| 22M| | 37303 (1)| 00:07:28 |
|* 22 | FILTER | | | | | | |
|* 23 | HASH JOIN OUTER | | 105K| 27M| 19M| 37303 (1)| 00:07:28 |
|* 24 | HASH JOIN RIGHT OUTER | | 120K| 18M| 4136K| 32231 (1)| 00:06:27 |
| 25 | TABLE ACCESS FULL | PSP_CORE_ORG_LEVEL | 98360 | 2977K| | 2023 (1)| 00:00:25 |
|* 26 | HASH JOIN RIGHT OUTER | | 120K| 15M| 4712K| 29192 (1)| 00:05:51 |
| 27 | TABLE ACCESS FULL | PSP_CORE_ORG | 98360 | 3554K| | 308 (1)| 00:00:04 |
|* 28 | HASH JOIN OUTER | | 120K| 10M| 10M| 28051 (1)| 00:05:37 |
|* 29 | FILTER | | | | | | |
|* 30 | HASH JOIN RIGHT OUTER| | 120K| 9269K| 3520K| 14464 (1)| 00:02:54 |
| 31 | TABLE ACCESS FULL | PSP_EXISTED_GOC_CATEGORY_LIST | 116K| 2155K| | 1170 (1)| 00:00:15 |
|* 32 | TABLE ACCESS FULL | PSP_CORE_GDW | 360K| 20M| | 11891 (1)| 00:02:23 |
| 33 | TABLE ACCESS FULL | PSP_EMDD_FEED_TB | 477K| 6995K| | 12455 (1)| 00:02:30 |
| 34 | TABLE ACCESS FULL | PSP_EXISTED_CATEGORY_LIST | 268K| 29M| | 2466 (1)| 00:00:30 |
| 35 | TABLE ACCESS FULL | PSP_CORE_ORG_LEVEL | 98360 | 31M| | 2023 (1)| 00:00:25 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6685 | 10M| | 13806 (1)| 00:02:46 | | | |
|* 1 | FILTER | | | | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | PSP_GROUP_CAT_MASTER | 1 | 14 | | 2 (0)| 00:00:01 | | | |
|* 3 | INDEX RANGE SCAN | PSP_GROUP_CAT_MASTER_PK | 1 | | | 1 (0)| 00:00:01 | | | |
|* 4 | FILTER | | | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | PSP_GROUP_CAT_MASTER | 1 | 14 | | 2 (0)| 00:00:01 | | | |
|* 6 | INDEX RANGE SCAN | PSP_GROUP_CAT_MASTER_PK | 1 | | | 1 (0)| 00:00:01 | | | |
| 7 | SORT ORDER BY | | 6685 | 10M| 29M| 13806 (1)| 00:02:46 | | | |
|* 8 | COUNT STOPKEY | | | | | | | | | |
| 9 | PX COORDINATOR | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10014 | 6685 | 10M| | 13805 (1)| 00:02:46 | Q1,14 | P->S | QC (RAND) |
| 11 | BUFFER SORT | | 6685 | 10M| | | | Q1,14 | PCWP | |
|* 12 | COUNT STOPKEY | | | | | | | Q1,14 | PCWC | |
|* 13 | FILTER | | | | | | | Q1,14 | PCWC | |
|* 14 | HASH JOIN RIGHT OUTER | | 6685 | 10M| | 13805 (1)| 00:02:46 | Q1,14 | PCWP | |
| 15 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
| 16 | PX RECEIVE | | 16 | 304 | | 3 (0)| 00:00:01 | Q1,14 | PCWP | |
| 17 | PX SEND BROADCAST | :TQ10004 | 16 | 304 | | 3 (0)| 00:00:01 | | S->P | BROADCAST |
| 18 | TABLE ACCESS FULL | PSP_IND_CAT_MASTER | 16 | 304 | | 3 (0)| 00:00:01 | | | |
|* 19 | FILTER | | | | | | | Q1,14 | PCWC | |
|* 20 | HASH JOIN RIGHT OUTER | | 15280 | 24M| | 13801 (1)| 00:02:46 | Q1,14 | PCWP | |
| 21 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
| 22 | PX RECEIVE | | 16 | 304 | | 3 (0)| 00:00:01 | Q1,14 | PCWP | |
| 23 | PX SEND BROADCAST | :TQ10005 | 16 | 304 | | 3 (0)| 00:00:01 | | S->P | BROADCAST |
| 24 | TABLE ACCESS FULL | PSP_IND_CAT_MASTER | 16 | 304 | | 3 (0)| 00:00:01 | | | |
|* 25 | HASH JOIN RIGHT OUTER | | 34925 | 54M| | 13798 (1)| 00:02:46 | Q1,14 | PCWP | |
| 26 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
| 27 | PX RECEIVE | | 98360 | 31M| | 2023 (1)| 00:00:25 | Q1,14 | PCWP | |
| 28 | PX SEND HASH | :TQ10006 | 98360 | 31M| | 2023 (1)| 00:00:25 | | S->P | HASH |
| 29 | TABLE ACCESS FULL | PSP_CORE_ORG_LEVEL | 98360 | 31M| | 2023 (1)| 00:00:25 | | | |
| 30 | PX RECEIVE | | 34925 | 43M| | 11774 (1)| 00:02:22 | Q1,14 | PCWP | |
| 31 | PX SEND HASH | :TQ10013 | 34925 | 43M| | 11774 (1)| 00:02:22 | Q1,13 | P->P | HASH |
|* 32 | HASH JOIN RIGHT OUTER BUFFERED | | 34925 | 43M| | 11774 (1)| 00:02:22 | Q1,13 | PCWP | |
| 33 | BUFFER SORT | | | | | | | Q1,13 | PCWC | |
| 34 | PX RECEIVE | | 41677 | 447K| | 144 (0)| 00:00:02 | Q1,13 | PCWP | |
| 35 | PX SEND BROADCAST | :TQ10002 | 41677 | 447K| | 144 (0)| 00:00:02 | | S->P | BROADCAST |
| 36 | TABLE ACCESS FULL | PSP_CORE_CSIS | 41677 | 447K| | 144 (0)| 00:00:02 | | | |
|* 37 | FILTER | | | | | | | Q1,13 | PCWC | |
|* 38 | HASH JOIN RIGHT OUTER | | 34925 | 42M| | 11629 (1)| 00:02:20 | Q1,13 | PCWP | |
| 39 | BUFFER SORT | | | | | | | Q1,13 | PCWC | |
| 40 | PX RECEIVE | | 84261 | 1481K| | 585 (1)| 00:00:08 | Q1,13 | PCWP | |
| 41 | PX SEND BROADCAST | :TQ10003 | 84261 | 1481K| | 585 (1)| 00:00:08 | | S->P | BROADCAST |
| 42 | TABLE ACCESS FULL | PSP_EXISTED_HR_CATEGORY_LIST | 84261 | 1481K| | 585 (1)| 00:00:08 | | | |
| 43 | VIEW | | 105K| 127M| | 11044 (1)| 00:02:13 | Q1,13 | PCWP | |
|* 44 | HASH JOIN RIGHT OUTER | | 105K| 27M| | 11044 (1)| 00:02:13 | Q1,13 | PCWP | |
| 45 | PX RECEIVE | | 98360 | 2977K| | 561 (1)| 00:00:07 | Q1,13 | PCWP | |
| 46 | PX SEND HASH | :TQ10011 | 98360 | 2977K| | 561 (1)| 00:00:07 | Q1,11 | P->P | HASH |
| 47 | PX BLOCK ITERATOR | | 98360 | 2977K| | 561 (1)| 00:00:07 | Q1,11 | PCWC | |
| 48 | TABLE ACCESS FULL | PSP_CORE_ORG_LEVEL | 98360 | 2977K| | 561 (1)| 00:00:07 | Q1,11 | PCWP | |
| 49 | PX RECEIVE | | 105K| 24M| | 10481 (1)| 00:02:06 | Q1,13 | PCWP | |
| 50 | PX SEND HASH | :TQ10012 | 105K| 24M| | 10481 (1)| 00:02:06 | Q1,12 | P->P | HASH |
|* 51 | HASH JOIN RIGHT OUTER BUFFERED| | 105K| 24M| | 10481 (1)| 00:02:06 | Q1,12 | PCWP | |
| 52 | PX RECEIVE | | 98360 | 3554K| | 86 (2)| 00:00:02 | Q1,12 | PCWP | |
| 53 | PX SEND HASH | :TQ10009 | 98360 | 3554K| | 86 (2)| 00:00:02 | Q1,09 | P->P | HASH |
| 54 | PX BLOCK ITERATOR | | 98360 | 3554K| | 86 (2)| 00:00:02 | Q1,09 | PCWC | |
| 55 | TABLE ACCESS FULL | PSP_CORE_ORG | 98360 | 3554K| | 86 (2)| 00:00:02 | Q1,09 | PCWP | |
| 56 | PX RECEIVE | | 105K| 21M| | 10395 (1)| 00:02:05 | Q1,12 | PCWP | |
| 57 | PX SEND HASH | :TQ10010 | 105K| 21M| | 10395 (1)| 00:02:05 | Q1,10 | P->P | HASH |
|* 58 | FILTER | | | | | | | Q1,10 | PCWC | |
|* 59 | HASH JOIN OUTER | | 105K| 21M| | 10395 (1)| 00:02:05 | Q1,10 | PCWP | |
|* 60 | HASH JOIN OUTER | | 120K| 10M| | 7928 (1)| 00:01:36 | Q1,10 | PCWP | |
| 61 | PX RECEIVE | | 120K| 9269K| | 4471 (1)| 00:00:54 | Q1,10 | PCWP | |
| 62 | PX SEND HASH | :TQ10007 | 120K| 9269K| | 4471 (1)| 00:00:54 | Q1,07 | P->P | HASH |
|* 63 | FILTER | | | | | | | Q1,07 | PCWC | |
|* 64 | HASH JOIN RIGHT OUTER| | 120K| 9269K| | 4471 (1)| 00:00:54 | Q1,07 | PCWP | |
| 65 | BUFFER SORT | | | | | | | Q1,07 | PCWC | |
| 66 | PX RECEIVE | | 116K| 2155K| | 1170 (1)| 00:00:15 | Q1,07 | PCWP | |
| 67 | PX SEND BROADCAST | :TQ10000 | 116K| 2155K| | 1170 (1)| 00:00:15 | | S->P | BROADCAST |
| 68 | TABLE ACCESS FULL| PSP_EXISTED_GOC_CATEGORY_LIST | 116K| 2155K| | 1170 (1)| 00:00:15 | | | |
| 69 | PX BLOCK ITERATOR | | 360K| 20M| | 3300 (1)| 00:00:40 | Q1,07 | PCWC | |
|* 70 | TABLE ACCESS FULL | PSP_CORE_GDW | 360K| 20M| | 3300 (1)| 00:00:40 | Q1,07 | PCWP | |
| 71 | PX RECEIVE | | 477K| 6995K| | 3456 (1)| 00:00:42 | Q1,10 | PCWP | |
| 72 | PX SEND HASH | :TQ10008 | 477K| 6995K| | 3456 (1)| 00:00:42 | Q1,08 | P->P | HASH |
| 73 | PX BLOCK ITERATOR | | 477K| 6995K| | 3456 (1)| 00:00:42 | Q1,08 | PCWC | |
| 74 | TABLE ACCESS FULL | PSP_EMDD_FEED_TB | 477K| 6995K| | 3456 (1)| 00:00:42 | Q1,08 | PCWP | |
| 75 | BUFFER SORT | | | | | | | Q1,10 | PCWC | |
| 76 | PX RECEIVE | | 268K| 29M| | 2466 (1)| 00:00:30 | Q1,10 | PCWP | |
| 77 | PX SEND HASH | :TQ10001 | 268K| 29M| | 2466 (1)| 00:00:30 | | S->P | HASH |
| 78 | TABLE ACCESS FULL | PSP_EXISTED_CATEGORY_LIST | 268K| 29M| | 2466 (1)| 00:00:30 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------