我有多个表一个键ACCOUNT_ID，我构建了多个视图来计算每个CUSTOMER的各种值。
现在我正在尝试遍历关系：
编辑：给定一个svc_mrt_id，account_id可以在CUS_SERVICE中识别，然后使用该account_id，可以检索各种统计信息。
我试过这个查询
SELECT * FROM (
SELECT *
FROM CUS_SERVICE s
LEFT JOIN CUSTOMER c ON s.account_id = c.account_id
LEFT JOIN V_STCLOSE_5_CURRENT_BALANCE cb ON cb.stat_account_id = s.account_id
LEFT JOIN V_STATUS_ALL sa ON sa.account_id = s.account_id
LEFT JOIN V_NEXT_POOL_DATE pd ON pd.pool_donor = s.account_id
) WHERE svc_mrt_id = ?;
Query plan:
SELECT (245709)
HASH JOIN (245078)
HASH JOIN (628)
NESTED LOOPS
TABLE CUS_SERVICE (1)
VIEW V_STATUS_ALL (626)
VIEW V_STATUS_ALL (626)
/* This one is the culprit, should be (4317) if pushed predicates */
VIEW V_STCLOSE_5_CURRENT_BALANCE (245078)
HASH JOIN(232729)
MAT_VIEW ACCESS(4)
VIEW (232670)
/* Not pushing this predicate further is completely wrong */
/* Filtering here will generate large intermediate result set for only one row*/
/* Should be pushed to base table to generate only that one row required */
FILTER PREDICATES t1.ACCOUNT_ID = s.ACCOUNT_ID
现在，当两个视图与表CUS_SERVICE连接在一起时，谓词仅推送到V_STATUS_ALL而不推送到V_STCLOSE_5_CURRENT_BALANCE
有谁知道是否有可能解决这个问题，或者有任何替代方案？
更多信息：
编辑： 2013/12/26 19：13 GMT+8经过进一步测试，我已经隔离了两个不兼容的视图。结果如下：
SELECT * FROM (
SELECT sa.account_id
FROM V_STCLOSE_5_CURRENT_BALANCE cb
LEFT JOIN V_STATUS_ALL sa ON sa.account_id = cb.stat_account_id
) WHERE account_id = ?;
/* COST = 4323 */
SELECT * FROM (
SELECT s.svc_mrt_id
FROM CUS_SERVICE s
LEFT JOIN V_STCLOSE_5_CURRENT_BALANCE cb ON cb.stat_account_id = s.account_id
) WHERE svc_mrt_id = ?;
/* COST = 4322, looks right */
SELECT * FROM (
SELECT s.svc_mrt_id
FROM CUS_SERVICE s
LEFT JOIN V_STATUS_ALL sa ON sa.account_id = s.account_id
) WHERE svc_mrt_id = ?;
/* COST ~ 2045 */
SELECT * FROM (
SELECT s.svc_mrt_id
FROM CUS_SERVICE s
LEFT JOIN V_STCLOSE_5_CURRENT_BALANCE cb ON cb.stat_account_id = s.account_id
LEFT JOIN V_STATUS_ALL sa ON sa.account_id = s.account_id
) WHERE svc_mrt_id = ?;
/* cost ~ 232729, something strange happened about this 3-Table join combination */
对于视图查询：
/*IV_STCLOSE_1_1_T*/
/*IV_STCLOSE_1_2_T*/
/*IV_STCLOSE_2_1_T*/
/*These are materialized view for the bill day related logic,
basically calculate the open and close dates for each customer each month,
and is only refreshed once per day*/
/*IV_STCLOSE_2_2_T*/
/*This filter some unnecessary, outdated transaction records*/
/*As each customer may have only 20/3M transaction records required for calculation,
the query must push select of account_id up to here to be most selective*/
SELECT ACCOUNT_ID, POINT_DATE, POINT
FROM history_point
WHERE point_date >= (
SELECT MIN(open_date)
FROM IV_STCLOSE_2_1_t
)
/*IV_STCLOSE_3_T*/
/*This sum points within each month*/
SELECT p0.account_id, p0.open_date, p0.close_date,
COALESCE(SUM(DECODE(SIGN(p.point),-1,p.point)),0) AS point_used,
COALESCE(SUM(DECODE(SIGN(p.point),1,p.point)),0) AS point_earned
FROM iv_stclose_2_1_t p0
LEFT JOIN iv_stclose_2_2_t p
ON p.account_id = p0.account_id
AND p.point_date >= p0.open_date
AND p.point_date < p0.close_date + INTERVAL '1' DAY
GROUP BY p0.account_id, p0.open_date, p0.close_date
/*IV_STCLOSE_4_T*/
/*This sums each month's point earned and used for the open and close balance*/
SELECT v.account_id AS STAT_ACCOUNT_ID, v.open_date, v.close_date,
v.close_balance + t1.open_balance AS open_balance,
t1.point_earned AS point_earn, t1.point_used,
v.close_balance + t1.open_balance + t1.point_earned + t1.point_used AS close_balance
FROM IV_STCLOSE_2_1_T v
LEFT JOIN (
SELECT v1.account_id, v1.open_date, v1.point_earned, v1.point_used,
COALESCE(sum(v2.point_used + v2.point_earned),0) AS OPEN_BALANCE
FROM IV_STCLOSE_3_T v1
LEFT JOIN IV_STCLOSE_3_T v2
ON v1.account_id = v2.account_id
AND v1.OPEN_DATE > v2.OPEN_DATE
GROUP BY v1.account_id, v1.open_date, v1.point_earned, v1.point_used
) t1
ON v.account_id = t1.account_id
AND v.OPEN_DATE = t1.OPEN_DATE
/*V_STCLOSE_5_CURRENT_BALANCE*/
/*This select the most recent balance*/
SELECT v.STAT_ACCOUNT_ID,v.OPEN_DATE,v.CLOSE_DATE,
v.OPEN_BALANCE,v.POINT_EARN,v.POINT_USED,v.CLOSE_BALANCE
FROM IV_STCLOSE_4_T v
WHERE MONTHS_BETWEEN(TRUNC(SYSDATE), v.OPEN_DATE) < 1
尝试添加索引提示，以便优化程序使用该特定索引。 您还可以尝试按表应连接的顺序列出表，然后添加/*+ 有序的 */提示。