Выборка из одной таблицы в SQL

[RXL]

Запрос используется в интерактивном приложении - выполняется не часто (порядка раз в минуту для каждого пользователя), но должен выполняться быстро. Время выполнения 200-300 мс, возвращает порядка 50-100 строк. За одно действие в приложении происходит 3-7 вызовов этого запроса.
Над оптимизацией работал много, но с каждой дополнительной фишкой база разрастается, связи растут и запрос работает все медленнее и приходится изыскивать способы его ускорить. Статистику вычисляю каждый месяц.
Выборка чаще идет по PK из одного столбца, реже - по составному ключу. Все LEFT JOIN - дополнительные фичи. Т.е. надо поддерживать как их отсутствие (или старый вариант фичи), так и наличие.

Размеры:
db2.ORDERS_HISTORY - 1770 тыс.
db2.ORDERS - 483 тыс.
db2.ORDER_FLOW_CTRL_HISTORY - 47 тыс.
db2.HISTORY - 637 тыс.
db1.JOBCARDS - 220 тыс.
db1.NEWORDHD - 126 тыс.
db2.ADDRESS - VIEW по шести таблицам (55000, 60, 5000, 20, 500, 20)
db1.SUBSCRIB - 129 тыс.
db2.GET_ORDER_FLOW_CTRL_LAST_ID_() - по одной таблице (47000)

Две базу принадлежат разным приложениям: db2 менять нельзя, db1 - под моим контролем, но изначально проектировалась не очень хорошо и отказаться от многих вещей нет возможности. Дамп (expdp) обеих баз весит 3 ГБ, а в развернутом виде - около 20 ГБ. Это еще ничего - раньше это было два отдельных инстанса.



Добавлено через 11 минут и 22 секунды:
Из запроса я еще исключил несколько справочных таблиц. Вместо этого загружаю их в std::map и выбираю средствами приложения.
Самих таблиц привести не могу. Если интересно, могу план в личку кинуть.

[McZim]

RXL, понятно. Да скинь планчик.

Добавлено через 1 час, 3 минуты и 27 секунд:
RXL, да и кстате, я покрутил твой запрос. Данных в таблицах нет, но если некоторые соединения перенести в подзапросы, план выполнения начинает выглядеть получше. Конечно нужно знать какие таблицы выносить, а возможно с теми данными что у тебя их вобще не нужно выносить.

Я хотел спрсить, ты выносил их в подзапросы? Не ВСЕ а некоторые. И да кстате, посмотри вот это может тебе тоже чем то поможет?

scott@ORA9IR2> explain plan for
  2  select ename, dname, a.deptno
  3  from dept a
  4  left outer join emp b
  5               on a.deptno = b.deptno
  6              and a.deptno in (10, 40)
  7  /
 
Explained.
 
scott@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    82 |  2378 |   166 |
|   1 |  NESTED LOOPS OUTER  |             |    82 |  2378 |   166 |
|   2 |   TABLE ACCESS FULL  | DEPT        |    82 |  1804 |     2 |
|   3 |   VIEW               |             |     1 |     7 |     2 |
|*  4 |    TABLE ACCESS FULL | EMP         |     1 |    20 |     2 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("A"."DEPTNO"="B"."DEPTNO" AND ("A"."DEPTNO"=10 OR "
              A"."DEPTNO"=40) AND ("B"."DEPTNO"=10 OR "B"."DEPTNO
              "=40))
 
Note: cpu costing is off
 
19 rows selected.

Now, thats interesting -- the predicate  it applied to a view of EMP, it is not really applied to
DEPT at all -- this is the semantics of the ON clause in an OUTER JOIN, compare to an ON + WHERE:

scott@ORA9IR2> delete from plan_table;
 
6 rows deleted.
 
scott@ORA9IR2> explain plan for
  2  select ename, dname, a.deptno
  3  from dept a
  4  left outer join emp b on a.deptno = b.deptno
  5   where a.deptno in (10, 40)
  6  /
 
Explained.
 
scott@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------
 
-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    42 |     4 |
|*  1 |  HASH JOIN OUTER              |             |     1 |    42 |     4 |
|   2 |   INLIST ITERATOR             |             |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    22 |     1 |
|*  4 |     INDEX RANGE SCAN          | PK_DEPT     |     1 |       |     2 |
|   5 |   TABLE ACCESS FULL           | EMP         |    82 |  1640 |     2 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DEPTNO"="B"."DEPTNO"(+))
   4 - access("A"."DEPTNO"=10 OR "A"."DEPTNO"=40)
 
Note: cpu costing is off
 
19 rows selected.
 
scott@ORA9IR2>

Here we see the predicate is applied to the table DEPT, not EMP.

[Dana]

2.   Вывести список всех клиентов из Челябинска, вносивших больше 500 рублей за оплату единовременно после 25 июня 2008 года.

select C_FIRST_NAME, C_SECOND_NAME, C_LAST_NAME, d_pay, n_sum
FROM
client,payment
WHERE
client.n_client = payment.n_client (+)
and n_SUM>500, d_pay>('25.06.08', 'DD.MM.YY')

d_pay>('25.06.08', 'DD.MM.YY') - что-то не срабатывает

[Sla]

разбери эту строку
and n_SUM>500, d_pay>('25.06.08', 'DD.MM.YY')

и скажи ЧТО ты хочешь получить?

[McZim]

Dana, для начала исправь синтаксические ошибки.

[Dana]

разбери эту строку
and n_SUM>500, d_pay>('25.06.08', 'DD.MM.YY')

и скажи ЧТО ты хочешь получить?

сумма оплаты больше 500, дата оплаты больше 25 июня 2008 года

[Sla]

Dana, ты уверена? То что ты сказала - это ты повторила свой вопрос, но не рассказала как ты его реализовала

вот давай словами расскажи нам эту строку
что с чем сравнивается, зачем там нужна запятая, и нужна ли она?

Как должно выглядеть WHERE с критериями условий?

[Dimka]

Ты хочешь сказать что если я работаю с ораклом и использую (+), если мне этого хватает для покрытия бизнес требований, то я плохой инженер и пишу "говонокод"?

Нет, не так. Плохой инженер и плохой программист - разные вещи. Можно быть отличным программистом и писать хороший код, оставаясь при этом плохим инженером (игнорировать нетехнические вещи, сопутствующие разработке ПО). Инженер - это более широкое понятие, чем программист. Использование того или иного технического средства (пусть даже такой мелочи, как "(+)") чем-то обусловлено. Если оно обусловлено лишь желанием пятки левой ноги, это вызывает у меня вопросы.

Ты знаешь, есть программисты у которых все по стандарту, да только все работает в зависимости от погоды на марсе.

Это плохие программисты, а их следование стандартам может быть не осознанием потребности, а внешним к ним требованием (начальник пинает) - тогда и стандарту они следуют так себе. Стандарт не исправляет отсутствие навыков и низкую квалификацию.

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

Это мнение проистекает от неумения руководства считать деньги. Как раз в самописных продуктах структура затрат на производство такова, львиная её доля приходится на этап сопровождения, а не начальной разработки. Следовательно максимальную экономию можно получить, грамотно организовав работы по сопровождению, максимально снизив издержки на внесение изменений, опытную эксплуатацию, процедуры развёртывания (возможно, вызывающие простой основного производства/работ фирмы), процедуры перехода на новые версии (возможно, вызывающие снижение производительности труда пользователей, вынужденных проходить дополнительное обучение и переучиваться выполнению привычных им операций новым способом - в зависимости от степени изменений в новой версии). Для управляемости всеми этими издержками гораздо выгоднее следовать стандартным процедурам и методикам производства ПО, а также максимально использовать готовые продукты, нежели этого не делать. Чтобы это понять, достаточно честно учитывать ВСЕ затраты предприятия, а не только стоимость серверов и зарплат программистов. И планировать IT-проекты, исходя из этой полной информации. Неплохо также отдельно считать рентабельность каждого проекта.

Скажу так, мы купили биллинг и схему БД я переделываю под наши бизнес требования, производительность растет в разы!

Не знаю, на каких условиях покупалась биллинговая система, но если вместе с подпиской на сопровождение производителем, то внесение изменений в схему БД влечёт дополнительные затраты при обновлении на новую версию, осложняет применение патчей и т.п. вещей, выпускаемых производителем ПО. Либо штатный программист организации должен будет вручную перебирать все обновления производителя (что влечёт его (программиста) трудозатраты и снижает качество системы, поскольку наработки производителя по тестированию и отладке этих патчей пропадают), либо штатный программист будет вынужден вывести систему из эксплуатации, восстановить старую структуру БД, применить обновления, опять реализовать новую структуру БД (что влечёт трудозатраты программиста и затраты на простой). Обычно биллинговая система - не то, что можно вывести из эксплуатации, следовательно для вышеописанных манипуляций, нужен горячий резерв. Стоят ли затраты на поддержание горячего резерва затрат на дополнительные мощности для работы более медленной системы - не знаю, считать надо. Т.е. для меня повышение производительности в разы - это лишь высвобождение машинных ресурсов, обычно сравнительно дешёвых, если речь идёт об информационных системах среднего размера.

Я не говорю, что у тебя так, я не знаю, я просто указываю на то, о чём стоит думать, прежде чем браться за собственно программирование или даже проектирование ПО, затевать проекты переделки закупленного ПО.

<update statement: searched> ::=
              UPDATE <table name>
                SET <set clause list>
                [ WHERE <search condition> ]

         <set clause list> ::=
              <set clause> [ { <comma> <set clause> }... ]

         <set clause> ::=
              <object column> <equals operator> <update source>

         <update source> ::=
                <value expression>
              | <null specification>
              | DEFAULT

         <object column> ::= <column name>

1. использование табличных алиасов для ссылок на обновляемую таблицу в подзапросах
2. подзапросы в правой части предложения SET в отличие от только выражений в ANSI SQL

Вообще использование подзапросов для получения атомарного значения даже в SELECT - дело подозрительное потому, что SELECT возвращает таблицу, а не атомарное значение. Как правило в SELECT такие подзапросы несложно вынести в секцию FROM и сделать нужные JOIN. Согласен, что в ANSI SQL для серии обновлений в таблице по значениям других таблиц подразумевается использование курсоров, что плохо сказывается на производительности и более громоздко по записи. Тем не менее, когда начинаешь задумываться о блокировках записей, скорости прохождения параллельных транзакций через СУБД и оптимальности плана исполнения UPDATE с подзапросами, начинают приходить мысли о том, что комитет по стандартизации SQL не просто так остановился на данном решении.

3. список обновляемых колонок в левой части предложения SET, в отличии от одной колонки в ANSI SQL

Этого я не понял.

4. подзапросы в предложении SET или WHERE могут ссылаться на обновляемую таблицу

Если в СУБД используется секция FROM в UPDATE, то данное решение не совсем оправданно. Очень хорошо, когда запрос данных отделён от обновления данных - у программиста в голове меньше путаницы о том, какое значение (старое или новое) используется при такой операции. Особенно это касается случаев, когда внутри одной таблицы значения из одних записей используются для вычисления значений в других записях. Такие операции критически важно выполнять в правильном порядке - опять же см. выше доводы в пользу курсора, несмотря на его низкую производительность.

5. Оператор UPDATE поддерживает обновление подзапросов

Это я тоже не понял.

[Oldy]

Вот дискуссия то!

И как сильно эта дискуссия вам помогла, и чему научила?

[Dimka]

Oldy, её надо в другую тему выделить

[Dana]

select C_FIRST_NAME, C_SECOND_NAME, C_LAST_NAME, d_pay, n_sum
FROM
client,payment
WHERE
client.n_client = payment.n_client (+)
and n_SUM>500,d_pay>to_date('25.06.08','DD.MM.YY')

[Sla]

Dana, мы не в угадалки играем а пишем ПРАВИЛЬНЫЙ запрос

ты на чем-то свои запросы проверяешь?
Если проверяешь, то получаешь достаточно информативную ошибку

[McZim]

Я могу побыть копипастером. Dana, вот тебе ответ на твой запрос, на твоих же данных

[Dana]

Dana, мы не в угадалки играем а пишем ПРАВИЛЬНЫЙ запрос

ты на чем-то свои запросы проверяешь?
Если проверяешь, то получаешь достаточно информативную ошибку

ошибка, ваша команда не закончена "SQL command not properly ended" и что я должна из этого вынести?
Можно разбить на 2 запроса: сначала n_sum, а потом d_pay потому что по отдельности они выполняются, а с запятой не хотят

[McZim]

Dana, не верный перевод! SQL команда не верно завершена. Это значит что у тебя есть синтаксические ошибки, найди и исправь их.

Добавлено через 1 минуту и 11 секунд:

а с запятой не хотят

а ты точно знаешь что можно через запятую писать?

[Dana]

select C_FIRST_NAME, C_SECOND_NAME, C_LAST_NAME, d_pay, n_sum
FROM
client,payment
WHERE
client.n_client = payment.n_client (+)
and n_SUM>500 and
d_pay>to_date('25.06.08','DD.MM.YY')

[McZim]

Dana, и чего ты просто так показываешь свои селекты? Расскажи хоть он работает или нет, как работает, правильно или нет? Выполняет то что тебе нужно или нет?

[Dana]

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

[McZim]

Dana, а чего тут странного, такая реакция потому что ты не рассуждаешь и не решаешь свою задачу, а предлагаешь нам это сделать за тебя, выкладывая код.

Вопрос, ты точно уверена что тебе нужно внешнее соединение?

[Sla]

Dana,
1. мы не знаем твоих данных
2. мы хотим тебя научить правильно формулировать вопросы и научить самостоятельно пытаться на них. Надеюсь, что и ты этого же хочешь.

Если у тебя что-то спрашивают, то старайся ответить, а не играть в угадайку.

Вот ты привела запрос с (+), а почему ты не привела запрос с left join? и нужно ли оно здесь?

[McZim]

а почему ты не привела запрос с left join? и нужно ли оно здесь?

и почему left join? может right join или inner join?

P.S. это вопрос к Дане

[Dana]

Cлучаq с left join не подойдет, так каку меня нет главной таблицы из которой можно выбрать все записи. Поэтому использовала +

[McZim]

Dana, а в чем разница (+) и left join?

[Dusk]

Dana, прочитай здесь

[Dana]

Dana, прочитай здесь

Прочитала, по вашим записям тогда должно получиться:
select C_FIRST_NAME, C_SECOND_NAME, C_LAST_NAME, d_pay, n_sum
FROM
client,payment
left join payment on client.n_client=payment.n_client(+)
and n_SUM>500 and
d_pay>to_date('25.06.08','DD.MM.YY')
Кажется здесь вообще + не нужен.

[RXL]

Да, не нужен.

[Dana]

3.   Вывести все платежи за  воду в период с 5 апреля по 30 августа 2008 года. Указать, кем был совершен платеж.

Код:

select C_FIRST_NAME, C_SECOND_NAME, C_LAST_NAME, d_pay, n_sum
FROM
client,payment
WHERE
client.n_client = payment.n_client 
and d_pay between to_date('05.04.08','DD.MM.YY')and to_date('30.08.08','DD.MM.YY')
ORDER BY d_pay asc

[Sla]

и ШО сказал оракл?

[Dana]

C_FIRST_NAME C_SECOND_NAME C_LAST_NAME D_PAY N_SUM
Влада Александровна Вавилова 16.04.08 205
Алиса Сергеевна Сюсюк 16.04.08 1712
Светлана Евгеньевна Деревенец 16.04.08 778
Татьяна Александровна Сафрошкина 16.04.08 104
Луиза Сабиржановна Закирова 16.04.08 1156
Дарья Евгеньевна Чупа 16.04.08 744
Наталья Васильевна Вдовина 16.04.08 158
Лариса Михайловна Шадринова 16.04.08 522
Наталья Александровна Шаврина 16.04.08 628
Ярослав Олегович Клявлин 16.04.08 386
Юлия Рафаиловна Гайлиулина 16.04.08 468
Ольга Михайловна Соколова 16.04.08 100
Татьяна Петровна Алешина 16.04.08 696
Нелли Мугалимовна Ситдикова 16.04.08 250
Наталья Юрьевна Круценко 16.04.08 851
Олег Ринатович Шакиров 16.04.08 371
Светлана Сергеевна Эпова 16.04.08 537
Кристина Олеговна Чаус 16.04.08 290
Андрей Сергеевич Зеньков 16.04.08 100
Ирина Николаевна Дизендорф 16.04.08 225
Гульсина Тагировна Московенко 16.04.08 994
Ольга Вячеславовна Шеломидова 16.04.08 212
Андрей Михайлович Приходько 16.04.08 100
Михаил Анатольевич Димитров 16.04.08 508
Ксения Вячеславовна Васильева 16.04.08 464
Елена Викторовна Дмитриева 16.04.08 902
Юлия Петровна Булыгина 16.04.08 1243
Ирина Викторовна Касатова 16.04.08 145
Ольга Юрьевна Андриянова 16.04.08 1365
Антонина Сергеевна Кузнецова 16.04.08 629
Людмила Сергеевна Болонная 16.04.08 730
Мария Владимировна Мамонтова 16.04.08 751
Наталья Петровна Тенякова 16.04.08 305
Юлия Викторовна Казанцева 16.04.08 616
Мария Владимировна Фефелова 16.04.08 100
Мария Анатольевна Падерина 16.04.08 398
Диана Радиковна Каримова 16.04.08 1660
Татьяна Анатольевна Ефимова 16.04.08 470
Асия Равильевна Бедарева 16.04.08 1235
Виктория Игоревна Першукова 16.04.08 648
Оксана Ринатовна Растунцова 16.04.08 1718
Асия Илиясовна Хасанова 16.04.08 1164
Марат Гайнутдинович Камалов 16.04.08 612
Наталья Алексеевна Редько 16.04.08 575
Марина Юрьевна Булаева 16.04.08 195
.................................
________________________________________
1480 rows returned in 0,14 seconds

[Sla]

Dana, а вот скажи... если здесь использовать левое соединение таблиц, то - это будет плохо или хорошо, и почему?