Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Что лучше: вложенный запрос или join?  [new]
AlexG
Member

Откуда: Санкт-Петербург
Сообщений: 912
Есть две связанные таблицы, одна из которых является справочником. Например, "Книги" и "Тип книги".
При показе информации можно написать так:
select b.isbn,
       b.name,
       bt.name as type
  from book b
  left join book_type bt on bt.id = b.type_id
А можно так:
select b.isbn,
       b.name,
       (select * from book_type 
        where id = type_id) as type
  from book b
Запросы, конечно, абстрактные. В реальности используются более сложные. Но, надеюсь, смысл понятен.
Так вот, мне интересно в каком случае, что лучше использовать? Или может какой-то один вариант всегда желательно использовать?
3 апр 06, 13:45    [2517758]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
AlexG
select b.isbn,
       b.name,
       (select * from book_type 
        where id = type_id) as type
  from book b
Запросы, конечно, абстрактные.


Даже абстрактные запросы желательно писать правильно.
Скалярное выражение возвращает только одну колонку, что Вам и надо.

select b.isbn,
       b.name,
       (select name from book_type 
        where id = b.type_id) as type
  from book b

ИМХО скалярные подзапросы предпочтительнее, хотя лучше потестировать.
3 апр 06, 13:51    [2517795]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
AlexG
Member

Откуда: Санкт-Петербург
Сообщений: 912
dmidek
AlexG
select b.isbn,
       b.name,
       (select * from book_type 
        where id = type_id) as type
  from book b
Запросы, конечно, абстрактные.


Даже абстрактные запросы желательно писать правильно.
Скалярное выражение возвращает только одну колонку, что Вам и надо.


Блин, ну не заметил...
3 апр 06, 13:56    [2517817]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
Sergey M
Member

Откуда: г. Барнаул
Сообщений: 5462
AlexG
Есть две связанные таблицы, одна из которых является справочником. Например, "Книги" и "Тип книги".
При показе информации можно написать так:
select b.isbn,
       b.name,
       bt.name as type
  from book b
  left join book_type bt on bt.id = b.type_id
А можно так:
select b.isbn,
       b.name,
       (select * from book_type 
        where id = type_id) as type
  from book b
Запросы, конечно, абстрактные. В реальности используются более сложные. Но, надеюсь, смысл понятен.
Так вот, мне интересно в каком случае, что лучше использовать? Или может какой-то один вариант всегда желательно использовать?

Теоретически, оптимизатор вложенные подзапросы раскрывает (преобразует к джойнам). В этом простом примере вероятность 99% что он именно так и сделает (см. планы запросов там видно преобразовал он запрос или нет) .
3 апр 06, 14:09    [2517892]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
miner
Member

Откуда: Moscow
Сообщений: 206
Всё зависит от подзапроса. И размера выборки.
Если ваш подзапрос считает сложную статистику и выборка небольшая, то лучше коррелированный подзапрос или же ограничивать выборку внутри подзапроса во FROM.
В приведённом же случае левое внешнее соединение как раз то что нужно.
3 апр 06, 14:09    [2517893]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
AlexG
Member

Откуда: Санкт-Петербург
Сообщений: 912
miner
...коррелированный подзапрос...

Это ты что имеешь ввиду?
3 апр 06, 14:20    [2517959]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
miner
Member

Откуда: Moscow
Сообщений: 206
AlexG
miner
...коррелированный подзапрос...

Это ты что имеешь ввиду?

Ну как раз приведённый подзапрос и есть коррелированный - ссылается на значения внешнего запроса и выполняется для каждой выбранной в нём записи.
3 апр 06, 14:29    [2518000]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
alex-ls
Member

Откуда: Иркутская обл - Пенза - Москва
Сообщений: 6916
ИМХО лучше план посмотреть в конкретном случае...
3 апр 06, 14:55    [2518165]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
Фрол
Guest
Планы абсолютно разные:

select a.id,
s.id sid 
from s, a
where a.sid=s.id

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |    18M|   304M|   239K  (2)| 00:47:57 |
|*  1 |  HASH JOIN              |                  |    18M|   304M|   239K  (2)| 00:47:57 |
|   2 |   INDEX FAST FULL SCAN  | PK_S_ID          |  9671 | 48355 |     6   (0)| 00:00:01 |
|   3 |   VIEW                  | index$_join$_002 |    18M|   214M|   239K  (2)| 00:47:52 |
|*  4 |    HASH JOIN            |                  |       |       |            |          |
|   5 |     INDEX FAST FULL SCAN| INDEX_A          |    18M|   214M| 69300   (2)| 00:13:52 |
|   6 |     INDEX FAST FULL SCAN| INDEX_A$M        |    18M|   214M|   111K  (2)| 00:22:16 |
--------------------------------------------------------------------------------------------
   1 - access("A"."SOURCE_ID"="S"."ID")
   4 - access(ROWID=ROWID)




select a.id,
(select s.id sid 
from s
where a.sid=s.id)
from a

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |    18M|   214M|   894K  (1)| 02:59:00 |
|*  1 |  INDEX UNIQUE SCAN | PK_S_ID      |     1 |     5 |     1   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A            |    18M|   214M|   894K  (1)| 02:59:00 |

3 апр 06, 15:26    [2518380]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
Elic
Member

Откуда:
Сообщений: 29990
AlexG
Есть две связанные таблицы, одна из которых является справочником.
Хоть это немного и зависит от размера справочника, но я считаю что каждый раз соединять основную таблицу со справочником (а уж тем более со справочниками) - это перебор, ибо никому не нужный лишний траффик.
Современные клиентские библиотеки умеет поодерживать так называемые lookup-field-ы, при использовании которых справочник(и) один раз закачивается на клиента, а потом вовсю используется для расшифровки кода по справочнику. При этом основной запрос прост как топор: select * from table. По сути это тот же scalar-subquery, только гораздо быстрее.
3 апр 06, 15:36    [2518446]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
Фрол
Планы абсолютно разные:

select a.id,
s.id sid 
from s, a
where a.sid=s.id



Да и запросы разные.
Где outer join ?
Если Вы конечно имеете отношение к автору ...
3 апр 06, 15:36    [2518448]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
Фрол
Guest
dmidek
Фрол
Планы абсолютно разные:

select a.id,
s.id sid 
from s, a
where a.sid=s.id



Да и запросы разные.
Где outer join ?
Если Вы конечно имеете отношение к автору ...


Прошу прощения, поторопился, вот другой план

select a.id
  from a a
  left join s on a.sid = s.id

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |    18M|   304M|   930K  (5)| 03:06:08 |
|   1 |  NESTED LOOPS OUTER|              |    18M|   304M|   930K  (5)| 03:06:08 |
|   2 |   TABLE ACCESS FULL| A            |    18M|   214M|   894K  (1)| 02:59:00 |
|*  3 |   INDEX UNIQUE SCAN| PK_S_ID      |     1 |     5 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   3 - access("A"."SID"="S"."ID"(+))
3 апр 06, 16:10    [2518694]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
Q u a d r o
Member

Откуда: Canada
Сообщений: 1987
subquery имеют обычай тяготеть к задиранию LIO до небес, к тому же нам нужен хороший и быстрый путь доступа для subquery:

SQL> create table t1 as select object_id from all_objects;

Table created.

SQL> create table t2 as select object_id, object_name from all_objects;

Table created.

SQL> create index i_t2 on t2 (object_id) nologging;

Index created.

SQL> select  t1.object_id,
  2   (select t2.object_name from t2 where t1.object_id=t2.object_id)
  3   from t1;

49260 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2656203860

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 53160 |   674K|    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2   |   453 | 13590 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T2 |   181 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | T1   | 53160 |   674K|    18   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("T2"."OBJECT_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     105270  consistent gets
          0  physical reads
          0  redo size
    1907619  bytes sent via SQL*Net to client
      36493  bytes received via SQL*Net from client
       3285  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49260  rows processed

(попробуйте на досуге дождаться выполнения этого запроса без индекса по t2.object_id)

просто джойн положит на все индексы и сделает ту же самую работу используя в 30 раз меньше LIO:

SQL> select t1.object_id, t2.object_name
  2   from t1, t2
  3   where t1.object_id=t2.object_id(+);

49260 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 53160 |  2232K|       |   228   (2)| 00:00:03 |
|*  1 |  HASH JOIN OUTER   |      | 53160 |  2232K|  1304K|   228   (2)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| T1   | 53160 |   674K|       |    18   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 45287 |  1326K|       |    54   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+))

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3575  consistent gets
          0  physical reads
          0  redo size
    1907572  bytes sent via SQL*Net to client
      36493  bytes received via SQL*Net from client
       3285  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49260  rows processed
3 апр 06, 16:13    [2518711]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
AlexG
Так вот, мне интересно в каком случае, что лучше использовать? Или может какой-то один вариант всегда желательно использовать?

Позволю себе высказать мнение, не пытаясь его обосновать. По дефолту, если возможен join - следует использовать именно его. Далее, если производительность не устраивает - надо лезть в планы, разбираться, и в том числе выделение подзапроса может оказаться методом оптимизации.
3 апр 06, 19:06    [2519487]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: вложенный запрос или join?  [new]
nata1111
Member

Откуда:
Сообщений: 1800
Оракл не может подзапросы в фразе select преобразовывать к join.
3 апр 06, 20:20    [2519625]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить