oracle left outer join 子查询,LEFT JOIN优化多个子查询

1.SQL1

OLD:

SELECT COUNT (1) num

FROM (  SELECT t1.*

FROM t_asset t1

WHERE     1 = 1

AND t1.TYPE = 0

AND (   t1.status IN (1,

10,

11,

12,

100)

OR (EXISTS

(SELECT b.resource_id

FROM t_asset_file b

WHERE     t1.resource_id = b.asset_code

AND t1.status IN (3, 4, 8)

AND b.status IN (1,

10,

11,

12))))

AND (   EXISTS

(SELECT 1

FROM t_asset_file a1

WHERE     t1.resource_id = a1.asset_code

AND (   a1.content_status = 1

OR a1.content_status = 4))

OR NOT EXISTS

(SELECT 1

FROM t_asset_file a1

WHERE t1.resource_id = a1.asset_code))

ORDER BY t1.create_time DESC, t1.resource_id) a;

优化方向: 合并多个t_asset_file子查询

优化方法: 使用left join 来代替子查询，把关联列放在g