由于oracle中不存在查询，查询性能较慢

Question

这是我的查询，它需要更多的时间来执行它，有谁能让它更快！我认为not exists会导致更多的时间消耗，但我不知道如何将其转换为条件更多的左外部连接，我已经多次更改它，但结果也随之改变。

提前谢谢。

select count(1) from(
        SELECT distinct   t.tax_payer_no,taxestab.estab_no 
          from tax_period tp, 
               imposition_base impb ,tax_acct_base_imp tabi, tax_account ta, tax_payer t , tax_form tf,tax_Type tt, tax_estab taxestab, establishment est
         where
           t.tax_payer_no=ta.tax_payer_no
           and tp.form_no=tf.form_NO
           and tf.tax_Type_no=tt.tax_Type_No
           and ta.tax_Type_no=tt.tax_type_no

        and (( tabi.tax_account_No=ta.tax_account_no and tt.tax_Type_No!=2) OR( tt.tax_Type_No=2))
        and impb.imposition_base_no(+) = tp.imposition_base_no
           AND impb.imposition_base_no = tabi.imposition_base_no(+)
           and ta.tax_Account_No=taxestab.tax_account_no(+)
           and taxestab.estab_no=est.estab_no(+)
        and ta.tax_account_no={0}
        and ta.close_date is null


        and not exists ( SELECT 1  
                                      FROM  assessment ass
                                      WHERE tax_account_no = ta.tax_account_No
                                      AND ass.tax_period_no= tp.tax_period_no                             
                                     AND (ass.estab_no = taxestab.estab_no OR taxestab.estab_no IS NULL)
                                   )

        and not exists (SELECT 1 FROM document doc
                         WHERE doc.tax_period_no =tp.tax_period_no
                         AND doc.tax_type_no = ta.tax_type_no
                         AND doc.tax_payer_no = t.tax_payer_no
                         AND doc.tax_centre_no = t.tax_centre_no
                         AND doc.doc_type_no = 1 
                         AND doc.doc_state_no <> 3 

                         AND (doc.estab_no = taxestab.estab_no OR taxestab.estab_no IS NULL))

Answer 1

根据基本调优原则，使用exists或not exists如果内部使用的查询not exists或exists具有巨大的data.if，则使用IN或NOT IN

还要删除SELECT distinct t.tax_payer_no，taxestab.estab_no中的DISTINCT，并在CTE查询中使用它，看看它花费了多少时间

  with data as (
    SELECT t.tax_payer_no tax_payer_no,taxestab.estab_no estab_no.. rest of your query)
  select count(1),tax_payer_no,estab_no from data
     group by tax_payer_no,estab_no