#sql #oracle #plsql
Вопрос:
CREATE TABLE new_details_staging ( e_id NUMBER(10), e_name VARCHAR2(30), portal_desc VARCHAR2(50), risk_dec VARCHAR2(50), CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id) ); INSERT INTO new_details_staging VALUES (11, 'A', 'AA', 'High'); INSERT INTO new_details_staging VALUES (22, 'B', 'BB', 'Low'); CREATE TABLE lookup_ref ( ref_id NUMBER(10), ref_typ VARCHAR2(30), ref_typ_desc VARCHAR2(20), CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id) ); INSERT INTO lookup_ref VALUES (181, 'portal', 'AA'); INSERT INTO lookup_ref VALUES (182, 'portal', 'BB'); INSERT INTO lookup_ref VALUES (183, 'risk', 'High'); INSERT INTO lookup_ref VALUES (184, 'risk', 'Low'); CREATE TABLE new_details_main ( e_id NUMBER(10), e_name VARCHAR2(30), portal NUMBER(20), risk NUMBER(20), CONSTRAINT pk_new_details_main PRIMARY KEY (e_id) ); COMMIT; Моя попытка:
INSERT INTO new_details_main (e_id, e_name, portal,risk) SELECT n.e_id, n.e_name, (SELECT lr.ref_id FROM lookup_ref lr WHERE lr.ref_typ = 'portal' AND lr.ref_typ_desc = n.portal_desc), (SELECT lr.ref_id FROM lookup_ref lr WHERE lr.ref_typ = 'risk' AND lr.ref_typ_desc = n.risk_dec) FROM new_details_staging n; В настоящее время я вставляю несколько записей, и это дает точные результаты, но на самом деле существует более 40 тысяч записей, поэтому я считаю, что это также приведет к проблемам с производительностью. Есть ли какой-нибудь способ вставить записи быстрее, потому что я напишу процедуру для этой вставки? Есть ли какой-либо другой способ, с помощью которого я могу написать запрос на вставку внутри процедуры?
Комментарии:
1. Если вставка 40 тысяч строк (в реляционной базе данных нет «записей») занимает больше секунды или около того, значит, что-то не так. Либо вы делаете это исключительно плохо, либо на самом деле это намного больше, чем 40 тысяч строк. 40 тысяч строк для вставки-ЭТО НИЧЕГО.
2. Есть ли триггеры, индексы в новой таблице? Если вы запустите инструкцию select без вставки, как будет выглядеть ее производительность и план выполнения?
3. Итак,
new_details_mainу вас есть еще много записей ? как насчетnew_details_staging. Можете ли вы дать нам количество таблиц, чтобы дать нам представление ?4. Вставка, которая у вас есть, выглядит разумно. Это на самом деле медленно, или вы просто ищете указатели на случай, если это так?
5. @mathguy Спасибо, что изучили это. На самом деле в нем было 40 тысяч записей, но можно вставить столько строк за считанные секунды, как показал Коннор в примере с 80 тысячами строк. Так что у меня все хорошо с этим 🙂
Ответ №1:
На моем ноутбуке мы можем достаточно легко масштабировать ваш тест
SQLgt; SQLgt; CREATE TABLE new_details_staging 2 ( 3 e_id NUMBER(10), 4 e_name VARCHAR2(30), 5 portal_desc VARCHAR2(50), 6 risk_dec VARCHAR2(50), 7 CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id) 8 ); Table created. SQLgt; SQLgt; INSERT INTO new_details_staging VALUES (11, 'A', 'AA', 'High'); 1 row created. SQLgt; INSERT INTO new_details_staging VALUES (22, 'B', 'BB', 'Low'); 1 row created. SQLgt; SQLgt; insert into new_details_staging 2 select e_id*500000 rownum, e_name, portal_desc, risk_dec 3 from new_details_staging, 4 ( select 1 from dual connect by level lt;= 400000 ); 800000 rows created. SQLgt; SQLgt; CREATE TABLE lookup_ref 2 ( 3 ref_id NUMBER(10), 4 ref_typ VARCHAR2(30), 5 ref_typ_desc VARCHAR2(20), 6 CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id) 7 ); Table created. SQLgt; SQLgt; INSERT INTO lookup_ref VALUES (181, 'portal', 'AA'); 1 row created. SQLgt; INSERT INTO lookup_ref VALUES (182, 'portal', 'BB'); 1 row created. SQLgt; INSERT INTO lookup_ref VALUES (183, 'risk', 'High'); 1 row created. SQLgt; INSERT INTO lookup_ref VALUES (184, 'risk', 'Low'); 1 row created. SQLgt; SQLgt; CREATE TABLE new_details_main 2 ( 3 e_id NUMBER(10), 4 e_name VARCHAR2(30), 5 portal NUMBER(20), 6 risk NUMBER(20), 7 CONSTRAINT pk_new_details_main PRIMARY KEY (e_id) 8 ); Table created. SQLgt; SQLgt; set timing on SQLgt; INSERT INTO new_details_main (e_id, e_name, portal,risk) 2 SELECT 3 n.e_id, 4 n.e_name, 5 (SELECT lr.ref_id 6 FROM lookup_ref lr 7 WHERE lr.ref_typ = 'portal' 8 AND lr.ref_typ_desc = n.portal_desc), 9 (SELECT lr.ref_id 10 FROM lookup_ref lr 11 WHERE lr.ref_typ = 'risk' 12 AND lr.ref_typ_desc = n.risk_dec) 13 FROM 14 new_details_staging n; 800002 rows created. Elapsed: 00:00:02.97 SQLgt; SQLgt; Таким образом, около 3 секунд для 800 000 строк. Я думаю, с тобой все будет в порядке 🙂
Если поиск всегда верен и ключ сохранен, вы можете получить некоторую выгоду, перейдя в соединение, например
SQLgt; set timing on SQLgt; INSERT INTO new_details_main (e_id, e_name, portal,risk) 2 SELECT 3 n.e_id, 4 n.e_name, 5 lr.ref_id, 6 lr1.ref_id 7 FROM 8 new_details_staging n, 9 lookup_ref lr, 10 lookup_ref lr1 11 where lr.ref_typ = 'portal' 12 AND lr.ref_typ_desc = n.portal_desc 13 and lr1.ref_typ = 'risk' 14 AND lr1.ref_typ_desc = n.risk_dec ; 800002 rows created. Elapsed: 00:00:02.64 Комментарии:
1. Спасибо за это. Я исходил из предположения, что слияние происходит намного быстрее, чем вставка. Но, как вы показали на примере, который мне помог