In VS or of Oracle, Which Faster

IN vs OR of Oracle, which faster?

IN is preferable to OR -- OR is a notoriously bad performer, and can cause other issues that would require using parenthesis in complex queries.

Better option than either IN or OR, is to join to a table containing the values you want (or don't want). This table for comparison can be derived, temporary, or already existing in your schema.

In clause versus OR clause performance wise

the query doesn't matter much in case of 3 value checking only.

Oracle will re-write the query anyways to match the best option available.

in case there were more values and that too dynamic then the in clause or inner join could have been better.

its best to leave the query as it is currently

Why ' in ' is so much faster than ' = ' in SQL Select?

The "IN" means "there might be more that one row returned in this subquery, please check them all" whereas "=" means "there will be only one line returned from subquery" otherwise it would be an error.

Having that info the optimizer build different query plans. For "="-query it executes subquery first and then filters the custInfo table out.

For the "IN" query optimizer performs a join operation as if you've written following query

SELECT *
  FROM custinfo cs
  JOIN customers c
    ON cs.idcust = c.cust_id
 WHERE c.id = 1230;

This is why execution time differs. It can take longer or not depending on you data selectivity, indexes, partitioning and so on

UPD. From the execution plans you've uploaded I see the following

1. For the "=" query:

1.1. It competely scans the MT_OPERATION_OUT table (FULL TABLE SCAN), captures the result
1.2. Then it accesess another table on remote DB, presumably scans it too (REMOTE)
1.3. Filters data it got from remote.

2. For the "IN" query:

2.1. It competely scans the MT_OPERATION_OUT table (FULL TABLE SCAN), captures the result
2.2. Sorts what it got on the previous step (SORT UNIQUE)
2.3. Then it accesess another table on remote DB, presumably scans it too (REMOTE)
2.4. Performs a join (NESTED LOOPS)

So to me it seems that for some reason the db needs more time to filter data from remote db that to join it using "nested loops" method.

Oracle select in loop vs select with in clause

Tom Kyte's mantra is:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL Statement, then do it in PL/SQL.
• If you cannot do it in PL/SQL, try a Java Stored Procedure.
• If you cannot do it in Java, do it in a C external procedure.
• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

I find it's a very usefull rule of thumb.

Any performance impact in Oracle for using LIKE 'string' vs = 'string'?

There is a clear difference when you use bind variables, which you should be using in Oracle for anything other than data warehousing or other bulk data operations.

Take the case of:

SELECT * FROM SOME_TABLE WHERE SOME_FIELD LIKE :b1

Oracle cannot know that the value of :b1 is '%some_value%', or 'some_value' etc. until execution time, so it will make an estimation of the cardinality of the result based on heuristics and come up with an appropriate plan that either may or may not be suitable for various values of :b, such as '%A','%', 'A' etc.

Similar issues can apply with an equality predicate but the range of cardinalities that might result is much more easily estimated based on column statistics or the presence of a unique constraint, for example.

So, personally I wouldn't start using LIKE as a replacement for =. The optimizer is pretty easy to fool sometimes.

Which SQL is faster? Select and then Update VS. Update with inner select

The second should be faster, because you don't have two round trips to the database.

It also allows multiple rows to be updated at the same time, in case there are multiple matches.

That said, I don't see why this trigger is necessary. You can just look up the style when querying the data. Storing redundant data -- particularly to a reference table -- seems like a bad idea.

BETWEEN clause versus = AND =

There is no performance difference between the two example queries because BETWEEN is simply a shorthand way of expressing an inclusive range comparison. When Oracle parses the BETWEEN condition it will automatically expand out into separate comparison clauses:

ex.

SELECT *  
  FROM table
 WHERE column BETWEEN :lower_bound AND :upper_bound  

...will automatically become:

SELECT *  
  FROM table
 WHERE :lower_bound <= column
   AND :upper_bound >= column

IN vs OR in the SQL WHERE clause

I assume you want to know the performance difference between the following:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

According to the manual for MySQL if the values are constant IN sorts the list and then uses a binary search. I would imagine that OR evaluates them one by one in no particular order. So IN is faster in some circumstances.

The best way to know is to profile both on your database with your specific data to see which is faster.

I tried both on a MySQL with 1000000 rows. When the column is indexed there is no discernable difference in performance - both are nearly instant. When the column is not indexed I got these results:

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

So in this case the method using OR is about 30% slower. Adding more terms makes the difference larger. Results may vary on other databases and on other data.

---

Related Topics