[ТЕМА ЗАКРЫТА]
Опции темы
02.02.2009 13:17  
OlegON
Сделйте, пожалуйста...
Код:
 explain plan for   SELECT SP.SPECITEM , SC.DOCTYPE , SC.DOCID , SC.SPECITEM SPECITEM_SRC , SC.ITEMPRICE , SC.ITEMPRICENOTAX FROM SMSPEC SP , SMSPEC SC , SMDOCUMENTS D WHERE SP.DOCTYPE = :b1 AND SP.DOCID = :b2 AND ( ( :b3 = '0' ) OR NVL(SP.ITEMPRICE , 0 ) = 0 ) AND SC.DOCTYPE = D.DOCTYPE AND SC.DOCID = D.ID AND D.CREATEDAT <= :b4 AND D.DOCTYPE = 'WI' AND D.DOCSTATE = 3 AND D.OPCODE = 0 AND SC.ARTICLE = SP.ARTICLE AND D.LOCATIONTO IN (SELECT ID FROM TTSHOPLIST ) AND ( :b1 != 'WI' OR D.ID != :b2 ) AND D.ID IN (SELECT MAX (D2.ID) FROM SMSPEC S2 , SMDOCUMENTS D2 WHERE S2.DOCTYPE = D2.DOCTYPE AND S2.DOCID = D2.ID AND D2.DOCTYPE = 'WI' AND D2.DOCSTATE = 3 AND D2.OPCODE = 0 AND S2.ARTICLE = SP.ARTICLE AND D2.LOCATIONTO IN (SELECT ID FROM TTSHOPLIST ) AND ( :b1 != 'WI' OR D2.ID != :b2 ) AND D2.CREATEDAT IN (SELECT MAX (D1.CREATEDAT) FROM SMSPEC S1 , SMDOCUMENTS D1 WHERE S1.DOCTYPE = D1.DOCTYPE AND S1.DOCID = D1.ID AND D1.CREATEDAT <= :b4 AND D1.DOCTYPE = 'WI' AND D1.DOCSTATE = 3 AND D1.OPCODE = 0 AND S1.ARTICLE = S2.ARTICLE AND D1.LOCATIONTO IN (SELECT ID FROM TTSHOPLIST ) AND ( :b1 != 'WI' OR D1.ID != :b2 ) ) ) AND SC.SPECITEM IN (SELECT MAX (S3.SPECITEM) FROM SMSPEC S3 WHERE S3.DOCTYPE = D.DOCTYPE AND S3.DOCID = D.ID AND S3.ARTICLE = SP.ARTICLE );
у меня что-то нехорошее...
Код:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |     1 |   112 |    12 |       |       |
|   1 |  NESTED LOOPS                           |                    |     1 |   112 |     4 |       |       |
|   2 |   NESTED LOOPS                          |                    |     1 |    76 |     3 |       |       |
|   3 |    NESTED LOOPS                         |                    |     1 |    63 |     2 |       |       |
|   4 |     PARTITION LIST SINGLE               |                    |     1 |    30 |     1 |   KEY |   KEY |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID  | SMSPEC             |     1 |    30 |     1 |   KEY |   KEY |
|   6 |       INDEX RANGE SCAN                  | SMCSPEC_DISPLAYPOS |    22 |       |     1 |   KEY |   KEY |
|   7 |     TABLE ACCESS BY INDEX ROWID         | SMDOCUMENTS        |     1 |    33 |     1 |       |       |
|   8 |      INDEX UNIQUE SCAN                  | SMCDOCUMENTS_PK    |     1 |       |     1 |       |       |
|   9 |       SORT AGGREGATE                    |                    |     1 |   104 |       |       |       |
|  10 |        NESTED LOOPS                     |                    |     1 |   104 |     7 |       |       |
|  11 |         NESTED LOOPS                    |                    |     1 |    91 |     6 |       |       |
|  12 |          NESTED LOOPS                   |                    |     1 |    58 |     5 |       |       |
|  13 |           VIEW                          | VW_SQ_1            |     1 |    36 |     4 |       |       |
|  14 |            SORT GROUP BY                |                    |     1 |    74 |     4 |       |       |
|  15 |             NESTED LOOPS                |                    |     1 |    74 |     4 |       |       |
|  16 |              MERGE JOIN CARTESIAN       |                    |     1 |    41 |     3 |       |       |
|  17 |               TABLE ACCESS FULL         | TTSHOPLIST         |     1 |    13 |     2 |       |       |
|  18 |               BUFFER SORT               |                    |   438 | 12264 |     1 |       |       |
|  19 |                PARTITION LIST SINGLE    |                    |   438 | 12264 |     1 |   KEY |   KEY |
|  20 |                 INDEX RANGE SCAN        | SMSPEC_ART         |   438 | 12264 |     1 |     6 |     6 |
|  21 |              TABLE ACCESS BY INDEX ROWID| SMDOCUMENTS        |     1 |    33 |     1 |       |       |
|  22 |               INDEX UNIQUE SCAN         | SMCDOCUMENTS_PK    |     1 |       |     1 |       |       |
|  23 |           PARTITION LIST SINGLE         |                    |     1 |    22 |     1 |   KEY |   KEY |
|  24 |            INDEX RANGE SCAN             | SMSPEC_ART         |     1 |    22 |     1 |     6 |     6 |
|  25 |          TABLE ACCESS BY INDEX ROWID    | SMDOCUMENTS        |     1 |    33 |     1 |       |       |
|  26 |           INDEX UNIQUE SCAN             | SMCDOCUMENTS_PK    |     1 |       |     1 |       |       |
|  27 |         INDEX UNIQUE SCAN               | TTCSHOPLIST_PK     |     1 |    13 |     1 |       |       |
|  28 |    INDEX UNIQUE SCAN                    | TTCSHOPLIST_PK     |     1 |    13 |     1 |       |       |
|  29 |   PARTITION LIST SINGLE                 |                    |     1 |    36 |     1 |   KEY |   KEY |
|  30 |    TABLE ACCESS BY LOCAL INDEX ROWID    | SMSPEC             |     1 |    36 |     1 |     6 |     6 |
|  31 |     INDEX UNIQUE SCAN                   | SMCSPEC_PK         |     1 |       |     1 |     6 |     6 |
|  32 |      SORT AGGREGATE                     |                    |     1 |    26 |       |       |       |
|  33 |       PARTITION LIST SINGLE             |                    |     1 |    26 |     1 |   KEY |   KEY |
|  34 |        TABLE ACCESS BY LOCAL INDEX ROWID| SMSPEC             |     1 |    26 |     1 |   KEY |   KEY |
|  35 |         INDEX RANGE SCAN                | SMSPEC_ART         |     1 |       |     1 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------
 
02.02.2009 17:36  
vdm
На 10-м оракле надо или ?

- 9.2.08
- всяческие smspec - стандартные
Код:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name               | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |     1 |    97 |     4 |
|   1 |  NESTED LOOPS                         |                     |     1 |    97 |     4 |
|   2 |   NESTED LOOPS                        |                     |     1 |    66 |     3 |
|   3 |    NESTED LOOPS                       |                     |     1 |    53 |     2 |
|*  4 |     TABLE ACCESS BY INDEX ROWID       | SMSPEC              |     1 |    27 |     1 |
|*  5 |      INDEX RANGE SCAN                 | SMCSPEC_DISPLAYPOS  |     1 |       |     4 |
|*  6 |     TABLE ACCESS BY INDEX ROWID       | SMDOCUMENTS         |     1 |    26 |     1 |
|*  7 |      INDEX UNIQUE SCAN                | SMCDOCUMENTS_PK     |     1 |       |       |
|   8 |       SORT AGGREGATE                  |                     |     1 |    59 |       |
|*  9 |        FILTER                         |                     |       |       |       |
|  10 |         NESTED LOOPS                  |                     |     1 |    59 |     5 |
|  11 |          NESTED LOOPS                 |                     |     1 |    46 |     4 |
|* 12 |           INDEX RANGE SCAN            | SMSPEC_ART          |    18 |   360 |     4 |
|* 13 |           TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS         |     1 |    26 |     1 |
|* 14 |            INDEX UNIQUE SCAN          | SMCDOCUMENTS_PK     |     1 |       |       |
|* 15 |          INDEX UNIQUE SCAN            | TTCSHOPLIST_PK      |   233 |  3029 |       |
|  16 |         SORT AGGREGATE                |                     |     1 |    59 |       |
|  17 |          NESTED LOOPS                 |                     |     1 |    59 |     5 |
|  18 |           NESTED LOOPS                |                     |     1 |    46 |     4 |
|* 19 |            INDEX RANGE SCAN           | SMSPEC_ART          |    18 |   360 |     4 |
|* 20 |            TABLE ACCESS BY INDEX ROWID| SMDOCUMENTS         |     1 |    26 |     1 |
|* 21 |             INDEX UNIQUE SCAN         | SMCDOCUMENTS_PK     |     1 |       |       |
|* 22 |           INDEX UNIQUE SCAN           | TTCSHOPLIST_PK      |   233 |  3029 |       |
|* 23 |    INDEX UNIQUE SCAN                  | TTCSHOPLIST_PK      |   233 |  3029 |       |
|* 24 |   TABLE ACCESS BY INDEX ROWID         | SMSPEC              |     1 |    31 |     1 |
|* 25 |    INDEX UNIQUE SCAN                  | SMCSPEC_PK          |     1 |       |       |
|  26 |     SORT AGGREGATE                    |                     |     1 |    23 |       |
|  27 |      TABLE ACCESS BY INDEX ROWID      | SMSPEC              |     1 |    23 |     1 |
|* 28 |       INDEX RANGE SCAN                | SMSPEC_ART          |     1 |       |     4 |
---------------------------------------------------------------------------------------------
 
03.02.2009 07:41  
kadr
а не последствия ли это того что мат. представление создавал?
 
03.02.2009 08:00  
kadr
у меня такой план
Код:
 PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              |  Name               | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |     1 |   102 |     5  (20)|       |       |
|   1 |  NESTED LOOPS                          |                     |     1 |   102 |     5  (20)|       |       |
|   2 |   NESTED LOOPS                         |                     |     1 |    73 |     4  (25)|       |       |
|   3 |    NESTED LOOPS                        |                     |     1 |    60 |     3  (34)|       |       |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID | SMSPEC              |     1 |    33 |     2  (50)| ROWID | ROW L |
|*  5 |      INDEX RANGE SCAN                  | SMCSPEC_DISPLAYPOS  |     2 |       |     5  (20)|       |       |
|*  6 |     TABLE ACCESS BY INDEX ROWID        | SMDOCUMENTS         |     1 |    27 |     2  (50)|       |       |
|*  7 |      INDEX UNIQUE SCAN                 | SMCDOCUMENTS_PK     |     1 |       |     2  (50)|       |       |
|   8 |       SORT AGGREGATE                   |                     |     1 |    58 |            |       |       |
|*  9 |        FILTER                          |                     |       |       |            |       |       |
|  10 |         NESTED LOOPS                   |                     |     1 |    58 |    48   (0)|       |       |
|  11 |          NESTED LOOPS                  |                     |     1 |    45 |    47   (0)|       |       |
|* 12 |           INDEX RANGE SCAN             | SMSPEC_ART          |   223 |  4014 |    18   (6)|       |       |
|* 13 |           TABLE ACCESS BY INDEX ROWID  | SMDOCUMENTS         |     1 |    27 |     2  (50)|       |       |
|* 14 |            INDEX UNIQUE SCAN           | SMCDOCUMENTS_PK     |     1 |       |     2  (50)|       |       |
|* 15 |          INDEX UNIQUE SCAN             | TTCSHOPLIST_PK      |   194 |  2522 |            |       |       |
|  16 |         SORT AGGREGATE                 |                     |     1 |    58 |            |       |       |
|  17 |          NESTED LOOPS                  |                     |     1 |    58 |    48   (0)|       |       |
|  18 |           NESTED LOOPS                 |                     |     1 |    45 |    47   (0)|       |       |
|* 19 |            INDEX RANGE SCAN            | SMSPEC_ART          |   223 |  4014 |    18   (6)|       |       |
|* 20 |            TABLE ACCESS BY INDEX ROWID | SMDOCUMENTS         |     1 |    27 |     2  (50)|       |       |
|* 21 |             INDEX UNIQUE SCAN          | SMCDOCUMENTS_PK     |     1 |       |     2  (50)|       |       |
|* 22 |           INDEX UNIQUE SCAN            | TTCSHOPLIST_PK      |   194 |  2522 |            |       |       |
|* 23 |    INDEX UNIQUE SCAN                   | TTCSHOPLIST_PK      |   194 |  2522 |            |       |       |
|* 24 |   TABLE ACCESS BY GLOBAL INDEX ROWID   | SMSPEC              |     1 |    29 |     2  (50)|     2 |     2 |
|* 25 |    INDEX UNIQUE SCAN                   | SMCSPEC_PK          |     1 |       |     3  (34)|       |       |
|  26 |     SORT AGGREGATE                     |                     |     1 |    29 |            |       |       |
|  27 |      TABLE ACCESS BY GLOBAL INDEX ROWID| SMSPEC              |     1 |    29 |     2  (50)| ROWID | ROW L |
|* 28 |       INDEX RANGE SCAN                 | SMSPEC_ART          |     1 |       |     5  (20)|       |       |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(NVL("SYS_ALIAS_4"."ITEMPRICE",0)=0 OR :Z='0')
   5 - access("SYS_ALIAS_4"."DOCTYPE"=:Z AND "SYS_ALIAS_4"."DOCID"=:Z)
   6 - filter("SYS_ALIAS_3"."LOCATIONTO" IS NOT NULL AND "SYS_ALIAS_3"."OPCODE"=0 AND "SYS_ALIAS_3"."DOCSTATE"=3
              AND "SYS_ALIAS_3"."CREATEDAT"<=:Z AND ("SYS_ALIAS_3"."ID"<>:Z OR :Z<>'WI'))
   7 - access("SYS_ALIAS_3"."DOCTYPE"='WI' AND "SYS_ALIAS_3"."ID"= (SELECT /*+ */ MAX("D2"."ID") FROM
              "SMDOCUMENTS" "D2","SMSPEC" "SYS_ALIAS_1","TTSHOPLIST" "TTSHOPLIST" WHERE "D2"."CREATEDAT"= (SELECT /*+ */
              MAX("D1"."CREATEDAT") FROM "SMDOCUMENTS" "D1","SMSPEC" "S1","TTSHOPLIST" "TTSHOPLIST" WHERE
              "D1"."LOCATIONTO"="TTSHOPLIST"."ID" AND "S1"."DOCTYPE"='WI' AND "S1"."ARTICLE"=:B1 AND "S1"."DOCID"="D1"."ID" AND
              "D1"."DOCTYPE"='WI' AND "D1"."LOCATIONTO" IS NOT NULL AND "D1"."OPCODE"=0 AND "D1"."DOCSTATE"=3 AND
              "D1"."CREATEDAT"<=:Z AND ("D1"."ID"<>:Z OR :Z<>'WI') AND "S1"."DOCTYPE"="D1"."DOCTYPE") AND
              "D2"."LOCATIONTO"="TTSHOPLIST"."ID" AND "SYS_ALIAS_1"."DOCTYPE"='WI' AND "SYS_ALIAS_1"."ARTICLE"=:B2 AND
              "SYS_ALIAS_1"."DOCID"="D2"."ID" AND "D2"."DOCTYPE"='WI' AND "D2"."LOCATIONTO" IS NOT NULL AND "D2"."OPCODE"=0 AND
              "D2"."DOCSTATE"=3 AND ("D2"."ID"<>:Z OR :Z<>'WI') AND "SYS_ALIAS_1"."DOCTYPE"="D2"."DOCTYPE"))
   9 - filter("D2"."CREATEDAT"= (SELECT /*+ */ MAX("D1"."CREATEDAT") FROM "SMDOCUMENTS" "D1","SMSPEC"
              "S1","TTSHOPLIST" "TTSHOPLIST" WHERE "D1"."LOCATIONTO"="TTSHOPLIST"."ID" AND "S1"."DOCTYPE"='WI' AND
              "S1"."ARTICLE"=:B1 AND "S1"."DOCID"="D1"."ID" AND "D1"."DOCTYPE"='WI' AND "D1"."LOCATIONTO" IS NOT NULL AND
              "D1"."OPCODE"=0 AND "D1"."DOCSTATE"=3 AND "D1"."CREATEDAT"<=:Z AND ("D1"."ID"<>:Z OR :Z<>'WI') AND
              "S1"."DOCTYPE"="D1"."DOCTYPE"))
  12 - access("SYS_ALIAS_1"."ARTICLE"=:B1 AND "SYS_ALIAS_1"."DOCTYPE"='WI')
  13 - filter("D2"."LOCATIONTO" IS NOT NULL AND "D2"."OPCODE"=0 AND "D2"."DOCSTATE"=3 AND ("D2"."ID"<>:Z OR
              :Z<>'WI'))
  14 - access("D2"."DOCTYPE"='WI' AND "SYS_ALIAS_1"."DOCID"="D2"."ID")
       filter("SYS_ALIAS_1"."DOCTYPE"="D2"."DOCTYPE")
  15 - access("D2"."LOCATIONTO"="TTSHOPLIST"."ID")
  19 - access("S1"."ARTICLE"=:B1 AND "S1"."DOCTYPE"='WI')
  20 - filter("D1"."LOCATIONTO" IS NOT NULL AND "D1"."OPCODE"=0 AND "D1"."DOCSTATE"=3 AND "D1"."CREATEDAT"<=:Z AND
              ("D1"."ID"<>:Z OR :Z<>'WI'))
  21 - access("D1"."DOCTYPE"='WI' AND "S1"."DOCID"="D1"."ID")
       filter("S1"."DOCTYPE"="D1"."DOCTYPE")
  22 - access("D1"."LOCATIONTO"="TTSHOPLIST"."ID")
  23 - access("SYS_ALIAS_3"."LOCATIONTO"="TTSHOPLIST"."ID")
  24 - filter("SC"."ARTICLE"="SYS_ALIAS_4"."ARTICLE")
  25 - access("SC"."DOCTYPE"='WI' AND "SC"."DOCID"="SYS_ALIAS_3"."ID" AND "SC"."SPECITEM"= (SELECT /*+ */
              MAX("S3"."SPECITEM") FROM "SMSPEC" "S3" WHERE "S3"."DOCID"=:B1 AND "S3"."DOCTYPE"=:B2 AND "S3"."ARTICLE"=:B3))
       filter("SC"."DOCTYPE"="SYS_ALIAS_3"."DOCTYPE")
  28 - access("S3"."ARTICLE"=:B1 AND "S3"."DOCTYPE"=:B2 AND "S3"."DOCID"=:B3)
 
03.02.2009 09:06  
OlegON
Цитата:
Сообщение от kadr
а не последствия ли это того что мат. представление создавал?
Сомнительно... Пока размышляю. Придумаю - скажу. Хотя в принципе результаты работы по времени терпимые, это юзеров я избаловал :)
 
 
Опции темы



Часовой пояс GMT +3, время: 06:57.

Все в прочитанное - Календарь - RSS - - Карта - Вверх 👫 Яндекс.Метрика
Форум сделан на основе vBulletin®
Copyright ©2000 - 2016, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.