Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
Добрый день.

Есть запрос, который конвертирует числоподобные строковые значения из поля таблицы и успешно выполняется сам по себе.
CREATE TABLE testnum AS SELECT '1000' id1 FROM dual UNION ALL select 'qwerty' FROM dual;

select * from  testnum ;


ID1
------
1000
qwerty

Вот сам запрос:

SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]');

ID1           ID2
------ ----------
1000         1000

Execution Plan
----------------------------------------------------------
Plan hash value: 3904613528

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTNUM |     1 |     6 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter( NOT REGEXP_LIKE ("ID1",'[a-z]'))

Но когда я делаю его в качестве подзапроса

SELECT --+ no_push_subq no_push_pred
x.* FROM (
SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
) x
WHERE x.id2 > 0 ;


то получаю ошибку ORA-01722: invalid number
Execution Plan
----------------------------------------------------------
Plan hash value: 3904613528

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTNUM |     1 |     6 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("ID1")>0 AND  NOT REGEXP_LIKE ("ID1",'[a-z]'))

План sql-запроса тот же - одно значение хэша.

Понятно, что условие верхнего уровня
x.id2 > 0
обработалось раньше,
чем подзапросное
NOT regexp_like ( id1, '[a-z]')
.
1 - filter(TO_NUMBER("ID1")>0 AND NOT REGEXP_LIKE ("ID1",'[a-z]'))

Не понятно как его заставить выполняться "в свою очередь".
Хинт no_push_subq не помог
9 июн 13, 15:00    [14411341]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
SELECT  /*+ ORDERED_PREDICATES */
        id1,
        TO_NUMBER(id1) id2
  FROM  testnum
  WHERE NOT regexp_like(id1,'[a-z]')
    AND TO_NUMBER(id1) > 0 
/

ID1           ID2
------ ----------
1000         1000

SQL>


SY.
9 июн 13, 15:50    [14411418]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Или:

SELECT  id1,
        TO_NUMBER(id1) id2
  FROM  testnum
  WHERE CASE
          WHEN NOT regexp_like(id1,'[a-z]') THEN TO_NUMBER(id1)
        END > 0 
/

ID1           ID2
------ ----------
1000         1000

SQL>


SY.
9 июн 13, 15:53    [14411424]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
SY,
Вы предложили изменить сам запрос, но этот метод не подходит, т.к. сам подзапрос изменять нельзя
(фактически это представление), и я привёл предельно упрощённый пример.
Я могу изменять только запрос верхнего уровня, т.е.
SELECT ... 
FROM (
SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
) x -- подзапрос неизменен
WHERE ...
and x.id2 > 0 ;


Мне казалось, что
no_push_subq
должен был воспрепятствовать передаче условия в подзапрос, но этого не происходит
9 июн 13, 16:19    [14411446]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Боюсь мез материализации подзапроса не обойтись:

SQL> with x as (
  2             SELECT /*+ MATERIALIZE */ id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like(id1,'[a-z]')
  3            )
  4  SELECT  *
  5    FROM  x
  6    WHERE id2 > 0
  7  /

ID1           ID2
------ ----------
1000         1000

SQL> SELECT  *
  2    FROM  (
  3           SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like(id1,'[a-z]')
  4          ) x
  5    WHERE x.id2 + rownum - rownum > 0
  6  /

ID1           ID2
------ ----------
1000         1000

SQL>


SY.
P.S. Хинт MATERIALIZE не документирован.

Сообщение было отредактировано: 9 июн 13, 16:38
9 июн 13, 16:37    [14411474]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
SY,
Спасибо за совет. Красивый приём +
9 июн 13, 16:49    [14411492]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
SY,
Спасибо за совет.
Красивый приём +rownm -rownum чтобы избежать продавливания условия в подзапрос.
9 июн 13, 16:52    [14411499]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Михайла,

можно и без изменения запроса с 11.2.0.3 с no_merge + _optimizer_filter_pushdown
10 июн 13, 02:36    [14412716]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Не люблю параметры и хинты начинающиеся c "_".

SY.
10 июн 13, 04:01    [14412756]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54376
SY
SQL> SELECT *
2 FROM (
3 SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like(id1,'[a-z]')
4 ) x
5 WHERE x.id2 + rownum - rownum > 0
6 /
какое-то некоторое противоречие есть в использовании rownum во WHERE.
Ведь псевдостолбец rownum вроде бы появляется для уже отоборанной строки.
10 июн 13, 09:10    [14412996]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Михайла,

можно с проталкиванием не бороться и хинтов не пользовать, а использовать выражение для фиксирования порядка предикатов
SQL> SELECT
  2  x.* FROM (
  3  SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
  4  ) x
  5  WHERE x.id2 > 0;
ERROR:
ORA-01722: неверное число 

строки не выбраны

SQL> 
SQL> SELECT
  2  x.* FROM (
  3  SELECT id1, To_Number(id1) id2 FROM testnum -- WHERE NOT regexp_like ( id1, '[a-z]')
  4  ) x
  5  WHERE case when not regexp_like( id1, '[a-z]' ) then x.id2 end > 0;

ID1           ID2                                                               
------ ----------                                                               
1000         1000                                                               

баян, между прочим
10 июн 13, 12:07    [14414035]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54376
честно говоря, сама ситуация в корне неверна - из строки каждый раз через какую-либо функцию данные получать.
Если они так нужны - садите их триггером в отдельную колонку.
10 июн 13, 12:26    [14414170]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

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

Подзапрос менять нельзя, ну пусть это будет не явный подзапрос, а вью

CREATE OR REPLACE view y
AS
SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
;


Сам запрос тогда будет выглядеть так :
SELECT y.* 
FROM y
WHERE y.id2 > 0 ;


Результат такой же.

Вопрос: Как составить мой запрос "SELECT y.* FROM y WHERE ... AND y.id2 > 0...", чтобы избежать ошибки ?
10 июн 13, 14:06    [14414972]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Михайла
orawish,

Подзапрос менять нельзя, ну пусть это будет не явный подзапрос, а вью
..

Результат такой же.

Вопрос: Как составить мой запрос "SELECT y.* FROM y WHERE ... AND y.id2 > 0...", чтобы избежать ошибки ?

очевидно же
SELECT y.* 
FROM y
WHERE case when regexp(ляля) then ляляля end > 0 ;
10 июн 13, 14:20    [14415099]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

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

Если проверочное условие на "числоподобность" держать во внешнем запросе,
то и смысла в преобразовании внутри подзапроса/вьюшки нет.
Поэтому усложняю задачу :)

CREATE OR REPLACE view Y
AS
SELECT  To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
/
10 июн 13, 14:47    [14415314]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
andreymx
SY
SQL> SELECT *
2 FROM (
3 SELECT id1, To_Number(id1) id2 FROM testnum WHERE NOT regexp_like(id1,'[a-z]')
4 ) x
5 WHERE x.id2 + rownum - rownum > 0
6 /
какое-то некоторое противоречие есть в использовании rownum во WHERE.
Ведь псевдостолбец rownum вроде бы появляется для уже отобранной строки.


Поэтому оптимизатор не продавливает условие с rownum в подзапрос
10 июн 13, 15:02    [14415462]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Михайла
orawish,

Если проверочное условие на "числоподобность" держать во внешнем запросе,
то и смысла в преобразовании внутри подзапроса/вьюшки нет.
Поэтому усложняю задачу :)

CREATE OR REPLACE view Y
AS
SELECT  To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
/
тож мне усложнитель все решения продолжают оставаться применимыми и к этому "усложнению". Тема уже многократно обсуждалась и, если бы поискал, то не стал бы создавать еще один лишний топик.
10 июн 13, 15:08    [14415520]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Михайла
orawish,

Если проверочное условие на "числоподобность" держать во внешнем запросе,
то и смысла в преобразовании внутри подзапроса/вьюшки нет.
Поэтому усложняю задачу :)

CREATE OR REPLACE view Y
AS
SELECT  To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
/

при вашем усложнении, запрос остается без изменений.
и смысл есть. и он в том, чтобы трансформация запроса оптимизатором не вызывала ошибки.
10 июн 13, 15:09    [14415534]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Михайла
Мне казалось, что
no_push_subq

должен был воспрепятствовать передаче условия в подзапрос, но этого не происходит
И не пихайте какие попало хинты куда ни попадя:
NO_PUSH_SUBQ
NO_PUSH_PRED
10 июн 13, 15:15    [14415598]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
orawish
Михайла
orawish,

Если проверочное условие на "числоподобность" держать во внешнем запросе,
то и смысла в преобразовании внутри подзапроса/вьюшки нет.
Поэтому усложняю задачу :)

CREATE OR REPLACE view Y
AS
SELECT  To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
/

при вашем усложнении, запрос остается без изменений.
и смысл есть. и он в том, чтобы трансформация запроса оптимизатором не вызывала ошибки.


Даже если смысл и есть, то возможности уже нет.
10 июн 13, 16:28    [14416297]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Михайла
orawish
пропущено...

при вашем усложнении, запрос остается без изменений.
и смысл есть. и он в том, чтобы трансформация запроса оптимизатором не вызывала ошибки.


Даже если смысл и есть, то возможности уже нет.

ну, если и вьюху поправить нет возможности и запрос к вьюхе поправить нет возможности - тогда беда.
а, собственно, что мы обсуждали то?
10 июн 13, 17:17    [14416657]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
orawish
ну, если и вьюху поправить нет возможности и запрос к вьюхе поправить нет возможности - тогда беда.
а, собственно, что мы обсуждали то?

Запрос к вьюхе можно править, но нет доступа из запроса к исходному полю id1.
10 июн 13, 18:42    [14417178]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
Михайла
Member

Откуда:
Сообщений: 27
xtender
Михайла
orawish,

Если проверочное условие на "числоподобность" держать во внешнем запросе,
то и смысла в преобразовании внутри подзапроса/вьюшки нет.
Поэтому усложняю задачу :)

CREATE OR REPLACE view Y
AS
SELECT  To_Number(id1) id2 FROM testnum WHERE NOT regexp_like ( id1, '[a-z]')
/
тож мне усложнитель все решения продолжают оставаться применимыми и к этому "усложнению". Тема уже многократно обсуждалась и, если бы поискал, то не стал бы создавать еще один лишний топик.


Нет, не все решения продолжают оставаться применимыми к этому "усложнению".
+- rownum  /*+ MATERIALIZE */
сработают, но доступа к полю id1 уже нет.
Впрочем, я вполне удовлетворён поступившими советами.
10 июн 13, 18:58    [14417238]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Михайла
orawish
ну, если и вьюху поправить нет возможности и запрос к вьюхе поправить нет возможности - тогда беда.
а, собственно, что мы обсуждали то?

Запрос к вьюхе можно править, но нет доступа из запроса к исходному полю id1.

смешно.
ну, решать вам. для (дополнительной) аргументации за необходимость переписать вью можете использовать
тот факт, что использование регулярного выражения здесь ни разу не оправдано, в смысле производительности.
10 июн 13, 19:05    [14417271]     Ответить | Цитировать Сообщить модератору
 Re: Условие из внешнего запроса уходит в подзапрос и вызывает ошибку  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Михайла
Нет, не все решения продолжают оставаться применимыми к этому "усложнению".
+- rownum  /*+ MATERIALIZE */

сработают, но доступа к полю id1 уже нет.
какие не применимы? и что имелось ввиду под "доступа к полю уже нет" и зачем? to_number(id1) снаружи есть и этого достаточно.
10 июн 13, 19:25    [14417342]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить