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

Откуда:
Сообщений: 2
Возможно ли написать более оптимально запрос :

SELECT
DOCUMENTNO,
CASE
WHEN T1.DAID = 111111 THEN 'DB'
WHEN T1.CAID = 111111 THEN 'CR'
END DBCRD,
SITEID,
CASE
WHEN T1.DAID = 111111 THEN T1.AANO
WHEN T1.CAID = 111111 AND T1.ID = T2.ID_BI2 THEN T2.KACC
WHEN T1.CAID = 111111 THEN T1.ABNO
END SAMEACC,
CASE
WHEN T1.DAID = 111111 THEN T1.BAID
WHEN T1.CAID = 111111 AND T1.ID = T2.ID_BI2 THEN T2.KMFO
WHEN T1.CAID = 111111 THEN T1.BBID
END SAMEMFO,
CASE
WHEN T1.DAID = 111111 THEN T1.CABNAME
WHEN T1.CAID = 111111 AND TABLE1.ID = T2.ID_BI2 THEN T2.NAME_PAYEE
WHEN T1.CAID = 111111 THEN T1.CABNAME
END SAMENAME,
(SELECT
t.SNAME
FROM
SOMECODE t WHERE T.ID = CASE
WHEN DAID = 111111 THEN AID
WHEN CAID = 111111 THEN BID
END ) VALUEB,
CID,
SUMMAEQ,
PLATPURPOSE
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2 ON T1.ID = T2.ID_BI2 WHERE
T1.DAID = 111111 OR T1.CAID = 111111 AND
T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30'
6 фев 19, 12:39    [21802607]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
-2-
Member

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

full -> left
6 фев 19, 12:47    [21802615]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Щукина Анна
Member

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

критерий оптимальности?
6 фев 19, 12:47    [21802616]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29302
w31
более оптимально запрос
Если поубирать переводы строк и незначащие пробелы, то длина запроса оптимизируется даже не смотря на то, что нужно два раза добавить date.
6 фев 19, 12:49    [21802619]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
SELECT
	DOCUMENTNO,
	'DB' as DBCRD,
	SITEID,
	T1.AANO as SAMEACC,
  T1.BAID as SAMEMFO,
  T1.CABNAME as SAMENAME,
	t.SNAME as VALUEB,
	CID,
	SUMMAEQ,
	PLATPURPOSE
	FROM TABLE1 T1
  LEFT JOIN SOMECODE t on T.ID = AID
	LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID_BI2 
  WHERE	T1.DAID = 111111 
  AND	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30' 
union all
SELECT
	DOCUMENTNO,
  'CR' as DBCRD,
	SITEID,
	nvl(T2.KACC,T1.ABNO) as SAMEACC,
  nvl(T2.KMFO,T1.BBID) as SAMEMFO,
  nvl(T2.NAME_PAYEE,T1.CABNAME) as SAMENAME,
	t.SNAME as VALUEB,
	CID,
	SUMMAEQ,
	PLATPURPOSE
	FROM TABLE1 T1
  LEFT JOIN SOMECODE t on T.ID = BID
	LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID_BI2 
  WHERE	T1.DAID != 111111 and T1.CAID = 111111 
  AND	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30'   
6 фев 19, 13:50    [21802660]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
  WHERE	T1.DAID = 111111 
  AND	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30' 
union all
...
  WHERE	T1.DAID != 111111 and T1.CAID = 111111 
  AND	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30'   

Неверно.
6 фев 19, 14:09    [21802682]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
andrey_anonymous
Dshedoo
  WHERE	T1.DAID = 111111 
  AND	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30' 
union all
...
  WHERE	T1.DAID != 111111 and T1.CAID = 111111 
  AND	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30'   

Неверно.


Если я правильно понял, то в оригинале очепятка в виде отсутствующих скобок:

T1.DAID = 111111 OR T1.CAID = 111111 AND
	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30' 


(T1.DAID = 111111 OR T1.CAID = 111111) AND
	T1.SOMEDATE BETWEEN '2018-11-01' AND '2018-11-30' 
6 фев 19, 14:17    [21802689]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
в оригинале очепятка

В оригинале - тестовое задание, которое позволяет интервьюеру оценить компетенцию кандидата.
Если бы интервьюером был я, то Ваш ответ для начала сказал бы мне, что Вы не сумели написать эквивалентный запрос :)
6 фев 19, 14:21    [21802698]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
-2-
full -> left

Как минимум, зависит от
      ,CID
      ,SUMMAEQ
      ,PLATPURPOSE
6 фев 19, 14:22    [21802699]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
andrey_anonymous
Dshedoo
в оригинале очепятка

В оригинале - тестовое задание, которое позволяет интервьюеру оценить компетенцию кандидата.
Если бы интервьюером был я, то Ваш ответ для начала сказал бы мне, что Вы не сумели написать эквивалентный запрос :)


С таким подходом можно и по поводу алиасов придираться, например:
SOMECODE t WHERE T.ID = CASE
	WHEN DAID = 111111 THEN AID


Вдруг тут имелся ввиду не T1.DAID, а T.DIAD или T2.DAID.
Нигде же не сказано, что запрос возвращает данные, а не ORA-01427
6 фев 19, 14:36    [21802710]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
andrey_anonymous
-2-
full -> left

Как минимум, зависит от
      ,CID
      ,SUMMAEQ
      ,PLATPURPOSE

а в чем зависимость?

ps
если T1.SOMEDATE тип date, я б дописал date '2018-11-01' AND date '2018-11-30'

....
stax
6 фев 19, 14:43    [21802725]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Stax
а в чем зависимость?

А они в какой таблице?
-2- предложил уйти от full outer, который мог быть написан не "шоббуло".
6 фев 19, 14:45    [21802726]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
andrey_anonymous
Stax
а в чем зависимость?

А они в какой таблице?
-2- предложил уйти от full outer, который мог быть написан не "шоббуло".

WHERE
	T1.[...] OR T1.[...] AND
	T1.[...]
6 фев 19, 14:54    [21802738]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
WHERE
	T1.[...] OR T1.[...] AND
	T1.[...]

Вы правы.
6 фев 19, 14:56    [21802740]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
andrey_anonymous
Stax
а в чем зависимость?

А они в какой таблице?
-2- предложил уйти от full outer, который мог быть написан не "шоббуло".


я в смысле как влияет на замену full outer?

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

.....
stax
6 фев 19, 14:58    [21802743]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Stax
я в смысле как влияет на замену full outer?

С учетом where - уже никак, я "перебдел" :)
6 фев 19, 15:07    [21802752]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
	nvl(T2.KACC,T1.ABNO) as SAMEACC,
  nvl(T2.KMFO,T1.BBID) as SAMEMFO,
  nvl(T2.NAME_PAYEE,T1.CABNAME) as SAMENAME,


	nvl2(T2.ID_BI2,T2.KACC,T1.ABNO) as SAMEACC,
  nvl2(T2.ID_BI2,T2.KMFO,T1.BBID) as SAMEMFO,
  nvl2(T2.ID_BI2,T2.NAME_PAYEE,T1.CABNAME) as SAMENAME,
6 фев 19, 15:11    [21802761]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
-2-
Member

Откуда:
Сообщений: 14922
andrey_anonymous
Stax
я в смысле как влияет на замену full outer?

С учетом where - уже никак, я "перебдел" :)
Правда замена влияет только на визуальную оптимальность.
Оптимизатор и сам разберется кто outer, а кто нет.
6 фев 19, 15:23    [21802777]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
  nvl(T2.NAME_PAYEE,T1.CABNAME) as SAMENAME,


  nvl2(T2.ID_BI2,T2.NAME_PAYEE,T1.CABNAME) as SAMENAME,

Опять неверно :)
6 фев 19, 15:28    [21802786]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
Dshedoo
	nvl(T2.KACC,T1.ABNO) as SAMEACC,


CASE
WHEN T1.DAID = 111111 THEN T1.AANO
WHEN T1.CAID = 111111 AND T1.ID = T2.ID_BI2 THEN T2.KACC
WHEN T1.CAID = 111111 THEN T1.ABNO
END SAMEACC,


и тд
....
stax
6 фев 19, 15:32    [21802795]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Stax
и тд

Да.
+
with table1(ID, DAID,CAID,BAID,BBID) as(
                    select 1, 111111, 222222, 'T1.BAID', 'T1.BBID'
from dual union all select 2, 111111, 222222, 'T1.BAID', 'T1.BBID'
from dual union all select 3, 222222, 111111, 'T1.BAID', 'T1.BBID'
from dual union all select 4, 222222, 111111, 'T1.BAID', 'T1.BBID'
from dual )
, table2(ID_BI2, KMFO) as ( select 1, 'T2.KMFO'
from dual union all select 3, 'T2.KMFO'
from dual )
----------------------------
select id, daid,caid, ID_BI2
     , CASE WHEN T1.DAID = 111111 THEN T1.BAID
            WHEN T1.CAID = 111111 AND T1.ID = T2.ID_BI2 THEN T2.KMFO
            WHEN T1.CAID = 111111 THEN T1.BBID
       END SAMEMFO_ORIG
     , CASE 111111
            WHEN T1.DAID THEN T1.BAID
            WHEN T1.CAID THEN coalesce(T2.KMFO, T1.BBID)
        END SAMEMFO_ANE
     , nvl(T2.KMFO,T1.BBID) as SAMEMFO_DSHEDOO
     , nvl2(T2.ID_BI2,T2.KMFO,T1.BBID) as SAMEMFO_DSHEDOO2
  FROM TABLE1 T1
  LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID_BI2
 WHERE T1.DAID = 111111
    OR T1.CAID = 111111
;
        ID       DAID       CAID     ID_BI2 SAMEMFO_ORIG SAMEMFO_ANE SAMEMFO_DSHEDOO SAMEMFO_DSHEDOO2
---------- ---------- ---------- ---------- ------------ ----------- --------------- ----------------
         1     111111     222222          1 T1.BAID      T1.BAID     T2.KMFO         T2.KMFO
         3     222222     111111          3 T2.KMFO      T2.KMFO     T2.KMFO         T2.KMFO
         4     222222     111111            T1.BBID      T1.BBID     T1.BBID         T1.BBID
         2     111111     222222            T1.BAID      T1.BAID     T1.BBID         T1.BBID

SQL> 
6 фев 19, 15:35    [21802800]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
w31,

WHEN T1.CAID = 111111 THEN

заменить на ELSE

.....
stax
6 фев 19, 15:37    [21802805]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
andrey_anonymous
Stax
и тд

Да.

... мой вариант корректировки case представлен для T2.KMFO not null, не надо инсинуаций :)
6 фев 19, 15:38    [21802807]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
Stax
Dshedoo
	nvl(T2.KACC,T1.ABNO) as SAMEACC,


CASE
WHEN T1.DAID = 111111 THEN T1.AANO
WHEN T1.CAID = 111111 AND T1.ID = T2.ID_BI2 THEN T2.KACC
WHEN T1.CAID = 111111 THEN T1.ABNO
END SAMEACC,


и тд
....
stax


SELECT
	[...],
	T1.AANO as SAMEACC,
        [...]
	FROM [...]
  WHERE	T1.DAID = 111111 
      AND [...]
union all
SELECT
       [...],
       nvl2(T2.ID_BI2,T2.KACC,T1.ABNO) as SAMEACC,
       [...],
       FROM [...]
  WHERE	T1.DAID != 111111 and T1.CAID = 111111
      AND [...]


NVL я заменил на NVL2, т.к. если T2.KACC is null, чтобы вернулся null.
6 фев 19, 15:38    [21802808]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
  WHERE	T1.DAID = 111111 
      AND [...]
union all
  WHERE	T1.DAID != 111111 and T1.CAID = 111111
      AND [...]


Тогда приводите решение целиком, фрагменты по умолчанию относятся к оригиналу.
6 фев 19, 15:41    [21802812]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
andrey_anonymous,
WHEN T1.CAID THEN coalesce(T2.KMFO, T1.BBID)

T2.KMFO not null?

ELSE не подойдет?

....
stax
6 фев 19, 15:42    [21802814]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Stax
ELSE не подойдет?

Я случайно опубликовал один из размышлизмов.
6 фев 19, 15:43    [21802816]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
Dshedoo,

лично я б OR на UNION ALL не менял (пусть ето делает оптимизатор)

хотя если схема хорошо известна,
то можно и заменить с пониманием что так будет оптимальнее
но сопровождать будет явно посложнее

.....
stax
6 фев 19, 15:51    [21802824]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
Stax
Dshedoo,

лично я б OR на UNION ALL не менял (пусть ето делает оптимизатор)

хотя если схема хорошо известна,
то можно и заменить с пониманием что так будет оптимальнее
но сопровождать будет явно посложнее

.....
stax


Это только для оптимизации кейсов.
Как мне кажется, если этот запрос и можно хорошо оптимизировать, то только через UNION.
6 фев 19, 16:04    [21802833]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
Как мне кажется, если этот запрос и можно хорошо оптимизировать, то только через UNION.

1. Оптимизация невозможна без конкретной цели и конкретного окружения.
2. Если, к примеру, цель - сокращение dbtime и нагрузки на IO указанного запроса, в table1 триллион записей и предикат "CAID = 111111" низкоселективен, то идея организации двойного прохода по table1 покажется слегка неумной.
3. Ваш вариант union all не эквиваленте исходному запросу.
4. Когда сделаете эквивалентным - рассмотрите случай CAID=DAID=111111

Можно перевести case expression из searched в simple - это несколько сократит издержки и упростит текст почти безотносительно к прочим факторам при условии, что соответствующие атрибуты TABLE2 not null - иначе simple форма становится неудобочитаемой.

Остальное - только "по месту".
К примеру, на решение повлияет возможность ситуации CAID=DAID=111111, соотношение мощностей DAID=111111 и (CAID=111111 and DAID<>111111), наличие индексов, доступный объем памяти и т.д.
6 фев 19, 16:21    [21802846]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
andrey_anonymous
3. Ваш вариант union all не эквиваленте исходному запросу.
4. Когда сделаете эквивалентным - рассмотрите случай CAID=DAID=111111

3. Можете, конечно, убрать из первого запроса условие по дате, сути это не изменит.
4. Он рассмотрен.

Остальное из разряда "трава зелёная, а небо голубое".
6 фев 19, 16:32    [21802864]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1983
Dshedoo
Это только для оптимизации кейсов.
Как мне кажется, если этот запрос и можно хорошо оптимизировать, то только через UNION.

может ето и имели ввиду хозяева, хз

на собеседовании обычно волнуешся, и я б сразу не додул до union all

зы
я при ручной замене OR на UNION ALL іспользовал LNNVL

зии
интересно что предложил w31 и пошел ли он тест
....
stax
6 фев 19, 16:39    [21802873]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17682
Dshedoo
4. Он рассмотрен.

Остальное из разряда "трава зелёная, а небо голубое".

4.1 DAID = null, CAID = 111111

"остальное" - печальная правда жизни.
Нельзя оптимизировать сферического коня по неопределенному критерию, любое конкретное предложение будет встречено контраргументом.
6 фев 19, 16:50    [21802890]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
Dshedoo
Member

Откуда:
Сообщений: 373
andrey_anonymous
Dshedoo
4. Он рассмотрен.

Остальное из разряда "трава зелёная, а небо голубое".

4.1 DAID = null, CAID = 111111

"остальное" - печальная правда жизни.
Нельзя оптимизировать сферического коня по неопределенному критерию, любое конкретное предложение будет встречено контраргументом.


4.1. Вы правы.
Тогда, как Stax написал выше, необходим LNNVL.
6 фев 19, 16:58    [21802900]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на интервью  [new]
-2-
Member

Откуда:
Сообщений: 14922
Stax
и пошел ли он тест
для теста запрос перегружен лишними символами и не имеет осмысленного решения.
Неоднозначность хороша только для дискуссии, а дискуссия предполагает листок и собеседника. Вряд ли автор набирал это с листка.
6 фев 19, 17:17    [21802917]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить