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

Откуда:
Сообщений: 20
Приветствую!

Описываю ситуацию: в базе 3800 человек сдавали тестирование, в тестировании 120 вопросов.

Нужно заполнить следующую таблицу с такими полями:
| Id-registration | date_testing | fio | region | code | pte | ptbe | ptbt | score | min_point | status | language | signature | v1 | a1 | c1 | v2 | a2| c2 |.............. |v120 | a120 | c120 |

Первые 13 полей понятно, данные о тестируемом лице, дальше идут vN, aN, cN до v120, a120, c120. vN - вопрос, aN - ответ, cN - правильно или нет.

Т.е таблица с количеством столбцов 133 (понимаю, маразм, но так нужно заказчику).

Первые 13 полей, т.е тестируемых вытаскиваю так:

select r.id_registration as id_registration,
       r.date_testing as date_testing,
       helper.getFIO(r.id_person) as fio,
       helper.getNameRegion(r.id_region) as region,
       helper.getCodeBundle(r.id_bundle) as code,
       helper.countSuccessQuestionPTECT(r.id_registration) as pte,
       helper.countSuccessQuestionPTBe(r.id_registration)as ptbe,
       helper.countSuccessQuestionPTBT(r.id_registration) as ptbt,
       helper.countSuccessQuestion(r.id_registration) as score,
       b.min_point as min_point,
       case when substr(r.status,1,10)='Не пройден' then 'Нет'
            when substr(r.status,1,7)='Пройден' then 'Да'
            else '---' end as status,
       r.language as language,
       r.signature as signature
  from test_operator.registration r, test_operator.bundle b
  where r.id_bundle=b.id_bundle
  and r.signature is not null
  and   trunc(r.beg_time_testing,'dd')>=to_date('03.12.2013','dd.mm.yyyy')
  and   trunc(r.end_time_testing,'dd')<=to_date('20.01.2014','dd.mm.yyyy')
  order by r.date_testing, helper.getFIO(r.id_person)


Результаты каждого тестируемого вытаскиваю так (в зависимости от id_registration):
select q.question as question, r.reply as reply, r.correctly as correctly
            from TEST_OPERATOR.questions_for_testing qt, TEST_OPERATOR.questions q, TEST_OPERATOR.replies r
            where qt.id_question = q.id_question
            and r.id_reply = qt.id_reply
            and qt.id_registration=1234
            order by qt.id_theme, qt.order_num_question


Создал таблицу super_report со 133 столбцами. И написал процедуру по ее заполнению:
procedure fill_super_report
is
v_id_registration test_operator.registration.id_registration%type;
v_date_testing date;
v_fio nvarchar2(256);
v_region nvarchar2(128);
v_code varchar(20);
v_pte number;
v_ptbe number;
v_ptbt number;
v_score number;
v_min_point number;
v_status nvarchar2(128);
v_language char(2);
v_signature nvarchar2(128);
sql_str varchar2(8000);
cnt pls_integer;
begin
  for cur in (select r.id_registration as id_registration,
       r.date_testing as date_testing,
       helper.getFIO(r.id_person) as fio,
       helper.getNameRegion(r.id_region) as region,
       helper.getCodeBundle(r.id_bundle) as code,
       helper.countSuccessQuestionPTECT(r.id_registration) as pte,
       helper.countSuccessQuestionPTBe(r.id_registration)as ptbe,
       helper.countSuccessQuestionPTBT(r.id_registration) as ptbt,
       helper.countSuccessQuestion(r.id_registration) as score,
       b.min_point as min_point,
       case when substr(r.status,1,10)='Не пройден' then 'Нет'
            when substr(r.status,1,7)='Пройден' then 'Да'
            else '---' end as status,
       r.language as language,
       r.signature as signature
  from test_operator.registration r, test_operator.bundle b
  where r.id_bundle=b.id_bundle
  and r.signature is not null
  and   trunc(r.beg_time_testing,'dd')>=to_date('03.12.2013','dd.mm.yyyy')
  and   trunc(r.end_time_testing,'dd')<=to_date('20.01.2014','dd.mm.yyyy')
  order by r.date_testing, helper.getFIO(r.id_person))
  loop
    --dbms_output.put_line(cur.id_registration);
    
    insert into test_operator.super_report (id_registration, date_testing, fio, region_name, code, pte_result, ptbe_result, 
            ptbt_result, total_score_result, min_point, status, test_language, signature)
    values (cur.id_registration, cur.date_testing, cur.fio, cur.region, cur.code, cur.pte, cur.ptbe, cur.ptbt, cur.score, 
            cur.min_point, cur.status, cur.language, cur.signature);
    commit;
    
    cnt:=1;
    
    for cur2 in (select q.question as question, r.reply as reply, r.correctly as correctly
            from TEST_OPERATOR.questions_for_testing qt, TEST_OPERATOR.questions q, TEST_OPERATOR.replies r
            where qt.id_question = q.id_question
            and r.id_reply = qt.id_reply
            and qt.id_registration=cur.id_registration
            order by qt.id_theme, qt.order_num_question)
      loop
        sql_str:='update test_operator.super_report s set s.v'||cnt||'='''||cur2.question||''', s.a'||cnt||'='''||cur2.reply||''', s.c'||cnt||'='''||cur2.correctly||''' where s.id_registration='''||cur.id_registration||'''';
        execute immediate sql_str;
        
        cnt:=cnt+1;
      end loop;
  end loop;  
end fill_super_report;


Все на что хватило моих знаний - это 2 вложенных цикла, в первом забиваю данные людей, вторым - вытаскиваю запросом таблицу с результатом по каждому и формирую запрос на update с помощью execute immediate и по три поля "апдейтю".

Результат: запустил это чудо - выполняется уже часа три )) Как я понимаю, сейчас выполняются 3800 * 120 = 456 000 апдейтов и не могут никак завершиться.
Может есть более умное и адекватное решение забить эту таблицу?
25 янв 14, 14:16    [15467449]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54369
ТС, у тебя ВСЁ неправильно. Практически каждая строка.
Для такой таблицы при заполнении можно попробовать pivot-методы. На форуме их куча.
25 янв 14, 14:24    [15467463]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1774
AziAd
Т.е таблица с количеством столбцов 133 (понимаю, маразм, но так нужно заказчику)
Адекватный заказчик
25 янв 14, 14:32    [15467484]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
Elic
Member

Откуда:
Сообщений: 29976
AziAd
Нужно заполнить следующую таблицу с такими полями:
| Id-registration | date_testing | fio | region | code | pte | ptbe | ptbt | score | min_point | status | language | signature | v1 | a1 | c1 | v2 | a2| c2 |.............. |v120 | a120 | c120 |
Какому идиоту это нужно именно в виде таблицы?
AziAd
Первые 13 полей понятно, данные о тестируемом лице, дальше идут vN, aN, cN до v120, a120, c120. vN - вопрос, aN - ответ, cN - правильно или нет.
Т.е таблица с количеством столбцов 133 (понимаю, маразм, но так нужно заказчику).
Сколько классов окончил? 13+3*120=373
AziAd
select r.id_registration as id_registration,
       r.date_testing as date_testing,
       helper.getFIO(r.id_person) as fio,
       helper.getNameRegion(r.id_region) as region,
       helper.getCodeBundle(r.id_bundle) as code,
       helper.countSuccessQuestionPTECT(r.id_registration) as pte,
       helper.countSuccessQuestionPTBe(r.id_registration)as ptbe,
       helper.countSuccessQuestionPTBT(r.id_registration) as ptbt,
       helper.countSuccessQuestion(r.id_registration) as score,
       b.min_point as min_point,
       case when substr(r.status,1,10)='Не пройден' then 'Нет'
            when substr(r.status,1,7)='Пройден' then 'Да'
            else '---' end as status,
       r.language as language,
       r.signature as signature
  from test_operator.registration r, test_operator.bundle b
  where r.id_bundle=b.id_bundle
  and r.signature is not null
  and   trunc(r.beg_time_testing,'dd')>=to_date('03.12.2013','dd.mm.yyyy')
  and   trunc(r.end_time_testing,'dd')<=to_date('20.01.2014','dd.mm.yyyy')
  order by r.date_testing, helper.getFIO(r.id_person)
Ужас! Явист?
AziAd
Результат: запустил это чудо - выполняется уже часа три )) Как я понимаю, сейчас выполняются 3800 * 120 = 456 000 апдейтов и не могут никак завершиться.
Не мудрено. Говнодизайн * говнореализация.
AziAd
Может есть более умное и адекватное решение забить эту таблицу?
Именно "забить" заказчика и таблицу.
RTFM Pivoting Operations (FAQ)
25 янв 14, 14:34    [15467491]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
AziAd
Member

Откуда:
Сообщений: 20
andreymx
ТС, у тебя ВСЁ неправильно. Практически каждая строка.
Для такой таблицы при заполнении можно попробовать pivot-методы. На форуме их куча.

В самом начале натыкался на pivot unpivot, но в какой-то момент решил, что под мою задачу это не подходит. Попробую поискать по этому поводу инфу...

А как насчет моей процедуры, она когда нибудь завершиться или убить ее? ))
25 янв 14, 14:39    [15467506]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
semenar
Member

Откуда: Днепропетровск
Сообщений: 3308
Блог
AziAd, убей от греха.
25 янв 14, 16:01    [15467710]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
slx
Member

Откуда:
Сообщений: 98
AziAd,
AziAd
Может есть более умное и адекватное решение забить эту таблицу


Сделай копию таблицы и в ней update выполняй, ведь так и данные тестирования затереть можно
26 янв 14, 01:37    [15469830]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
t00kuk
Member

Откуда:
Сообщений: 1014
Elic
Ужас! Явист?


Я правильно понимаю, что ужас заключается в огромном количестве используемых в запросе хранимых функций (да ещё и в ORDER BY запихали)?
Хотелось бы в кратце понять основную претензию к данной реализации, а то вдруг сам захочу такое же сделать. У нас в системе такие вещи бывают встречаются...
27 янв 14, 09:23    [15472959]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54369
t00kuk
Elic
Ужас! Явист?


Я правильно понимаю, что ужас заключается в огромном количестве используемых в запросе хранимых функций (да ещё и в ORDER BY запихали)?
Хотелось бы в кратце понять основную претензию к данной реализации, а то вдруг сам захочу такое же сделать. У нас в системе такие вещи бывают встречаются...
Можно перенести целый ящик конфет. И можно переносить его по отдельной конфетке. Ты какой метод предпочитаешь?
27 янв 14, 09:47    [15473034]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
Elic
Member

Откуда:
Сообщений: 29976
t00kuk
Я правильно понимаю
Безмерно перезаинкапсулировано.
27 янв 14, 09:48    [15473036]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого количества значений в огромную таблицу  [new]
t00kuk
Member

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

Понял, спасибо.
27 янв 14, 09:51    [15473058]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить