Forum Stats

  • 3,875,419 Users
  • 2,266,914 Discussions
  • 7,912,203 Comments

Discussions

Order Of Operations in Where Clause?

User_ZR3PE
User_ZR3PE Member Posts: 20 Green Ribbon

I searched a web for order of operations in where clause but I encountered articles about order of operations in sql in general. I wonder order of operations in WHERE clause beacuse I have a very big two tables (around 2,1 billion rows for each table). I want to make them filter for DATE field after then filter them on common field. If my SQL query first try to filter each other on common field then filter for DATE field it would take unnecessary amount of time to complete to query. How can I prioritize DATE filtering over common field filtering?

SELECT *

FROM

 TABLE1,

 TABLE2

WHERE TABLE1.DATE >= TO_DATE('18.11.2022','DD.MM.YYYY')

  AND TABLE1.DATE < TO_DATE ('18.11.2022','DD.MM.YYYY') + 1

  AND TABLE2.DATE >= TO_DATE('18.11.2022','DD.MM.YYYY')

  AND TABLE2.DATE < TO_DATE ('18.11.2022','DD.MM.YYYY') + 1

  AND TABLE1.TRANSACTIONID=TABLE2.TRANSACTIONID ;

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,912 Black Diamond
    Answer ✓

    If you don't try to force execution in a specific way, the query optimizer will consider all the different orders, assign a score (a "cost") to each, and select the one it thinks is optimal. You can see what the optimizer thinks is best by looking at the execution plan it comes up with.

    Unless you have reason to believe the optimizer is making poor choices, you should leave it alone. Most humans can't do better than the optimizer.

    If the optimizer is, in fact, making poor choices (which is less often than people think - even when they do not like the optimizer's choices), it may be because you don't have current statistics on the two tables, or because the data is very skewed - in which case you should add histograms, rather than force a specific execution plan (which may be optimal today but become suboptimal tomorrow, after the data changes).

    What would really help is indexes - both on the ID column (in both tables) and on the date columns.

    User_ZR3PEBluShadow

Answers

  • mathguy
    mathguy Member Posts: 10,912 Black Diamond
    Answer ✓

    If you don't try to force execution in a specific way, the query optimizer will consider all the different orders, assign a score (a "cost") to each, and select the one it thinks is optimal. You can see what the optimizer thinks is best by looking at the execution plan it comes up with.

    Unless you have reason to believe the optimizer is making poor choices, you should leave it alone. Most humans can't do better than the optimizer.

    If the optimizer is, in fact, making poor choices (which is less often than people think - even when they do not like the optimizer's choices), it may be because you don't have current statistics on the two tables, or because the data is very skewed - in which case you should add histograms, rather than force a specific execution plan (which may be optimal today but become suboptimal tomorrow, after the data changes).

    What would really help is indexes - both on the ID column (in both tables) and on the date columns.

    User_ZR3PEBluShadow
  • User_ZR3PE
    User_ZR3PE Member Posts: 20 Green Ribbon

    Thanks I try making baby steps in SQL, I don't know the query optimizer but i will search it.

  • JonWat
    JonWat Member Posts: 559 Silver Badge

    If one of the main ways that you will query the data is on full days, then you might find it more efficient to set up your query like this:

    SELECT *
    FROM
     TABLE1 t1
    JOIN  TABLE2 t2 on t1.TRANSACTIONID=T2.TRANSACTIONID 
    WHERE trunc(T1.DATE) = TO_DATE('18.11.2022','DD.MM.YYYY')
      AND trunc(T2.DATE) = TO_DATE('18.11.2022','DD.MM.YYYY')
    

    and have an index on trunc(t1.date) and trunc(t2.date).

    User_ZR3PE
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,284 Bronze Trophy

    If you are looking for the hint ORDERED_PREDICATE

    SQL> select name, sql_feature from v$sql_hint where name like '%PREDICATE%';
    
    NAME                               SQL_FEATURE
    -----------------------------------------------------------
    ORDERED_PREDICATES                        QKSFM_CBO
    

    Then bear in mind that it has been deprecated by Oracle and I will not be surprised that it will be ignored.

    If you want to analyze the performance of your query I would advise you first to get its execution plan from memory and preferably with row source statistics (E-Rows, Starts, and A-Rows) so that you can have an idea about the accuracy of the statistics you have at the execution plan optimization moment. Don't try to use hints before you have clearly understood why your query is not performing as you would like it to be

    Best Regards

    Mohamed Houri

    User_ZR3PE