Модераторы: LSD
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Сложный SQL запрос 
:(
    Опции темы
Гость_Гость
Дата 12.7.2005, 13:10 (ссылка) | (голосов: 0) Загрузка ... Загрузка ... Быстрая цитата Цитата


Unregistered
БД - Oracle

1. есть таблицы
Код

TABLE SUBJECTS (
    id NUMBER NOT NULL, 
    subjectName VARCHAR2(255), 
    PRIMARY KEY (id)
)  


TABLE SENSORS (
    id NUMBER NOT NULL, 
    subjectID NUMBER(2) NOT NULL, 
    moduleType NUMBER(4)NOT NULL, 
    moduleID NUMBER(4)  NOT NULL,
    dx NUMBER(6,5)      NOT NULL, 
    dy NUMBER(6,5)      NOT NULL, 
    description VARCHAR2(255), 
    FOREIGN KEY (subjectID) REFERENCES IASU_SUBJECTS (id), 
    PRIMARY KEY (id) 
)


TABLE SUB_SUBJECTS (
    subjectID NUMBER(2)  NOT NULL, 
    name Varchar2(255)   NOT NULL,  
    IP1 NUMBER(10)       NOT NULL, 
    IP2 NUMBER(10)       NOT NULL,  
    moduleType NUMBER(4) NOT NULL, 
    moduleID NUMBER(4)   NOT NULL, 
    sensorIP NUMBER(10),
    FOREIGN KEY (subjectID) REFERENCES IASU_SUBJECTS (id)
)  



1. в первой перечислены субъекты РФ
2. во второй - некие сенсоры, которые имеют modeleType, moduleID (однозначно их идентифицирующие) и привязка
к субъекту через subjectID
3. в третьей находятся пулы адресов для каждого сенсора (IP1, IP2) и привязка к субъекту через subjectID

это 3 таблицы, которые описывают субъекты, сенсоры и пулы адресов

есть таблица
Код

TABLE DATA(
    moduleType NUMBER(4) NOT NULL, 
    moduleID NUMBER(4)   NOT NULL, 
    IP NUMBER(10)       NOT NULL, 
                date DATE;
)  


в которую кладутся сообщения от сенсоров для определенного moduleType и moduleID, а также IP источника и время

Нужно создать запросы
1. Выбрать за определенной период времени сообщения от все сенсоров для данного субъекта
2. Количество сообщений от сенсоров для каждого субъекта в отсортированном виде

ну и в общем все в этом роде
сложность в том, что все данные разнесены по разным таблицам и запрос получается очень сложным

например - каждый сенсор может иметь несколько пулов адресов.
значит для первого запроса:
Код

SELECT DATA.date FROM DATA
WHERE 
    1. DATA.IP принадлежит любому пулу адресов, любого сенсора для заданного субъекта
    2. DATA.date принадлежит заданному диапазону
и все это отсортированно во возрастанию по полю DATA.date


Как правильно задать запрос ?

Мой вариант такой - но он долго выполняется и како-то кривой smile

Код

SELECT DATA.date FROM SUB_SUBJECTS, DATA
WHERE (DATA.date BETWEEN TO_DATE ('08.07.2005 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_DATE ('11.07.2005 3:59:59', 'dd.mm.yyyy hh24:mi:ss'))
AND (DATA.IP BETWEEN SUB_SUBJECTS.IP1 AND SUB_SUBJECTS.IP2) 
AND (SUB_SUBJECTS.subjectID = 0) ORDER BY DATA.date ASC;
  Вверх
Beard
Дата 12.7.2005, 13:19 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Бывалый
*


Профиль
Группа: Участник
Сообщений: 228
Регистрация: 5.7.2005
Где: Санкт-Петербург

Репутация: нет
Всего: 9
Может стоит View создать для связи этих таблиц и оперировать с ним?
PM MAIL   Вверх
LSD
Дата 12.7.2005, 13:20 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Индексы по полям DATA.IP, SUB_SUBJECTS.IP1, SUB_SUBJECTS.IP2, DATA.DATE и SUB_SUBJECTS.subjectID созданны? (Их кстати по возможности лучше сделать уникальными)

SUB_SUBJECTS.IP1, SUB_SUBJECTS.IP2 - задают диапазон адресов?

P.S. Никаких особых косяков в запросе я не вижу.


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
Guest
Дата 12.7.2005, 13:26 (ссылка) | (голосов: 0) Загрузка ... Загрузка ... Быстрая цитата Цитата


Unregistered
Цитата(LSD @ 12.7.2005, 13:20)
SUB_SUBJECTS.IP1, SUB_SUBJECTS.IP2 - задают диапазон адресов?


Да - это диапазон адресов



Цитата(LSD @ 12.7.2005, 13:20)
P.S. Никаких особых косяков в запросе я не вижу.

я не уверен, что
Код

AND (DATA.IP BETWEEN SUB_SUBJECTS.IP1 AND SUB_SUBJECTS.IP2)
AND (SUB_SUBJECTS.subjectID = 0)


проверит принадлежность DATA.IP для всех пулов адресов для всех сенсоров для субъекта с индексом 0
  Вверх
LSD
Дата 12.7.2005, 13:55 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Цитата(Guest @ 12.7.2005, 14:26)
я не уверен, что
Код

AND (DATA.IP BETWEEN SUB_SUBJECTS.IP1 AND SUB_SUBJECTS.IP2)
AND (SUB_SUBJECTS.subjectID = 0)


проверит принадлежность DATA.IP для всех пулов адресов для всех сенсоров для субъекта с индексом 0

Вполне возможно, если для условю SUB_SUBJECTS.subjectID = 0 соответсвует больше одной строки. А как диапазоны соотносятся между собой, они всегда образуют непрерывный диапазон или в нем могут быть дыры?


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
Guest
Дата 12.7.2005, 14:04 (ссылка) | (голосов: 0) Загрузка ... Загрузка ... Быстрая цитата Цитата


Unregistered
Цитата(LSD @ 12.7.2005, 13:55)
Вполне возможно, если для условю SUB_SUBJECTS.subjectID = 0 соответсвует больше одной строки


нет - строка одна


Цитата(LSD @ 12.7.2005, 13:55)
А как диапазоны соотносятся между собой, они всегда образуют непрерывный диапазон или в нем могут быть дыры?


есть ли дыры или нет - это мне неважно.
Проблемма в том, что диапозонов может быть несколько, поэтому проверит ли это условие принадлежность IP для всех существующих диапазонов ? (Да еще только для тех диапазонов, которые принадлежат субъекту=0?)
  Вверх
Guest
Дата 12.7.2005, 14:08 (ссылка) | (голосов: 0) Загрузка ... Загрузка ... Быстрая цитата Цитата


Unregistered
тоесть например

есть для субъекта 0 сенсор 1

для сенсора есть диапазоны
1..10
13..40
55..60 (это так, для примера)

в DATA.IP = 5

так вот проверятся ли все 3 диапазона для субъекта = 0?
  Вверх
LSD
Дата 12.7.2005, 14:26 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Цитата(Guest @ 12.7.2005, 15:08)
так вот проверятся ли все 3 диапазона для субъекта = 0?

Да.


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
Guest
Дата 13.7.2005, 14:30 (ссылка) | (голосов: 0) Загрузка ... Загрузка ... Быстрая цитата Цитата


Unregistered
Спасибо LSD

А можно ли оптимизировать запрос - ну например объединение или еще что-нибудь, а то время запроса у Oracle для 2 миллионов строк составляет около 1 минуты (что-то слишком много smile)
  Вверх
LSD
Дата 13.7.2005, 15:02 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Приведи полный скрипт по созданию таблиц (включая индексы). Собери по таблицам статистику и покажи план запроса.


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
igon
Дата 13.7.2005, 21:57 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 327
Регистрация: 30.10.2004

Репутация: 3
Всего: 21
Если адресов не очень много, можно попробовать избавиться от BETWEEN для IP, создав и заполнив таблицу
Код

IP_SUB_SUBJECTS (
    IP NUMBER(10)       NOT NULL, -- PRIMARY KEY
    subjectID NUMBER(2)  NOT NULL) -- Index

Тогда запрос будет выглядеть так
Код

SELECT DATA.date FROM IP_SUB_SUBJECTS, DATA
WHERE (DATA.date BETWEEN TO_DATE ('08.07.2005 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_DATE ('11.07.2005 3:59:59', 'dd.mm.yyyy hh24:mi:ss'))
AND (DATA.IP = IP_SUB_SUBJECTS.IP ) 
AND (IP_SUB_SUBJECTS.subjectID = 0) ORDER BY DATA.date ASC;

P.S.: Чем, в контексте вопроса, может помочь таблица SENSORS? Она на запрос вроде никак не влияет.
Код


    moduleType NUMBER(4)NOT NULL, 
    moduleID NUMBER(4)  NOT NULL,
встречается в 3-х местах. Не многовато ли?

Это сообщение отредактировал(а) igon - 13.7.2005, 22:15


--------------------
Хотите поговорить об этом?
PM   Вверх
Guest
Дата 14.7.2005, 15:46 (ссылка) | (голосов: 0) Загрузка ... Загрузка ... Быстрая цитата Цитата


Unregistered
Цитата(LSD @ 13.7.2005, 15:02)
Приведи полный скрипт по созданию таблиц (включая индексы). Собери по таблицам статистику и покажи план запроса.



Таблицы
Код

CREATE TABLE IASU_SUBJECTS (
    id NUMBER NOT NULL, 
    subjectName VARCHAR2(255), 
    PRIMARY KEY (id)
)  
TABLESPACE  TestIASVU; 


CREATE TABLE IASU_SENSORS (
    id NUMBER NOT NULL, 
    subjectID NUMBER(2) NOT NULL, 
    moduleType NUMBER(4)NOT NULL, 
    moduleID NUMBER(4)  NOT NULL,
    dx NUMBER(6,5)      NOT NULL, 
    dy NUMBER(6,5)      NOT NULL, 
    description VARCHAR2(255), 
    FOREIGN KEY (subjectID) REFERENCES IASU_SUBJECTS (id), 
    PRIMARY KEY (id)
) 
TABLESPACE  TestIASVU;


CREATE TABLE IASU_SUB_SUBJECTS (
    subjectID NUMBER(2)  NOT NULL, 
    name Varchar2(255)   NOT NULL,  
    IP1 NUMBER(10)       NOT NULL, 
    IP2 NUMBER(10)       NOT NULL,  
    moduleType NUMBER(4) NOT NULL, 
    moduleID NUMBER(4)   NOT NULL, 
    sensorIP NUMBER(10),
    FOREIGN KEY (subjectID) REFERENCES IASU_SUBJECTS (id)
)  
TABLESPACE  TestIASVU;  

CREATE TABLE Data1 (
    id NUMBER NOT NULL, 
    moduleType NUMBER(4) NOT NULL, 
    moduleID NUMBER(4) NOT NULL,
    timeMoment DATE NOT NULL, 
    attackCode NUMBER(8) NOT NULL, 
    srcIP  NUMBER(10),
    srcPort NUMBER(5), 
    dstIP  NUMBER(10), 
    dstPort NUMBER(5),
    protocol Varchar2(20), 
    priority Number(4), 
    flag CHAR(1), 
    submoduleID NUMBER(4),
    revision NUMBER(4), 
    status CHAR(1) , 
    info Varchar2(2000), 
    PRIMARY KEY (id)
)
TABLESPACE  TestData1; 


Данные
Код

Данные IASU_SUBJECTS
0    Россия
1    Республика Адыгея
2    Республика Башкортостан
3    Республика Бурятия
4    Республика Алтай
5    Республика Дагестан
6    Республика Ингушетия
7    Кабардино-Балкарская Республика
8    Карачаево-Черкесская Республика
9    Республика Калмыкия
10    Республика Карелия
11    Республика Коми
12    Республика Марий Эл
13    Республика Мордовия
14    Республика Саха
15    Республика Северная Осетия
16    Республика Татарстан
17    Республика Тува
18    Удмуртская республика
19    Республика Хакасия
20    Чеченская республика
21    Чувашская республика
22    Алтайский край
23    Краснодарский край
24    Красноярский край
25    Приморский край
26    Ставропольский край
27    Хабаровский край
28    Амурская область
29    Архангельская область
30    Астраханская область
31    Белгородская область
32    Брянская область
33    Владимирская область
34    Волгоградская область
35    Вологодская область
36    Воронежская область
37    Ивановская область
38    Иркутская область
39    Калининградская область
40    Калужская область
41    Камчатская область
42    Кемеровская область
43    Кировская область
44    Костромская область
45    Курганская область
46    Курская область
47    Ленинградская область
48    Липецкая область
49    Магаданская область
50    Московская область
51    Мурманская область
52    Нижегородская область
53    Новгородская область
54    Новосибирская область
55    Омская область
56    Оренбургская область
57    Орловская область
58    Пензенская область
59    Пермская область
60    Псковская область
61    Ростовская область
62    Рязанская область
63    Самарская область
64    Саратовская область
65    Сахалинская область
66    Свердловская область
67    Смоленская область
68    Тамбовская область
69    Тверская область
70    Томская область
71    Тульская область
72    Тюменская область
73    Ульяновская область
74    Челябинская область
75    Читинская область
76    Ярославская область
77    Москва
78    Санкт-Петербург
79    Еврейская автономная область
80    Агинский Бурятский АО
81    Коми-Пермяцкий АО
82    Корякский АО
83    Ненецкий АО
84    Таймырский АО
85    Бурятский АО
86    Ханты-Мансийский АО
87    Чукотский АО
88    Эвенкийский АО
89    Ямало-Ненецкий АО

IASU_SENSORS
1    0    1    1    0.16358    0.59055    module

IASU_SUB_SUBJECTS
0    pool2    3269620736    3269621759    1    1    0
0    pool1    3269611520    3269615615    1    1    0

DATA1
4287686    1    1    2005-07-14 16:41:48    4        3269612306    50021    3451713744    80        6    0    0    119    1    U    (null)
4287687    1    1    2005-07-14 16:41:49    2229    3269612306    50024    3432850796    80        6    0    0    1    4    U    (null)
4287688    1    1    2005-07-14 16:41:49    2925    3641709557    80        3269611662    1038    6    0    0    1    2    U    (null)
4287689    1    1    2005-07-14 16:41:49    2925    3259182436    80        3269611662    1041    6    0    0    1    2    U    (null)
4287690    1    1    2005-07-14 16:41:49    2586    3269613573    62213    1415791087    4242    6    0    0    1    1    U    (null)
4287691    1    1    2005-07-14 16:41:49    2586    3269613573    61786    1415540940    4242    6    0    0    1    1    U    (null)
4287692    1    1    2005-07-14 16:41:49    402        1360667043    0        3269611843    0        1    10    1    1    7    U    (null)
4287693    1    1    2005-07-14 16:41:49    648        3269611588    4382    411232298    4662    6    0    0    1    7    U    (null)
  Вверх
LSD
Дата 19.7.2005, 16:15 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Leprechaun Software Developer
****


Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 24
Всего: 538
Я посмотрел следующий запрос:
Код
SELECT t2.TIMEMOMENT FROM IASU_SUB_SUBJECTS t1, DATA1 t2
  WHERE (t2.timemoment BETWEEN TO_DATE ('14.07.2005 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_DATE ('15.07.2005 00:00:00', 'dd.mm.yyyy hh24:mi:ss'))
  AND (t2.srcip BETWEEN t1.IP1 AND t1.IP2)   
  AND (t1.subjectID = 0) ORDER BY t2.TIMEMOMENT ASC
/

Без индексов тут будет следующий план запроса:
Код
План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=26)
   1    0   SORT (ORDER BY) (Cost=8 Card=1 Bytes=26)
   2    1     NESTED LOOPS (Cost=7 Card=1 Bytes=26)
   3    2       TABLE ACCESS (FULL) OF 'IASU_SUB_SUBJECTS' (TABLE) (Cost=3 Card=2 Bytes=26)
   4    2       TABLE ACCESS (FULL) OF 'DATA1' (TABLE) (Cost=2 Card=1 Bytes=13)

т.е. полное сканирование таблиц. В принципе оно может быть эффективным, если идет выборка более 50 процентов данных, но это по моему не тот случай (особенно для DATA1).
Если создать индексы
Код
create index IASU_SUB_SUBJECTS_SUBJ_IDX on IASU_SUB_SUBJECTS (SUBJECTID);
create index DATA1_SRCIP_IDX on DATA1 (SRCIP);
create index DATA1_TIMEMOMENT_IDX on DATA1 (TIMEMOMENT);

То план будет следующий
Код
План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=26)
   1    0   SORT (ORDER BY) (Cost=7 Card=1 Bytes=26)
   2    1     MERGE JOIN (Cost=6 Card=1 Bytes=26)
   3    2       SORT (JOIN) (Cost=3 Card=2 Bytes=26)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'IASU_SUB_SUBJECTS' (TABLE) (Cost=2 Card=2 Bytes=26)
   5    4           INDEX (RANGE SCAN) OF 'IASU_SUB_SUBJECTS_SUBJ_IDX' (INDEX) (Cost=1 Card=2)
   6    2       FILTER
   7    6         SORT (JOIN) (Cost=3 Card=8 Bytes=104)
   8    7           TABLE ACCESS (BY INDEX ROWID) OF 'DATA1' (TABLE) (Cost=2 Card=8 Bytes=104)
   9    8             INDEX (RANGE SCAN) OF 'DATA1_TIMEMOMENT_IDX' (INDEX) (Cost=1 Card=1)

выборка идет уже не по всей таблице а по диапазону индекса.

Посмотри так, что будет с запросом.


--------------------
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
PM MAIL WWW   Вверх
  
 Ответ в темуСоздание новой темы Создание опроса
Правила форума "Общие вопросы по базам данных"
LSD
Zloxa

Данный форум предназначен для обсуждения вопросов о базах данных не попадающих под тематику других форумов:

  • вопросам по СУБД для которых нет отдельных подфорумов
  • вопросам которые затрагивают несколько разных СУБД (например проблема выбора)
  • инструменты для работы с СУБД
  • вопросы проектирования БД
  • теоретически вопросы о СУБД

Данный форум не предназначен для:

  • вопросов о поиске разлиных БД (если не понимаете чем БД отличается от СУБД то: а) вам не сюда; б) Google в помощь)
  • обсуждения проблем с доступом к СУБД из различных ЯП (для этого есть соответсвующие форумы по каждому ЯП)
  • обсуждения проблем с написание SQL запросов, для этого есть форум Составление SQL-запросов
  • просьб о написании курсовой, реферата и т.п., для этого есть Центр помощи или фриланс биржа
  • объявлений о найме специалистов, для этого есть раздел Объявления о найме специалистов

Если вы не соблюдаете эти правила, не удивляйтесь потом не найдя свою тему/сообщение. ;)

Полезные советы:

При написании сообщения постарайтесь дать теме максимально понятное название. В теме максимально подробно опишите проблему. Если применимо укажите: название базы данных и версии (MySQL 4.1, MS SQL Server 2000 и т.п.); используемых язык программирования; способа доступа (ADO, BDE и т.д.); сообщения об ошибках.

Для вставки кода используйте теги [code=sql] [/code].

Литературу по базам данных можно поискать здесь.

Действия модераторов можно обсудить здесь.

Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, LSD, Zloxa.
 
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | СУБД, общие вопросы | Следующая тема »

Подписаться на тему | Подписка на этот форум | Скачать/Распечатать тему


 




[ Время генерации скрипта: 0.0610 ]   [ Использовано запросов: 22 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
 По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.