Хочу научиться читать SQL...

zVlad · Post by **zVlad** » 18 Jan 2015 17:26

Эта тема специально для iDesperedo. Не хочу превращать деловую тему в помойку перетягивания каната в области "умения читать SQL".

Вот простенький SQL (у мeня их несколько от последней миграции, так что не соскучимся) который мы совсем недавно выявили и попросили обратиьть на него внимание разработчиков. Прочитайте и Вы, iDesperado, этот SQL с умением и рассказы что было в нем не так и что разработчики в итоге сдеали с ним. Спасибо.

Code: Select all

SELECT WOTSK.FACILITY , WOTSK.UNIT , WOTSK.INTERRUPT
       , WOTSK.SHUTDOWN_NBR , WOTSK.WORK_ORDER_NBR
       , WOTSK.WORK_ORDER_TASK , WOTSK.TASK_SEQUENCE
       , WOTSK.WO_TSK_STATUS
       , WOTSK.WO_TSK_PRIORITY
       , WOTSK.NEED_DATE , WOTSK.WR_TASK_TITLE
 FROM AS07DADM.TIDWOTSK WOTSK
 WHERE ( WOTSK.WORK_ORDER_NBR BETWEEN ? AND ? )
 AND ( EXISTS ( SELECT 1 FROM AS07DADM.TIDWOTSK WOTS1
      WHERE ( WOTS1.WORK_ORDER_NBR = WOTSK.WORK_ORDER_NBR )
       AND ( WOTS1.WORK_ORDER_TASK = WOTSK.WORK_ORDER_TASK )
     AND ( ( WOTS1.FACILITY = ? ) OR ( 1 = 0 ) )
     AND ( ( WOTS1 .UNIT BETWEEN ? AND ? ) OR ( 1 = 0 ) )
      AND ( ( WOTS1.WO_TSK_STATUS    = '45' )
             OR ( WOTS1.WO_TSK_STATUS = '48' ) )
         ) OR ( 1 = 0 )
           )
      AND ( ( WOTSK.WORK_ORDER_NBR || WOTSK.WORK_ORDER_TASK
           BETWEEN ?  AND ?  ) OR ( 1 = 0 ) )
ORDER BY WOTSK.WORK_ORDER_NBR DESC
      , WOTSK.WORK_ORDER_TASK DESC;

Индексы (я их не для того привожу что хочу узнать от Вас какие именно индексы используются. Более того они и не рассматривались когда делались рекомендации. Просто предваряя возможный интерес к ним привожу):

Code: Select all

  CREATE UNIQUE INDEX TIIWOTSK ON TIDWOTSK
      (
         WORK_ORDER_NBR DESC,                                             0533
         WORK_ORDER_TASK DESC                                             0299
      )
....
  CREATE UNIQUE INDEX TIIWOTS1 ON TIDWOTSK
      (
         FACILITY,                                                        0533
         UNIT,                                                            0533
         SYSTEM_CODE,                                                     0533
         EQUIPMENT_TYPE,                                                  0533
         EQUIPMENT_NUMBER,                                                0533
         DISCIPLINE,                                                      0533
         WORK_ORDER_NBR,                                                  0533
         WORK_ORDER_TASK,                                                 0533
         WO_TSK_STATUS                                                    0533
      )
...
  CREATE        INDEX TIIWOTS2 ON TIDWOTSK
      (
         UTC_NUMBER
      )
....
  CREATE        INDEX TIIWOTS3 ON TIDWOTSK
      (
         E_CODE,
         WORK_ORDER_NBR,
         WO_TSK_STATUS                                                    0533
      )
...
   CREATE      INDEX TIIWOTS4 ON TIDWOTSK
      (
         WORK_ORDER_NBR,
         WORK_ORDER_TASK,                                                 0533
         WO_TSK_STATUS,
         NEED_DATE,
         WORK_ORDER_TYPE,
         UNIT,                                                            0533
         SYSTEM_CODE,                                                     0533
         DISCIPLINE,
         WO_TSK_PRIORITY
      )
....
   CREATE UNIQUE INDEX TIIWOTS5 ON TIDWOTSK
      (
         KEY_REF_NBR,
         KEY_REF_SUB_NBR,
         KEY_REF_TYPE
      )
...
  CREATE        INDEX TIIWOTS6 ON TIDWOTSK
      (
       LOCATION_ID,
       LOCATION_REGION,
       WORK_ORDER_NBR
      )
....
  CREATE        INDEX TIIWOTS7 ON TIDWOTSK
      (
       REFERENCE_TYPE,
       REFERENCE_NBR
      )
...
  CREATE UNIQUE INDEX TIIWOTS9 ON TIDWOTSK
      (
         FACILITY,
         WORK_ORDER_NBR,
         WORK_ORDER_TASK
      )
.....
  CREATE        INDEX TIIWOTSA ON TIDWOTSK
      (
       CLIENT_ID
      )
..............
  CREATE        INDEX TIIWOTSB ON TIDWOTSK
      (
         CATALOG_ID,
         Q_LEVEL,
         FACILITY
      )

iDesperado · Post by **iDesperado** » 18 Jan 2015 17:48

Влад, вы снова ярко показали, что не имеете никакого опыта в db2 и никогда не были DBA. если бы вы имели опыт, вы бы показали бы мне план запроса, а не перечисляли индексы, которые без дополнительной информации все равно особой смысловой нагрузки не несут. если бы вы имели опыт, вы бы знали, что наличие или отсутствие индекса это вообще не о чем, обсуждать имеет смысл только план и статистику по запросу.
например у вас не видно отдельного индекса по WORK_ORDER_NBR, но сказать, что без него будет плохой план не возможно, т.к. есть миллион вариантов, когда выгодней фулскан. кроме этого если речь идет о миграции, сортировка там явно лишняя.

zgur · Post by **zgur** » 18 Jan 2015 18:00

По мне так весь Exists лишний т.к. он присоединяется к основному запросу по первичному ключу. Если exists убрать (мигрировав все условия в главный запрос), то следующей проблемой я бы назвал условие "WOTSK.WORK_ORDER_NBR || WOTSK.WORK_ORDER_TASK BETWEEN ? AND ?". такое условие практически никогда не позволяет нспользовать индексы и зависит от внутренней структуры WORK_ORDER_NBR. Я бы постарался его заменить на два независимых условия по WORK_ORDER_NBR и WORK_ORDER_TASK.

Оптимизировать этот конкретный экземпляр не сложно, но присутствие условий типа OR ( 1 = 0 ) , дает повод думать, что SQL динамически сгенерирован и его прямое изменение либо невозможно, либо нетривиально.

iDesperado · Post by **iDesperado** » 18 Jan 2015 18:20

zgur wrote:По мне так весь Exists лишний т.к. он присоединяется к основному запросу по первичному ключу. Если exists убрать (мигрировав все условия в главный запрос), то следующей проблемой я бы назвал условие "WOTSK.WORK_ORDER_NBR || WOTSK.WORK_ORDER_TASK BETWEEN ? AND ?". такое условие практически никогда не позволяет нспользовать индексы и зависит от внутренней структуры WORK_ORDER_NBR. Я бы постарался его заменить на два независимых условия по WORK_ORDER_NBR и WORK_ORDER_TASK.

тут не соглашусь. exists не должен быть проблемой, т.к. все равно запрос будет переписан оптимизатором и план будет идентичен запросу без exists. WOTSK.WORK_ORDER_NBR || WOTSK.WORK_ORDER_TASK может индекс TIIWOTSK использовать.

zgur · Post by **zgur** » 18 Jan 2015 19:25

iDesperado wrote: тут не соглашусь. exists не должен быть проблемой, т.к. все равно запрос будет переписан оптимизатором и план будет идентичен запросу без exists.

В этом конкретном случае join elimination работать не будет т.к. первичный ключ состоит из двух столбцов. Вот похожий пример - если его поменять, чтобы только object_id был в ключе, работает как вы говорите.

Code: Select all

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 18 10:14:01 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 18 2015 09:53:00 -08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set pages 1000 lines 1000
SQL> CREATE TABLE test   (object_id INTEGER,owner varchar2(32),object_name varchar2(32) );

Table created.

SQL> INSERT INTO test
SELECT object_id,owner,object_name FROM dba_objects WHERE OBJECT_id IS NOT NULL  ;  2  

93646 rows created.

SQL> CREATE UNIQUE INDEX ind ON test (object_id,owner);  

Index created.

SQL> EXEC dbms_stats.gather_table_stats(NULL,'TEST'); 

PL/SQL procedure successfully completed.


SQL> EXPLAIN PLAN  FOR 
SELECT * FROM test t1
WHERE EXISTS (SELECT * FROM test t2 WHERE t1.object_id = t2.OBJECT_id AND t1.owner = t2.owner AND  object_name LIKE 'A%')
AND OBJECT_name LIKE '%A' 
/

Explained.

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT


Plan hash value: 3304307699

-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	  1 |	 72 |	288   (1)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|	    |	  1 |	 72 |	288   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | TEST |  1266 | 45576 |	144   (1)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | TEST |  4682 |	164K|	144   (1)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID" AND
	      "T1"."OWNER"="T2"."OWNER")
   2 - filter("OBJECT_NAME" LIKE 'A%')
   3 - filter("OBJECT_NAME" LIKE '%A' AND "OBJECT_NAME" IS NOT NULL)

18 rows selected.

SQL>

zgur · Post by **zgur** » 18 Jan 2015 19:32

iDesperado wrote: WOTSK.WORK_ORDER_NBR || WOTSK.WORK_ORDER_TASK может индекс TIIWOTSK использовать.

При конкатенации, не индекс на индивидуальные колонки, не индекс на обе колонки работать не будетю Тольно function based на эту операцию. Продлжение примера

Code: Select all

SQL> EXPLAIN PLAN  FOR 
SELECT * FROM test t1  WHERE object_id||owner = '122system' ;  2  

Explained.

SQL> SELECT * FROM table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   936 | 33696 |   144   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   936 | 33696 |   144   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_CHAR("OBJECT_ID")||"OWNER"='122system')

sp123 · Post by **sp123** » 18 Jan 2015 19:33

Запрос действительно простенький. Помимо execution plan не мешало бы также уточнить размеры таблиц и что примерно передается в bind variables в момент запроса - от выгребания, к примеру, 2% или половины большой таблицы рекомендации могут отличаться. А вообще да, динамический SQL - зло. Но видимо, в данном случае была нужда.

zVlad · Post by **zVlad** » 18 Jan 2015 19:52

zgur wrote:По мне так весь Exists лишний т.к. он присоединяется к основному запросу по первичному ключу. Если exists убрать (мигрировав все условия в главный запрос), то следующей проблемой я бы назвал условие "WOTSK.WORK_ORDER_NBR || WOTSK.WORK_ORDER_TASK BETWEEN ? AND ?". такое условие практически никогда не позволяет нспользовать индексы и зависит от внутренней структуры WORK_ORDER_NBR. Я бы постарался его заменить на два независимых условия по WORK_ORDER_NBR и WORK_ORDER_TASK.

Оптимизировать этот конкретный экземпляр не сложно, но присутствие условий типа OR ( 1 = 0 ) , дает повод думать, что SQL динамически сгенерирован и его прямое изменение либо невозможно, либо нетривиально.

Это самый лучший и полный анализ. SQL не сгенерирован динамически и первое что было предложено это как раз убрать OR (1=0). Одно лишь это радикально меняет план доступа. Даже убирание одного OR там где колонка FACILITY используется.

EXISTS совершенно не нужен, на результат не влияет но в плане доступа отражается как join.

Что касается конкатенация то во-первых у нас таких конструкции в приложении очень много, не понятно чем они мотивированны и не совсем понятно как влияет на результат. Я уже приводил здесь такую конструкцию для обсуждения но не помню на чем тогда остановились. Во-вторых все таки используется, я чуть позже приведу планы доступа для всех вариантов.

Таблица размером более 10 млн. строк.
Это не из миграции, это OLTP запрос выполняющий сия годами. К нашему огромному удивлению программисты поменяли его на раз-два-три. Обычно они очень не охотно это делают.

iDesperado · Post by **iDesperado** » 18 Jan 2015 20:03

zgur,
с конкотенацией у вас совершенно не корректный пример. конечно без хинтов оракл будет фулскан выбирать, т.к. на тех крошечных объемах это безусловно выгодней.
вот пример, где без проблем используется индекс, без FBI:
select /*+ index( c1 test_idx )*/ * from table1 c1 where portfolio_id||status = 'GE07018B000000UPD%';

Code: Select all

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows   | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   176K |    11M| 24838   (1)| 00:04:59 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1   |    176K|    11M| 24838   (1)| 00:04:59 |
|*  2 |   INDEX FULL SCAN           | TEST_IDX |    176K|       | 10173   (1)| 00:02:03 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PORTFOLIO_ID"))||SYS_OP_UNDESCEND
              (SYS_OP_DESCEND("GSS_STATUS"))='GE07018B000000UPD%')

понятно, что на небольших объемах сканировать индекс, а потом дергать соответствующие блоки таблицы гораздо затратней, чем считать пару блоков вашей крошечно таблички одним фулсканом.
с join elimination посмотрю чуть позже.

iDesperado · Post by **iDesperado** » 18 Jan 2015 20:13

zgur wrote: В этом конкретном случае join elimination работать не будет т.к. первичный ключ состоит из двух столбцов. Вот похожий пример - если его поменять, чтобы только object_id был в ключе, работает как вы говорите.

я не вижу проблем у себя на 11.2:
select * from table1 c1 where no=1 and exists (select 1 from table1 c2 where c2.status = 'MGR' and c1.id = c2.id and c1.portfolio_id=c2.portfolio_id) ;

Code: Select all

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 89400 |  5936K|  9375   (1)| 00:01:53 |
|*  1 |  TABLE ACCESS FULL| TABLE1| 89400 |  5936K|  9375   (1)| 00:01:53 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"."PORTFOLIO_ID" IS NOT NULL AND "NO"=1 AND 
              "STATUS"='MGR')

как видите в моем случае как полагается запрос переписан.
Update: прошу прощения, забыл про unique в индексе. действительно с уникальным индексом не может переписать. действительно еще одна проблема.

zVlad · Post by **zVlad** » 18 Jan 2015 20:17

sp123 wrote:Запрос действительно простенький. Помимо execution plan не мешало бы также уточнить размеры таблиц и что примерно передается в bind variables в момент запроса - от выгребания, к примеру, 2% или половины большой таблицы рекомендации могут отличаться. А вообще да, динамический SQL - зло. Но видимо, в данном случае была нужда.

В переменных может быть что угодно, конечно. Это OLTP, и значения берутся из полей окошек.
Больше 10 млн. строк.
А что Вы имеете в виду под "динамический"? То, что переменные не определены?
В нашей терминологии это не динамический а статический запрос в том смысле что план доступа вычисляется и запрос преобразуется в исполняемую форму до выполнения и хранится в БД (не в обычной таблице, а в специальном хранилище). Что касаемо переменных то в ДБ2 есть такое понятие как категория. Есть две основных категорий - одна это когда независимо ни отчего план однозначен, вторая когда план может быть утончен если, например (но не только), известны переменные.

iDesperado · Post by **iDesperado** » 18 Jan 2015 20:30

zVlad wrote: первое что было предложено это как раз убрать OR (1=0). Одно лишь это радикально меняет план доступа.

я слышал, что db2/zOS слабоват, но не думал что все на столько фигово

zVlad · Post by **zVlad** » 18 Jan 2015 21:11

Вот планы доступа из DB2 v9.

3333 - исходный запрос
3334 - без OR with FACILITY
3335 - без всех OR
3336 - без EXISTS.

Тут некоторые колонки из двух таблиц: PLAN_TABLE и DSN_STATEMNT. Дана расшифровка METHOD.
Во второй таблице есть цост в милисекундах и в service units. Кроме того указано что этот запрос не однозначен и его неоднозначность от HOST VARIABLES. Программы с такими запросами рекомендуется bind-ить с опцией REOPT(VARS) тогда во время выполнения с известными переменными будет делаться уточнение плана доступа и выбран лучший.

Code: Select all

--  METHOD:                                                                     
--  0 - FIRST TABLE ACCESSED, CONTINUATION OF                                   
--        PREVIOUS TABLE ACCESSED OR NOT USED                                   
--  1 - NESTED LOOP JOIN                                                        
--  2 - MERGE SCAN JOIN                                                         
--  3 - SORTS REQUIRED BY ORDER BY, GROUP BY,                                   
--        SELECT DISTINCT, UNION                                                
--  4 - HYBRID JOIN                                                             
   SELECT                                                                       
     QUERYNO, METHOD, ACCESSTYPE, MATCHCOLS, ACCESSNAME                         
     FROM PLAN_TABLE WHERE QUERYNO IN (3333, 3334, 3335);                       
---------+---------+---------+---------+---------+---------+
    QUERYNO  METHOD  ACCESSTYPE  MATCHCOLS  ACCESSNAME        
---------+---------+---------+---------+---------+---------+
       3333       0  I                   2  TOIWOTS4        
       3333       0  I                   1  TOIWOTSK        
       3334       0  I                   2  TOIWOTSK       
       3334       0  I                   1  TOIWOTSK        
       3335       0  I                   1  TOIWOTS4        
       3335       1  I                   2  TOIWOTSK
       3335       3                      0
       3336       0  I                   1  TOIWOTS4    
DSNE610I NUMBER OF ROWS DISPLAYED IS 7    
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100    
---------+---------+---------+---------+---------+---------+
   SELECT QUERYNO, COST_CATEGORY,      PROCMS,      PROCSU, REASON              
   FROM DSN_STATEMNT_TABLE WHERE QUERYNO IN (3333, 3334, 3335);                 
---------+---------+---------+---------+---------+---------+
    QUERYNO  COST_CATEGORY       PROCMS       PROCSU  REASON  
---------+---------+---------+---------+---------+---------+---------+
       3333  B                      356         3912  HOST VARIABLES  
       3334  B                      274         3004  HOST VARIABLES
       3335  B                        4           43  HOST VARIABLES
       3336  B                        3           33  HOST VARIABLES
DNE610I NUMBER OF ROWS DISPLAYED IS 3    
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

zgur · Post by **zgur** » 19 Jan 2015 00:28

iDesperado wrote:zgur,
с конкотенацией у вас совершенно не корректный пример. конечно без хинтов оракл будет фулскан выбирать, т.к. на тех крошечных объемах это безусловно выгодней.
вот пример, где без проблем используется индекс, без FBI:
select /*+ index( c1 test_idx )*/ * from table1 c1 where portfolio_id||status = 'GE07018B000000UPD%';
Code: Select all
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows   | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   176K |    11M| 24838   (1)| 00:04:59 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1   |    176K|    11M| 24838   (1)| 00:04:59 |
|*  2 |   INDEX FULL SCAN           | TEST_IDX |    176K|       | 10173   (1)| 00:02:03 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PORTFOLIO_ID"))||SYS_OP_UNDESCEND
              (SYS_OP_DESCEND("GSS_STATUS"))='GE07018B000000UPD%')
понятно, что на небольших объемах сканировать индекс, а потом дергать соответствующие блоки таблицы гораздо затратней, чем считать пару блоков вашей крошечно таблички одним фулсканом.
с join elimination посмотрю чуть позже.

Посмотрите ваш пример поподробнее. Он не использует индексы в классическом смысле этого понятия - O(log(n)) доступ к записям или "index range scan, index unique scan" в плане. Ваш пример делает index full scan - по сути вертикальная партиция таблицы, без учета стуктуры индкса и возможности быстрого (O(log(n))) поиска по нему. Если рассмотреть вырожденный случай, когда в таблице никаких других полей, кроме тех, что в индексе нет, то index full scan и table full scan будут эквывалентны.

Попробуйте создать пример с index range scan и, скорее всего, вам станет ясно, что это невозможно.

iDesperado · Post by **iDesperado** » 19 Jan 2015 09:58

zgur wrote: Посмотрите ваш пример поподробнее. Он не использует индексы в классическом смысле этого понятия - O(log(n)) доступ к записям или "index range scan, index unique scan" в плане. Ваш пример делает index full scan - по сути вертикальная партиция таблицы, без учета стуктуры индкса и возможности быстрого (O(log(n))) поиска по нему. Если рассмотреть вырожденный случай, когда в таблице никаких других полей, кроме тех, что в индексе нет, то index full scan и table full scan будут эквывалентны.

да, пожалуй вы снова правы. index full scan не то, что я хотел показать.

zVlad, так может попросите DBA всетаки показать планы запросов ?

Привет

Хочу научиться читать SQL...

Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...

Re: Хочу научиться читать SQL...