ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимальный план запроса
10 сообщений из 10, страница 1 из 1
Оптимальный план запроса
    #40079717
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
Есть учебная база, в которой есть табличка со справочником преподавателей PREPODS и табличка со справочником студентов STUDENTS.

Между ними связь многие-ко-многим, т.е. есть табличка для связки PREP_STUD, в которой внешний ключ PREP_ID на PREPODS и STUD_ID - на STUDENTS, ну а первичный ключ составной PREP_ID,STUD_ID. На внешние ключи созданы индексы IND_PREP_STUD__PREP_ID и IND_PREP_STUD__STUD_ID

Требуется написать запрос:

Получить список всех преподавателей, которые не обучают ни одного студента.

Первое что приходит в голову - это написать такой запрос:

Код: plsql
1.
2.
3.
select *
from PREPODS p
where p.PREP_ID not in (select ps.PREP_ID from PREP_STUD ps)


Но в плане будет TABLE ACCESS FULL таблицы преподавателей:

Код: plsql
1.
2.
3.
4.
5.
                                                         Cost  Cord.   Bytes
SELECT STATEMENT, GOAL = ALL_ROWS			  2	1	177
 NESTED LOOPS ANTI			                  2	1	177
  TABLE ACCESS FULL	  TST   PREPODS  	          2	1	164
  INDEX RANGE SCAN	  TST	IND_PREP_STUD__PREP_ID	  0	1	13


Как в данном случае написать более оптимальный запрос?
...
Рейтинг: 0 / 0
24.06.2021, 00:38
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079719
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
Была идея создать такую функцию:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
create function f_is_cur(pn_prep_id in integer) return integer is
  ln_res integer;
begin
  select count(*) into ln_res from PREP_STUD ps
  where ps.PREP_ID = pn_prof_id;

  return(ln_res);
end;


затем сделать индекс по этой функции в таблице преподавателей:

Код: plsql
1.
create index IND_PREPODS_F1 on PREPODS (f_is_cur(PREP_ID))


ну а затем в запросе использовать эту функцию:

Код: plsql
1.
2.
3.
select *
from PREPODS p
where f_is_cur(PREP_ID)=0


Но индекс по такой функции не удаётся создать, потому что она не детерминистская, т.е. для одних и тех же значений параметра может выдавать разный результат, поэтому такой индекс нельзя создать пишет нам оракл.
...
Рейтинг: 0 / 0
24.06.2021, 01:11
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079739
PuM256
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PuM256
Гость
Тут дело не в запросе, а в статистике.
Запрос нормальный, разве что я бы not in заменил на not exists.
...
Рейтинг: 0 / 0
24.06.2021, 09:18
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079741
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
verter

Но в плане будет TABLE ACCESS FULL таблицы преподавателей:

что ужасного в TABLE ACCESS FULL?

ps
другие варианты
not exists (...)
внешнее соединение

зы
для древних версий статейка была
(not) in и (not) exists

.....
stax
...
Рейтинг: 0 / 0
24.06.2021, 09:25
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079743
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Stax

для древних версий статейка была
(not) in и (not) exists

https://asktom.oracle.com/pls/apex/f?p=100:11:0::no::p11_question_id:442029737684
https://mir-oracle.com.ua/oracle-tech-info/13-otkryitooboracle/22--sql-.html

......
stax
...
Рейтинг: 0 / 0
24.06.2021, 09:37
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079748
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
Ну да, с not exists тоже альтернативный вариант, просто план у него такой же.

А вот если нужен вот такой запрос:

Получить список всех преподавателей, у которых 5 и более студентов, с указанием количества студентов сверх 5.

На ум приходит такой вариант:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select r.*
from (       
select p.*,
       (select count(*) from PREP_STUD ps
        where ps.PREP_ID = p.PREP_ID) - 5 as cnt
from PREPODS p       
) r
where r.cnt >= 0


План такой:

Код: plsql
1.
2.
3.
4.
5.
6.
                                                       Cost    Card.   Bytes
SELECT STATEMENT, GOAL = ALL_ROWS			2	1	177
 SORT AGGREGATE				                1	13
  INDEX RANGE SCAN	TST	IND_PREP_STUD__PREP_ID	1	1	13
 VIEW	TST		                                2	1	177
  TABLE ACCESS FULL	TST	PREPODS	                2	1	164
...
Рейтинг: 0 / 0
24.06.2021, 09:54
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079750
123йй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
123йй
Участник
verter,

при определенных условиях таблица преподователей не нужна.
Код: plsql
1.
2.
3.
4.
5.
6.
select count(*) , id from 
(select level ,2 id from dual connect by level<3
union
select level ,3 id from dual connect by level<5)
group by id
having count(*) >2
...
Рейтинг: 0 / 0
24.06.2021, 10:15
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40079752
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
verter

А вот если нужен вот такой запрос:

Получить список всех преподавателей, у которых 5 и более студентов, с указанием количества студентов сверх 5.

На ум приходит такой вариант:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select r.*
from (       
select p.*,
       (select count(*) from PREP_STUD ps
        where ps.PREP_ID = p.PREP_ID) - 5 as cnt
from PREPODS p       
) r
where r.cnt >= 0



зачем изобретать велосипеды? - для включения индексов базовый подход:
Код: plsql
1.
2.
3.
4.
5.
with arr as (select ps.PREP_ID, count(STUD_ID) as cnt_stud from PREP_STUD ps
       group by ps.PREP_ID having count(STUD_ID) > 4)
select p.*, a.cnt_stud       
from PREPODS p, arr a
where p.PREP_ID=a.PREP_ID



PS: мимо проходил
...
Рейтинг: 0 / 0
24.06.2021, 10:27
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40080074
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
verter
Участник
Fogel


зачем изобретать велосипеды? - для включения индексов базовый подход:
Код: plsql
1.
2.
3.
4.
5.
with arr as (select ps.PREP_ID, count(STUD_ID) as cnt_stud from PREP_STUD ps
                 group by ps.PREP_ID having count(STUD_ID) > 4)
select p.*, a.cnt_stud       
from PREPODS p, arr a
where p.PREP_ID = a.PREP_ID



PS: мимо проходил


Да, так план становится намного лучше:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
                                                Cost  Card.    Bytes
SELECT STATEMENT, GOAL = ALL_ROWS		  2	1	51
 NESTED LOOPS					
  NESTED LOOPS		                          2	1	51
   VIEW	TST		                          1	1	26
    FILTER					
     HASH GROUP BY			          1	1	3
      INDEX FULL SCAN	TST	PK__PREP_STUD	  1	17	51
   INDEX UNIQUE SCAN	TST	PK__PREPODS	  0	1	
  TABLE ACCESS BY INDEX ROWID	TST	PREPODS	  1	1	25



А почему в вашем случае идёт по индексам, а в моём нет?
...
Рейтинг: 0 / 0
25.06.2021, 11:26
| Ответить | Цитировать | Написать  
Оптимальный план запроса
    #40080085
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
verter
Fogel


зачем изобретать велосипеды? - для включения индексов базовый подход:
Код: plsql
1.
2.
3.
4.
5.
with arr as (select ps.PREP_ID, count(STUD_ID) as cnt_stud from PREP_STUD ps
                 group by ps.PREP_ID having count(STUD_ID) > 4)
select p.*, a.cnt_stud       
from PREPODS p, arr a
where p.PREP_ID = a.PREP_ID



PS: мимо проходил


Да, так план становится намного лучше:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
                                                Cost  Card.    Bytes
SELECT STATEMENT, GOAL = ALL_ROWS		  2	1	51
 NESTED LOOPS					
  NESTED LOOPS		                          2	1	51
   VIEW	TST		                          1	1	26
    FILTER					
     HASH GROUP BY			          1	1	3
      INDEX FULL SCAN	TST	PK__PREP_STUD	  1	17	51
   INDEX UNIQUE SCAN	TST	PK__PREPODS	  0	1	
  TABLE ACCESS BY INDEX ROWID	TST	PREPODS	  1	1	25



А почему в вашем случае идёт по индексам, а в моём нет?

в твоем случае он не может до джоина узнать сколько студентов у препода и поэтому вынужден перебирать ВСЕХ преподов.
А перебирать всех по индексу медленнее чем фулсканом.
А так мы можем ДО джоина выбрать нужных преподов только на базе PREP_STUD, и если окажется что их сильно меньше чем всех преподов - то нестед луп с индексным поиском будет быстрее
...
Рейтинг: 0 / 0
25.06.2021, 11:58
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимальный план запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=16&tid=1880086]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 33ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 12ms
total:  146ms
созд. / загр.: 146ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]