526互联

oracle中insert用select方式插入慢的解决之一

发布时间 2023-11-15 10:08:41作者: 许孟

原理我没明白，但是可以解决插入过慢的问题。

原SQL

INSERT
	INTO
	LOGINSTATBYDEPTzsy (group_id,
	persons,
	loginTimes,
	loginPersons,
	datadate,
	TYPE)
SELECT
	tg.id ,
	p.persons,
	sum(lb.LOGINAMOUNT) sum1,
	count(DISTINCT tu.id) sum2,
	1,
	2
FROM
	userdaylogininfo lb,
	tuser tu ,
	tgroup tg,
	(
	SELECT
		tgr.id,
		count(*) persons,
		ROWNUM hh
	FROM
		tuser tu,
		tgroup tgr
	WHERE
		tu.groupids LIKE ',,9001,,9002,,' || tgr.id || '%'
		AND tgr.parentid = 9002
	GROUP BY
		tgr.id
) p
WHERE
	lb.us_id = tu.id
	AND tg.parentID = 9002
	AND tu.groupids LIKE ',,9001,,9002,,' || tg.id || '%'
	AND lb.LOGINDAY >= to_date('2012-03-01', 'yyyy-mm-dd')
	AND p.id = tg.id
	AND lb.LOGINDAY < to_date('2012-04-01', 'yyyy-mm-dd')
GROUP BY
	tg.id,
	p.persons;

修改后SQL

INSERT
	INTO
	LOGINSTATBYDEPTzsy (group_id,
	persons,
	loginTimes,
	loginPersons,
	datadate,
	TYPE)
SELECT
	tg.id ,
	p.persons,
	sum(lb.LOGINAMOUNT) sum1,
	count(DISTINCT tu.id) sum2,
	1,
	2
FROM
	userdaylogininfo lb,
	tuser tu ,
	tgroup tg,
	(
	SELECT
		id ,
		persons ,
		ROWNUM HH --在最内层查询加上这个字段 或 用子查询的方式在外层查询加上
	FROM
		(
		SELECT
			tgr.id,
			count(*) persons
		FROM
			tuser tu,
			tgroup tgr
		WHERE
			tu.groupids LIKE ',,9001,,9002,,' || tgr.id || '%'
			AND tgr.parentid = 9002
		GROUP BY
			tgr.id
) pp
) p
WHERE
	lb.us_id = tu.id
	AND tg.parentID = 9002
	AND tu.groupids LIKE ',,9001,,9002,,' || tg.id || '%'
	AND lb.LOGINDAY >= to_date('2012-03-01', 'yyyy-mm-dd')
	AND p.id = tg.id
	AND lb.LOGINDAY < to_date('2012-04-01', 'yyyy-mm-dd')
GROUP BY
	tg.id,
	p.persons;

 PS：前提该insert慢不是由于select查询慢导致