0

this is my query it take more time to execute it can anyone make it faster!!! I think the not exists causes more time consuming but I don't know how to convert it to left outer join with more conditions I have changed it many times but the result was changed with it.

thanks in advance.

Mohammad Fawad Hbr
  • 1
  • 3
  • This is a long query and you have provided no details about the data or data structure, nor the business logic you're implementing. Also it looks like you have an embedded function call `PCK_TAX_PERIOD.TXPRD_DATE_FN()`: what does that do? You cannot expect us to rewrite a query knowing nothing about what it's supposed to be doing. Please read [this post on Oracle tuning](https://stackoverflow.com/a/34975420/146325) then **edit your question** to provide more of the information we need. – APC Mar 11 '19 at 07:41
  • @APC I have updated the query I need just to convert the not exist to join to increase the query execution speed!!! – Mohammad Fawad Hbr Mar 11 '19 at 09:02
  • You say in your question that you couldn't make the changes without affecting the result set. It's your data model and your business rules. So I think it's unlikely we can do it in complete ignorance. If you post some sample input data with the required output there is a chance someone might undertake the required surgery for you. – APC Mar 11 '19 at 09:24
  • your join condition `( tabi.tax_account_No=ta.tax_account_no and tt.tax_Type_No!=2) OR( tt.tax_Type_No=2)` looks strange - do you really want all tt.tax_type_no = 2 rows to be joined to every row, regardless of the tax_account_no? – Boneist Mar 11 '19 at 12:20
  • Also, you're not using anything from the establishment table; no columns, no predicates etc; you can remove that from the list of tables and joins. – Boneist Mar 11 '19 at 12:29
  • 1
    _"I think the not exists causes"_ There is no need to guess. Read up on `alter session set statistics_level=all;` and `DBMS_XPLAN.DISPLAY_CURSOR`. It will tell you EXACTLY which steps in your execution plan are requiring the most work. – Matthew McPeak Mar 11 '19 at 14:53

1 Answers1

0

As per basic tuning principle use exists or not exists if the query used inside not exists or exists has huge data.if it doesn't have huge data use IN or NOT IN instead

Also remove the distinct in SELECT DISTINCT t.tax_payer_no, taxestab.estab_no and use it in the CTE query and see how much time it makes

  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
psaraj12
  • 4,772
  • 2
  • 21
  • 30