Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Помогите плиз с производительностью запроса  [new]
vadipok
Member

Откуда:
Сообщений: 1001
Добрый день, коллеги!

1-й запрос
+

SELECT COMMENTS, NAME_TABLE, "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020", "2021", "2022", "2023", "2024", "2025", "2026", "2027", "2028", "2029", "2030", "2031", "2032", "2033", "2034", "2035", "2036", "2037", "2038", "2039", "2040", "2041", "2042", "2043", "2044", "2045", "2046", "2047", "2048", "2049", "2050", "2051", "2052", "2053", "2054", "2055"
  FROM (  SELECT /*+ PARALLEL(DATA_OBJECT_ALL 8) */ YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_ALL' TABLE_NAME, 1 ORDER_NUM
          FROM DATA_OBJECT_ALL
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (ALL01 AS 'ALL01', ALL02 AS 'ALL02', ALL03 AS 'ALL03',
                                                        ALL04 AS 'ALL04', ALL05 AS 'ALL05', ALL06 AS 'ALL06',
                                                        ALL07 AS 'ALL07', ALL08 AS 'ALL08', ALL09 AS 'ALL09',
                                                        ALL10 AS 'ALL10', ALL11 AS 'ALL11', ALL12 AS 'ALL12',
                                                        ALL13 AS 'ALL13', ALL14 AS 'ALL14', ALL15 AS 'ALL15',
                                                        ALL16 AS 'ALL16', ALL17 AS 'ALL17', ALL18 AS 'ALL18',
                                                        ALL19 AS 'ALL19'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_BASE' TABLE_NAME, 2 ORDER_NUM
          FROM DATA_OBJECT_BASE
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (BASE01 AS 'BASE01', BASE02 AS 'BASE02', BASE03 AS 'BASE03',
                                                        BASE04 AS 'BASE04', BASE05 AS 'BASE05', BASE06 AS 'BASE06',
                                                        BASE07 AS 'BASE07', BASE08 AS 'BASE08', BASE09 AS 'BASE09',
                                                        BASE10 AS 'BASE10', BASE11 AS 'BASE11'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_NEW_WELL' TABLE_NAME, 3 ORDER_NUM
          FROM DATA_OBJECT_NEW_WELL
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (NEW_WELL01 AS 'NEW_WELL01', NEW_WELL02 AS 'NEW_WELL02', NEW_WELL03 AS 'NEW_WELL03',
                                                        NEW_WELL04 AS 'NEW_WELL04', NEW_WELL05 AS 'NEW_WELL05', NEW_WELL06 AS 'NEW_WELL06',
                                                        NEW_WELL08 AS 'NEW_WELL08', NEW_WELL08 AS 'NEW_WELL08', NEW_WELL09 AS 'NEW_WELL09',
                                                        NEW_WELL10 AS 'NEW_WELL10', NEW_WELL11 AS 'NEW_WELL11', NEW_WELL12 AS 'NEW_WELL12',
                                                        NEW_WELL13 AS 'NEW_WELL13', NEW_WELL14 AS 'NEW_WELL14', NEW_WELL15 AS 'NEW_WELL15', 
                                                        NEW_WELL16 AS 'NEW_WELL16', NEW_WELL17 AS 'NEW_WELL17', NEW_WELL18 AS 'NEW_WELL18',
                                                        NEW_WELL19 AS 'NEW_WELL19', NEW_WELL20 AS 'NEW_WELL20', NEW_WELL21 AS 'NEW_WELL21',
                                                        NEW_WELL22 AS 'NEW_WELL22', NEW_WELL23 AS 'NEW_WELL23', NEW_WELL24 AS 'NEW_WELL24',
                                                        NEW_WELL25 AS 'NEW_WELL25', NEW_WELL26 AS 'NEW_WELL26', NEW_WELL27 AS 'NEW_WELL27',
                                                        NEW_WELL28 AS 'NEW_WELL28', NEW_WELL29 AS 'NEW_WELL29', NEW_WELL30 AS 'NEW_WELL30',
                                                        NEW_WELL31 AS 'NEW_WELL31', NEW_WELL32 AS 'NEW_WELL32', NEW_WELL33 AS 'NEW_WELL33'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_SIDE_WELL' TABLE_NAME, 4 ORDER_NUM
          FROM DATA_OBJECT_SIDE_WELL
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (SIDE_WELL01 AS 'SIDE_WELL01', SIDE_WELL02 AS 'SIDE_WELL02', SIDE_WELL03 AS 'SIDE_WELL03',
                                                        SIDE_WELL04 AS 'SIDE_WELL04', SIDE_WELL05 AS 'SIDE_WELL05', SIDE_WELL06 AS 'SIDE_WELL06',
                                                        SIDE_WELL07 AS 'SIDE_WELL07', SIDE_WELL08 AS 'SIDE_WELL08', SIDE_WELL09 AS 'SIDE_WELL09',
                                                        SIDE_WELL10 AS 'SIDE_WELL10', SIDE_WELL11 AS 'SIDE_WELL11', SIDE_WELL12 AS 'SIDE_WELL12',
                                                        SIDE_WELL13 AS 'SIDE_WELL13', SIDE_WELL14 AS 'SIDE_WELL14', SIDE_WELL15 AS 'SIDE_WELL15',
                                                        SIDE_WELL16 AS 'SIDE_WELL16', SIDE_WELL17 AS 'SIDE_WELL17', SIDE_WELL18 AS 'SIDE_WELL18',
                                                        SIDE_WELL19 AS 'SIDE_WELL19', SIDE_WELL20 AS 'SIDE_WELL20'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_GTM' TABLE_NAME, 5 ORDER_NUM
          FROM DATA_OBJECT_GTM
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (GTM01 AS 'GTM01', GTM02 AS 'GTM02', GTM03 AS 'GTM03',
                                                        GTM04 AS 'GTM04', GTM05 AS 'GTM05', GTM06 AS 'GTM06',
                                                        GTM07 AS 'GTM07', GTM08 AS 'GTM08', GTM09 AS 'GTM09',
                                                        GTM10 AS 'GTM10', GTM11 AS 'GTM11', GTM12 AS 'GTM12',
                                                        GTM13 AS 'GTM13', GTM14 AS 'GTM14', GTM15 AS 'GTM15',
                                                        GTM16 AS 'GTM16'))
          WHERE DATA_OBJECT_ID = 42001
          ) t1
        INNER JOIN (SELECT TABLE_NAME, TABLE_NAME NAME_TABLE, COLUMN_NAME, COMMENTS FROM DBA_COL_COMMENTS) t2 
                    ON t1.ORDER_MODE = t2.COLUMN_NAME AND t1.TABLE_NAME = t2.TABLE_NAME
PIVOT(SUM(YEARLY_TOTAL)
      FOR YEAR IN (2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029, 2030, 2031, 2032, 2033, 2034, 2035, 2036, 2037, 2038, 2039, 2040, 2041, 2042, 2043, 2044, 2045, 2046, 2047, 2048, 2049, 2050, 2051, 2052, 2053, 2054, 2055))
ORDER BY ORDER_NUM, ORDER_MODE



План запроса
+

 Plan Hash Value  : 2969900461 

--------------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                           | Name                        | Rows  | Bytes   | Cost | Time     |
--------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                    |                             |     1 |    2588 |  733 | 00:00:09 |
|    1 |   SORT ORDER BY                                     |                             |     1 |    2588 |  733 | 00:00:09 |
|    2 |    VIEW                                             |                             |     1 |    2588 |  732 | 00:00:09 |
|    3 |     TRANSPOSE                                       |                             |       |         |      |          |
|    4 |      SORT GROUP BY PIVOT                            |                             |     1 |     178 |  732 | 00:00:09 |
|  * 5 |       FILTER                                        |                             |       |         |      |          |
|    6 |        PX COORDINATOR                               |                             |       |         |      |          |
|    7 |         PX SEND QC (RANDOM)                         | :TQ10006                    |     1 |     178 |  732 | 00:00:09 |
|    8 |          NESTED LOOPS OUTER                         |                             |     1 |     178 |  732 | 00:00:09 |
|    9 |           NESTED LOOPS                              |                             |     1 |     139 |  732 | 00:00:09 |
|   10 |            NESTED LOOPS                             |                             |     1 |     135 |  731 | 00:00:09 |
|   11 |             NESTED LOOPS                            |                             |     1 |     113 |  731 | 00:00:09 |
| * 12 |              HASH JOIN                              |                             |  1812 |  161268 |  683 | 00:00:09 |
|   13 |               PX RECEIVE                            |                             |  4257 |  204336 |  249 | 00:00:03 |
|   14 |                PX SEND HASH                         | :TQ10005                    |  4257 |  204336 |  249 | 00:00:03 |
|   15 |                 BUFFER SORT                         |                             |     1 |    2588 |      |          |
|   16 |                  VIEW                               |                             |  4257 |  204336 |  249 | 00:00:03 |
|   17 |                   UNION-ALL                         |                             |       |         |      |          |
| * 18 |                    VIEW                             |                             |   817 |   37582 |   56 | 00:00:01 |
|   19 |                     UNPIVOT                         |                             |       |         |      |          |
|   20 |                      PX BLOCK ITERATOR              |                             |    43 |    5461 |    2 | 00:00:01 |
| * 21 |                       TABLE ACCESS FULL             | DATA_OBJECT_ALL             |    43 |    5461 |    2 | 00:00:01 |
|   22 |                    BUFFER SORT                      |                             |       |         |      |          |
|   23 |                     PX RECEIVE                      |                             |       |         |      |          |
|   24 |                      PX SEND ROUND-ROBIN            | :TQ10000                    |       |         |      |          |
| * 25 |                       VIEW                          |                             |   473 |   22231 |   22 | 00:00:01 |
|   26 |                        UNPIVOT                      |                             |       |         |      |          |
|   27 |                         TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_BASE            |    43 |    2365 |    2 | 00:00:01 |
| * 28 |                          INDEX RANGE SCAN           | DOBJECT_BASE_DOBJECT_ID_IDX |    43 |         |    1 | 00:00:01 |
|   29 |                    BUFFER SORT                      |                             |       |         |      |          |
|   30 |                     PX RECEIVE                      |                             |       |         |      |          |
|   31 |                      PX SEND ROUND-ROBIN            | :TQ10001                    |       |         |      |          |
| * 32 |                       VIEW                          |                             |  1419 |   72369 |   99 | 00:00:02 |
|   33 |                        UNPIVOT                      |                             |       |         |      |          |
|   34 |                         TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_NEW_WELL        |    43 |    6235 |    3 | 00:00:01 |
| * 35 |                          INDEX RANGE SCAN           | DOBJECT_NEW_DOBJECT_ID_IDX  |    43 |         |    1 | 00:00:01 |
|   36 |                    BUFFER SORT                      |                             |       |         |      |          |
|   37 |                     PX RECEIVE                      |                             |       |         |      |          |
|   38 |                      PX SEND ROUND-ROBIN            | :TQ10002                    |       |         |      |          |
| * 39 |                       VIEW                          |                             |   860 |   44720 |   40 | 00:00:01 |
|   40 |                        UNPIVOT                      |                             |       |         |      |          |
|   41 |                         TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_SIDE_WELL       |    43 |    3182 |    2 | 00:00:01 |
| * 42 |                          INDEX RANGE SCAN           | DOBJECT_SIDE_DOBJECT_ID_IDX |    43 |         |    1 | 00:00:01 |
|   43 |                    BUFFER SORT                      |                             |       |         |      |          |
|   44 |                     PX RECEIVE                      |                             |   688 |   31648 |   32 | 00:00:01 |
|   45 |                      PX SEND ROUND-ROBIN            | :TQ10003                    |   688 |   31648 |   32 | 00:00:01 |
| * 46 |                       VIEW                          |                             |   688 |   31648 |   32 | 00:00:01 |
|   47 |                        UNPIVOT                      |                             |       |         |      |          |
|   48 |                         TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_GTM             |    43 |    3569 |    2 | 00:00:01 |
| * 49 |                          INDEX RANGE SCAN           | DOBJECT_GTM_DOBJECT_ID_IDX  |    43 |         |    1 | 00:00:01 |
|   50 |               BUFFER SORT                           |                             |       |         |      |          |
|   51 |                PX RECEIVE                           |                             | 29049 | 1191009 |  434 | 00:00:06 |
|   52 |                 PX SEND HASH                        | :TQ10004                    | 29049 | 1191009 |  434 | 00:00:06 |
| * 53 |                  INDEX FAST FULL SCAN               | I_OBJ2                      | 29049 | 1191009 |  434 | 00:00:06 |
| * 54 |              TABLE ACCESS CLUSTER                   | COL$                        |     1 |      24 |    1 | 00:00:01 |
| * 55 |               INDEX UNIQUE SCAN                     | I_OBJ#                      |     1 |         |    0 | 00:00:01 |
| * 56 |             INDEX RANGE SCAN                        | I_USER2                     |     1 |      22 |    1 | 00:00:01 |
| * 57 |            INDEX RANGE SCAN                         | I_USER2                     |     1 |       4 |    1 | 00:00:01 |
|   58 |           TABLE ACCESS BY INDEX ROWID               | COM$                        |     1 |      39 |    2 | 00:00:01 |
| * 59 |            INDEX UNIQUE SCAN                        | I_COM1                      |     1 |         |    1 | 00:00:01 |
|   60 |        NESTED LOOPS                                 |                             |     1 |      31 |    3 | 00:00:01 |
| * 61 |         INDEX SKIP SCAN                             | I_USER2                     |     1 |      20 |    1 | 00:00:01 |
| * 62 |         INDEX RANGE SCAN                            | I_OBJ4                      |     1 |      11 |    2 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
  "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR "O"."TYPE#"=4 AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
  "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#"
  AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
* 12 - access("T1"."TABLE_NAME"="O"."NAME")
* 18 - filter("unpivot_view_043"."YEARLY_TOTAL" IS NOT NULL)
* 21 - filter("DATA_OBJECT_ALL"."DATA_OBJECT_ID"=42001)
* 25 - filter("unpivot_view_055"."YEARLY_TOTAL" IS NOT NULL)
* 28 - access("DATA_OBJECT_BASE"."DATA_OBJECT_ID"=42001)
* 32 - filter("unpivot_view_089"."YEARLY_TOTAL" IS NOT NULL)
* 35 - access("DATA_OBJECT_NEW_WELL"."DATA_OBJECT_ID"=42001)
* 39 - filter("unpivot_view_110"."YEARLY_TOTAL" IS NOT NULL)
* 42 - access("DATA_OBJECT_SIDE_WELL"."DATA_OBJECT_ID"=42001)
* 46 - filter("unpivot_view_127"."YEARLY_TOTAL" IS NOT NULL)
* 49 - access("DATA_OBJECT_GTM"."DATA_OBJECT_ID"=42001)
* 53 - filter("O"."TYPE#"=2 OR "O"."TYPE#"=4)
* 54 - filter(BITAND("C"."PROPERTY",32)=0 AND "T1"."ORDER_MODE"="C"."NAME")
* 55 - access("O"."OBJ#"="C"."OBJ#")
* 56 - access("O"."OWNER#"="U"."USER#")
* 57 - access("O"."SPARE3"="U"."USER#")
* 59 - access("C"."OBJ#"="CO"."OBJ#"(+) AND "C"."INTCOL#"="CO"."COL#"(+))
* 59 - filter("CO"."COL#"(+) IS NOT NULL)
* 61 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 61 - filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 62 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")




Пытался улучшить запрос используя DECODE:
+

SELECT COMMENTS, 
       NAME_TABLE,
       min(decode(YEAR, 2013, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2014, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2015, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2016, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2017, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2018, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2019, YEARLY_TOTAL, NULL)) ,
       min(decode(YEAR, 2020, YEARLY_TOTAL, NULL)) 
  FROM (  SELECT /*+ PARALLEL(DATA_OBJECT_ALL 8) */ YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_ALL' TABLE_NAME, 1 ORDER_NUM
          FROM DATA_OBJECT_ALL
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (ALL01 AS 'ALL01', ALL02 AS 'ALL02', ALL03 AS 'ALL03',
                                                        ALL04 AS 'ALL04', ALL05 AS 'ALL05', ALL06 AS 'ALL06',
                                                        ALL07 AS 'ALL07', ALL08 AS 'ALL08', ALL09 AS 'ALL09',
                                                        ALL10 AS 'ALL10', ALL11 AS 'ALL11', ALL12 AS 'ALL12',
                                                        ALL13 AS 'ALL13', ALL14 AS 'ALL14', ALL15 AS 'ALL15',
                                                        ALL16 AS 'ALL16', ALL17 AS 'ALL17', ALL18 AS 'ALL18',
                                                        ALL19 AS 'ALL19'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_BASE' TABLE_NAME, 2 ORDER_NUM
          FROM DATA_OBJECT_BASE
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (BASE01 AS 'BASE01', BASE02 AS 'BASE02', BASE03 AS 'BASE03',
                                                        BASE04 AS 'BASE04', BASE05 AS 'BASE05', BASE06 AS 'BASE06',
                                                        BASE07 AS 'BASE07', BASE08 AS 'BASE08', BASE09 AS 'BASE09',
                                                        BASE10 AS 'BASE10', BASE11 AS 'BASE11'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_NEW_WELL' TABLE_NAME, 3 ORDER_NUM
          FROM DATA_OBJECT_NEW_WELL
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (NEW_WELL01 AS 'NEW_WELL01', NEW_WELL02 AS 'NEW_WELL02', NEW_WELL03 AS 'NEW_WELL03',
                                                        NEW_WELL04 AS 'NEW_WELL04', NEW_WELL05 AS 'NEW_WELL05', NEW_WELL06 AS 'NEW_WELL06',
                                                        NEW_WELL08 AS 'NEW_WELL08', NEW_WELL08 AS 'NEW_WELL08', NEW_WELL09 AS 'NEW_WELL09',
                                                        NEW_WELL10 AS 'NEW_WELL10', NEW_WELL11 AS 'NEW_WELL11', NEW_WELL12 AS 'NEW_WELL12',
                                                        NEW_WELL13 AS 'NEW_WELL13', NEW_WELL14 AS 'NEW_WELL14', NEW_WELL15 AS 'NEW_WELL15', 
                                                        NEW_WELL16 AS 'NEW_WELL16', NEW_WELL17 AS 'NEW_WELL17', NEW_WELL18 AS 'NEW_WELL18',
                                                        NEW_WELL19 AS 'NEW_WELL19', NEW_WELL20 AS 'NEW_WELL20', NEW_WELL21 AS 'NEW_WELL21',
                                                        NEW_WELL22 AS 'NEW_WELL22', NEW_WELL23 AS 'NEW_WELL23', NEW_WELL24 AS 'NEW_WELL24',
                                                        NEW_WELL25 AS 'NEW_WELL25', NEW_WELL26 AS 'NEW_WELL26', NEW_WELL27 AS 'NEW_WELL27',
                                                        NEW_WELL28 AS 'NEW_WELL28', NEW_WELL29 AS 'NEW_WELL29', NEW_WELL30 AS 'NEW_WELL30',
                                                        NEW_WELL31 AS 'NEW_WELL31', NEW_WELL32 AS 'NEW_WELL32', NEW_WELL33 AS 'NEW_WELL33'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_SIDE_WELL' TABLE_NAME, 4 ORDER_NUM
          FROM DATA_OBJECT_SIDE_WELL
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (SIDE_WELL01 AS 'SIDE_WELL01', SIDE_WELL02 AS 'SIDE_WELL02', SIDE_WELL03 AS 'SIDE_WELL03',
                                                        SIDE_WELL04 AS 'SIDE_WELL04', SIDE_WELL05 AS 'SIDE_WELL05', SIDE_WELL06 AS 'SIDE_WELL06',
                                                        SIDE_WELL07 AS 'SIDE_WELL07', SIDE_WELL08 AS 'SIDE_WELL08', SIDE_WELL09 AS 'SIDE_WELL09',
                                                        SIDE_WELL10 AS 'SIDE_WELL10', SIDE_WELL11 AS 'SIDE_WELL11', SIDE_WELL12 AS 'SIDE_WELL12',
                                                        SIDE_WELL13 AS 'SIDE_WELL13', SIDE_WELL14 AS 'SIDE_WELL14', SIDE_WELL15 AS 'SIDE_WELL15',
                                                        SIDE_WELL16 AS 'SIDE_WELL16', SIDE_WELL17 AS 'SIDE_WELL17', SIDE_WELL18 AS 'SIDE_WELL18',
                                                        SIDE_WELL19 AS 'SIDE_WELL19', SIDE_WELL20 AS 'SIDE_WELL20'))
          WHERE DATA_OBJECT_ID = 42001
        UNION ALL 
          SELECT YEAR, ORDER_MODE, YEARLY_TOTAL, 'DATA_OBJECT_GTM' TABLE_NAME, 5 ORDER_NUM
          FROM DATA_OBJECT_GTM
               UNPIVOT (YEARLY_TOTAL FOR ORDER_MODE IN (GTM01 AS 'GTM01', GTM02 AS 'GTM02', GTM03 AS 'GTM03',
                                                        GTM04 AS 'GTM04', GTM05 AS 'GTM05', GTM06 AS 'GTM06',
                                                        GTM07 AS 'GTM07', GTM08 AS 'GTM08', GTM09 AS 'GTM09',
                                                        GTM10 AS 'GTM10', GTM11 AS 'GTM11', GTM12 AS 'GTM12',
                                                        GTM13 AS 'GTM13', GTM14 AS 'GTM14', GTM15 AS 'GTM15',
                                                        GTM16 AS 'GTM16'))
          WHERE DATA_OBJECT_ID = 42001
          ) t1
        INNER JOIN (SELECT TABLE_NAME, TABLE_NAME NAME_TABLE, COLUMN_NAME, COMMENTS FROM DBA_COL_COMMENTS) t2 
                    ON t1.ORDER_MODE = t2.COLUMN_NAME AND t1.TABLE_NAME = t2.TABLE_NAME
--ORDER BY ORDER_NUM, ORDER_MODE
GROUP BY COMMENTS, NAME_TABLE



План запроса
+

 Plan Hash Value  : 3417009169 

-----------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                        | Name                        | Rows  | Bytes   | Cost | Time     |
-----------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                 |                             |     1 |     175 |  732 | 00:00:09 |
|    1 |   HASH GROUP BY                                  |                             |     1 |     175 |  732 | 00:00:09 |
|  * 2 |    FILTER                                        |                             |       |         |      |          |
|    3 |     PX COORDINATOR                               |                             |       |         |      |          |
|    4 |      PX SEND QC (RANDOM)                         | :TQ10006                    |     1 |     175 |  732 | 00:00:09 |
|    5 |       NESTED LOOPS OUTER                         |                             |     1 |     175 |  732 | 00:00:09 |
|    6 |        NESTED LOOPS                              |                             |     1 |     136 |  732 | 00:00:09 |
|    7 |         NESTED LOOPS                             |                             |     1 |     132 |  731 | 00:00:09 |
|    8 |          NESTED LOOPS                            |                             |     1 |     110 |  731 | 00:00:09 |
|  * 9 |           HASH JOIN                              |                             |  1812 |  155832 |  683 | 00:00:09 |
|   10 |            PX RECEIVE                            |                             |  4257 |  191565 |  249 | 00:00:03 |
|   11 |             PX SEND HASH                         | :TQ10005                    |  4257 |  191565 |  249 | 00:00:03 |
|   12 |              BUFFER SORT                         |                             |     1 |     175 |  732 | 00:00:09 |
|   13 |               VIEW                               |                             |  4257 |  191565 |  249 | 00:00:03 |
|   14 |                UNION-ALL                         |                             |       |         |      |          |
| * 15 |                 VIEW                             |                             |   817 |   26961 |   56 | 00:00:01 |
|   16 |                  UNPIVOT                         |                             |       |         |      |          |
|   17 |                   PX BLOCK ITERATOR              |                             |    43 |    5461 |    2 | 00:00:01 |
| * 18 |                    TABLE ACCESS FULL             | DATA_OBJECT_ALL             |    43 |    5461 |    2 | 00:00:01 |
|   19 |                 BUFFER SORT                      |                             |       |         |      |          |
|   20 |                  PX RECEIVE                      |                             |       |         |      |          |
|   21 |                   PX SEND ROUND-ROBIN            | :TQ10000                    |       |         |      |          |
| * 22 |                    VIEW                          |                             |   473 |   16082 |   22 | 00:00:01 |
|   23 |                     UNPIVOT                      |                             |       |         |      |          |
|   24 |                      TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_BASE            |    43 |    2365 |    2 | 00:00:01 |
| * 25 |                       INDEX RANGE SCAN           | DOBJECT_BASE_DOBJECT_ID_IDX |    43 |         |    1 | 00:00:01 |
|   26 |                 BUFFER SORT                      |                             |       |         |      |          |
|   27 |                  PX RECEIVE                      |                             |       |         |      |          |
|   28 |                   PX SEND ROUND-ROBIN            | :TQ10001                    |       |         |      |          |
| * 29 |                    VIEW                          |                             |  1419 |   53922 |   99 | 00:00:02 |
|   30 |                     UNPIVOT                      |                             |       |         |      |          |
|   31 |                      TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_NEW_WELL        |    43 |    6235 |    3 | 00:00:01 |
| * 32 |                       INDEX RANGE SCAN           | DOBJECT_NEW_DOBJECT_ID_IDX  |    43 |         |    1 | 00:00:01 |
|   33 |                 BUFFER SORT                      |                             |       |         |      |          |
|   34 |                  PX RECEIVE                      |                             |       |         |      |          |
|   35 |                   PX SEND ROUND-ROBIN            | :TQ10002                    |       |         |      |          |
| * 36 |                    VIEW                          |                             |   860 |   33540 |   40 | 00:00:01 |
|   37 |                     UNPIVOT                      |                             |       |         |      |          |
|   38 |                      TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_SIDE_WELL       |    43 |    3182 |    2 | 00:00:01 |
| * 39 |                       INDEX RANGE SCAN           | DOBJECT_SIDE_DOBJECT_ID_IDX |    43 |         |    1 | 00:00:01 |
|   40 |                 BUFFER SORT                      |                             |       |         |      |          |
|   41 |                  PX RECEIVE                      |                             |   688 |   22704 |   32 | 00:00:01 |
|   42 |                   PX SEND ROUND-ROBIN            | :TQ10003                    |   688 |   22704 |   32 | 00:00:01 |
| * 43 |                    VIEW                          |                             |   688 |   22704 |   32 | 00:00:01 |
|   44 |                     UNPIVOT                      |                             |       |         |      |          |
|   45 |                      TABLE ACCESS BY INDEX ROWID | DATA_OBJECT_GTM             |    43 |    3569 |    2 | 00:00:01 |
| * 46 |                       INDEX RANGE SCAN           | DOBJECT_GTM_DOBJECT_ID_IDX  |    43 |         |    1 | 00:00:01 |
|   47 |            BUFFER SORT                           |                             |       |         |      |          |
|   48 |             PX RECEIVE                           |                             | 29049 | 1191009 |  434 | 00:00:06 |
|   49 |              PX SEND HASH                        | :TQ10004                    | 29049 | 1191009 |  434 | 00:00:06 |
| * 50 |               INDEX FAST FULL SCAN               | I_OBJ2                      | 29049 | 1191009 |  434 | 00:00:06 |
| * 51 |           TABLE ACCESS CLUSTER                   | COL$                        |     1 |      24 |    1 | 00:00:01 |
| * 52 |            INDEX UNIQUE SCAN                     | I_OBJ#                      |     1 |         |    0 | 00:00:01 |
| * 53 |          INDEX RANGE SCAN                        | I_USER2                     |     1 |      22 |    1 | 00:00:01 |
| * 54 |         INDEX RANGE SCAN                         | I_USER2                     |     1 |       4 |    1 | 00:00:01 |
|   55 |        TABLE ACCESS BY INDEX ROWID               | COM$                        |     1 |      39 |    2 | 00:00:01 |
| * 56 |         INDEX UNIQUE SCAN                        | I_COM1                      |     1 |         |    1 | 00:00:01 |
|   57 |     NESTED LOOPS                                 |                             |     1 |      31 |    3 | 00:00:01 |
| * 58 |      INDEX SKIP SCAN                             | I_USER2                     |     1 |      20 |    1 | 00:00:01 |
| * 59 |      INDEX RANGE SCAN                            | I_OBJ4                      |     1 |      11 |    2 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
  "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR "O"."TYPE#"=4 AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
  "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#"
  AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
* 9 - access("T1"."TABLE_NAME"="O"."NAME")
* 15 - filter("unpivot_view_042"."YEARLY_TOTAL" IS NOT NULL)
* 18 - filter("DATA_OBJECT_ALL"."DATA_OBJECT_ID"=42001)
* 22 - filter("unpivot_view_054"."YEARLY_TOTAL" IS NOT NULL)
* 25 - access("DATA_OBJECT_BASE"."DATA_OBJECT_ID"=42001)
* 29 - filter("unpivot_view_088"."YEARLY_TOTAL" IS NOT NULL)
* 32 - access("DATA_OBJECT_NEW_WELL"."DATA_OBJECT_ID"=42001)
* 36 - filter("unpivot_view_109"."YEARLY_TOTAL" IS NOT NULL)
* 39 - access("DATA_OBJECT_SIDE_WELL"."DATA_OBJECT_ID"=42001)
* 43 - filter("unpivot_view_126"."YEARLY_TOTAL" IS NOT NULL)
* 46 - access("DATA_OBJECT_GTM"."DATA_OBJECT_ID"=42001)
* 50 - filter("O"."TYPE#"=2 OR "O"."TYPE#"=4)
* 51 - filter(BITAND("C"."PROPERTY",32)=0 AND "T1"."ORDER_MODE"="C"."NAME")
* 52 - access("O"."OBJ#"="C"."OBJ#")
* 53 - access("O"."OWNER#"="U"."USER#")
* 54 - access("O"."SPARE3"="U"."USER#")
* 56 - access("C"."OBJ#"="CO"."OBJ#"(+) AND "C"."INTCOL#"="CO"."COL#"(+))
* 56 - filter("CO"."COL#"(+) IS NOT NULL)
* 58 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 58 - filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 59 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")



Ситуация не нравиться.
В декоде годов еще меньше, так что там может быть производительность быть хуже.
Самое обидное, что агрегировать не обязательно.
Данные по годам уникальные.
Но понимаю, что без этого нельзя сделать.

Спасибо большое!
18 июл 19, 06:53    [21929051]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз с производительностью запроса  [new]
kaldorey
Member

Откуда:
Сообщений: 600
vadipok,

Возможно, стоит получить результат, не делая unpivot.
18 июл 19, 08:13    [21929075]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить