我现在手上有个项目,因为客户要实时更新的数据(live data access), 我给他们建的最终表格(destination tables)
都是用的view.
但是现在问题是,在客户那里,我们的view 是建立在他们的Materialized view之上,所以我们的表格查询速度非常慢
所以客户现在要我想办法把运行速度提高简单数据
每个view 至少有 70,000 到 80,000个数据, 而且是 distinct过后的
在toad 里面,查询一个view 大概要花上200秒到250秒
在我们的软件里面会更长所以我想让个位高手给我想想办法,出点主意,
看看能不能建立一个能够动态更新的表格出来
因为表格的速度比view快太多了
都是用的view.
但是现在问题是,在客户那里,我们的view 是建立在他们的Materialized view之上,所以我们的表格查询速度非常慢
所以客户现在要我想办法把运行速度提高简单数据
每个view 至少有 70,000 到 80,000个数据, 而且是 distinct过后的
在toad 里面,查询一个view 大概要花上200秒到250秒
在我们的软件里面会更长所以我想让个位高手给我想想办法,出点主意,
看看能不能建立一个能够动态更新的表格出来
因为表格的速度比view快太多了
大家帮我看一下,优化一下
我水平比较低,写的不好大家不要笑话--- TO CREATE A TEMPORARY VIEW FOR THE PURPOSE OF FINDING CURRENT AND ON-LEAVE EMPLOYEE ---CREATE OR REPLACE view LOCATION_ASSOCIATE_VW2
AS
SELECT distinct
a.ID,
a.LONG_DESCRIPTION,
a.CODE,
a.PARENT_ID,
a.OULEVEL,
a.OUPATH,
(SELECT COUNT(c.GLOBAL_ID) FROM MV_NAKISA_ASSOCIATE_DATA c WHERE (a.CODE = c.HEAD_COUNT_LOC AND c.EMPLOYMENT_STATUS = 'A' ) ) AS DIRECTCOUNT_A,
(SELECT COUNT(c.GLOBAL_ID) FROM MV_NAKISA_ASSOCIATE_DATA c WHERE (a.CODE = c.HEAD_COUNT_LOC AND C.EMPLOYMENT_STATUS='L') ) AS DIRECTCOUNT_L
FROM MV_NAKISA_LOCATION_PATH a
/
--TO CREATE A VIEW FOR HEADCOUNTING BY LOCATIONS
CREATE OR REPLACE view LOCATION_ASSOCIATE_VW
AS
SELECT distinct
a.ID,
a.LONG_DESCRIPTION,
a.CODE,
a.PARENT_ID,
a.OULEVEL,
a.OUPATH,
(d.DIRECTCOUNT_A + d.DIRECTCOUNT_L) AS DIRECTCOUNT
FROM MV_NAKISA_LOCATION_PATH a
LEFT OUTER JOIN LOCATION_ASSOCIATE_VW2 d
ON a.ID=d.ID
/
CREATE OR REPLACE VIEW COUNTRY_ASSOCIATE_VW
AS
SELECT distinct
a.ID, a.PARENT_ID,
SUM(c.DIRECTCOUNT) AS TOTALCOUNT
FROM LOCATION_ASSOCIATE_vw a, LOCATION_ASSOCIATE_vw c
where a.ID=c.PARENT_ID
group by a.PARENT_ID , a.ID
/CREATE OR REPLACE VIEW REGION_ASSOCIATE_VW
AS
SELECT distinct
a.ID, a.PARENT_ID,
SUM(c. TOTALCOUNT) AS REGIONCOUNT
FROM COUNTRY_ASSOCIATE_vw a, COUNTRY_ASSOCIATE_vw c
where a.ID=c.PARENT_ID
group by a.ID, a.PARENT_ID
/
CREATE OR REPLACE VIEW BD_ASSOCIATE_VW
AS
SELECT distinct
a.ID, a.PARENT_ID,
SUM(c. REGIONCOUNT) AS T_COUNT
FROM REGION_ASSOCIATE_vw a, REGION_ASSOCIATE_vw c
where a.ID=c.PARENT_ID
group by a.ID, a.PARENT_ID
/drop LOCATION_ASSOCIATE_VW2
/
2.optimize your view SQL, especially for view LOCATION_ASSOCIATE_VW2.
3.application tuning.