порядок выполнения оракула и вычисления функций

Допустим, у нас есть таблица с 6 миллионами записей.

CREATE TABLE MYTABLE
(
  KEY1 NUMBER(15),
  ANALYSENO    NUMBER(15),
  ADDRESSNO    NUMBER(15),
  ABC          NUMBER(3),
  ABCDETAIL    CLOB
)

И у меня есть два запроса.

первый:

 Select m.*,
        CASE WHEN dbms_lob.INSTR(m.ABCDETAIL,'ABC$$') = 0 THEN '' ELSE
        dbms_lob.SUBSTR(m.ABCDETAIL,dbms_lob.INSTR(m.ABCDETAIL,'##AAA')-dbms_lob.INSTR(m.ABCDETAIL,'ABC$$')-10,dbms_lob.INSTR(m.ABCDETAIL,'ABC')+10) END) as SOMECALC 
 from MYTABLE m 
 where m.ABC = 1 
       and ROWNUM < 1000
       and CASE WHEN dbms_lob.INSTR(m.ABCDETAIL,'ABC$$') = 0 THEN '' ELSE
        dbms_lob.SUBSTR(m.ABCDETAIL,dbms_lob.INSTR(m.ABCDETAIL,'##AAA')-dbms_lob.INSTR(m.ABCDETAIL,'ABC$$')-10,dbms_lob.INSTR(m.ABCDETAIL,'ABC')+10) END like '%AAA%';

второй:

Select a.* from
    (Select m.*,
        CASE WHEN dbms_lob.INSTR(m.ABCDETAIL,'ABC$$') = 0 THEN '' ELSE
        dbms_lob.SUBSTR(m.ABCDETAIL,dbms_lob.INSTR(m.ABCDETAIL,'##AAA')-dbms_lob.INSTR(m.ABCDETAIL,'ABC$$')-10,dbms_lob.INSTR(m.ABCDETAIL,'ABC')+10) END as SOMECALC 
     from MYTABLE m 
     where m.ABC = 1 
           and ROWNUM < 1000) a 
     where a.SOMECALC like '%AAA%';

Единственное отличие состоит в том, что во втором запросе функции расчета не используются в операторе where. И будьте осторожны, я использую оператор ROWNUM ‹ 1000 для запроса where.

Итак, вопросы следующие

1- есть ли разница между двумя запросами?

2- можем ли мы сказать, что оператор ROWNUM ‹ 1000 вычисляется последним при выполнении всех операторов?

3- использование некоторых функций (длина, случай, когда, instr и т. д.) лениво оценивается?


sql oracle rownum
person brtb    schedule 08.10.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Да, есть разница между двумя запросами.

В первом запросе вы предлагаете выбрать первые 999 строк, которые соответствуют всем остальным условиям фильтрации.

Во втором запросе вы предлагаете выбрать первые 999 строк, в которых m.abc = 1, а затем отфильтровать те строки, где поле somecalc похоже на «%AAA%».

Вы вполне можете получить меньше строк, отображаемых вторым запросом. Если вы переместите предикат and rownum < 1000 во внешний запрос второго запроса, у вас будет запрос, эквивалентный вашему первому запросу.

person Boneist    schedule 08.10.2015

arrow_upward
0
arrow_downward

Во-первых, у вас есть синтаксическая ошибка в обоих запросах. У вас есть лишняя закрывающая скобка в выражении CASE.

Во всяком случае, переходя к вашему первоначальному вопросу:

1- есть ли разница между двумя запросами?

да.

можем ли мы сказать, что оператор ROWNUM ‹ 1000 вычисляется последним при выполнении всех операторов?

No.

Разница заключается в предикате фильтра. Чтобы понять это, сравните объяснить планы:

Запрос 1

SQL> set autot on explain
SQL> SELECT m.*,
  2    CASE
  3      WHEN dbms_lob.INSTR(m.ABCDETAIL,'ABC$$') = 0
  4      THEN ''
  5      ELSE dbms_lob.SUBSTR(m.ABCDETAIL,dbms_lob.INSTR(m.ABCDETAIL,'##AAA')-
  6      dbms_lob.INSTR(m.ABCDETAIL,'ABC$$')-10,dbms_lob.INSTR(m.ABCDETAIL,'ABC')+10)
  7    END AS SOMECALC
  8  FROM MYTABLE m
  9  WHERE m.ABC = 1
 10  AND ROWNUM  < 1000
 11  AND
 12    CASE
 13      WHEN dbms_lob.INSTR(m.ABCDETAIL,'ABC$$') = 0
 14      THEN ''
 15      ELSE dbms_lob.SUBSTR(m.ABCDETAIL,dbms_lob.INSTR(m.ABCDETAIL,'##AAA')-
 16      dbms_lob.INSTR(m.ABCDETAIL,'ABC$$')-10,dbms_lob.INSTR(m.ABCDETAIL,'ABC')+10)
 17    END LIKE '%AAA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1231656364

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |  2054 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| MYTABLE |     1 |  2054 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<1000)
   2 - filter("M"."ABC"=1 AND CASE "DBMS_LOB"."INSTR"("M"."ABCDETAIL",'A
              BC$$') WHEN 0 THEN '' ELSE "DBMS_LOB"."SUBSTR"("M"."ABCDETAIL","DBMS_LOB
              "."INSTR"("M"."ABCDETAIL",'##AAA')-"DBMS_LOB"."INSTR"("M"."ABCDETAIL",'A
              BC$$')-10,"DBMS_LOB"."INSTR"("M"."ABCDETAIL",'ABC')+10) END  LIKE
              '%AAA%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Запрос 2

SQL> SELECT a.*
  2  FROM
  3    (SELECT m.*,
  4      CASE
  5        WHEN dbms_lob.INSTR(m.ABCDETAIL,'ABC$$') = 0
  6        THEN ''
  7        ELSE dbms_lob.SUBSTR(m.ABCDETAIL,dbms_lob.INSTR(m.ABCDETAIL,'##AAA')-
  8        dbms_lob.INSTR(m.ABCDETAIL,'ABC$$')-10,
  9        dbms_lob.INSTR(m.ABCDETAIL,'ABC')+10)
 10      END AS SOMECALC
 11    FROM MYTABLE m
 12    WHERE m.ABC = 1
 13    AND ROWNUM  < 1000
 14    ) a
 15  WHERE a.SOMECALC LIKE '%AAA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 76631248

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |  4056 |     2   (0)| 00:00:01 |
|*  1 |  VIEW               |         |     1 |  4056 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |         |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| MYTABLE |     1 |  2054 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("A"."SOMECALC" LIKE '%AAA%' AND "A"."SOMECALC" IS NOT
              NULL)
   2 - filter(ROWNUM<1000)
   3 - filter("M"."ABC"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Вы видите COUNT STOPKEY в плане объяснения, потому что ROWNUM используется для фильтрации строк. Таким образом, в первом плане объяснения STOPKEY применяется первым, во втором запросе он применяется после filter("A"."SOMECALC" LIKE '%AAA%' AND "A"."SOMECALC" IS NOT NULL)..

person Lalit Kumar B    schedule 08.10.2015