Oracle vs SQL Server Query Optimization

I posted this to the IHOC, but thought it was of general enough interest. So, here's a slightly tamer version of the original post.

One of the large differences between Oracle and SQL Server is query optimization. In SQL Server, you rarely (if ever) have to worry about it because the database, for the most part, does the optimization for you. In all of the SQL Development I've done, I've had to optimize only a single query. And even then, it was on a WTF of a system.

In Oracle, you're on your own. You need to know which columns are indexed and what type of join to use. No, not the INNER or OUTER. I mean the physical types: HASH, MERGE, NESTED, etc. Take for example this trivial query.

SELECT LOAN_NBR, LOAN_RGSTRTN_TS
FROM WHL_LOAN_TB
WHERE LOANSTATUS_CD='500'

The table has indexes on the LOAN_NBR and LOANSTATUS_CD columns, but not on the LOAN_GRSTRTN_TS column. So, here's what the execution plan will look like ...

Operation Object Name Rows Bytes Cost
SELECT STATEMENT   2 K 107041
   TABLE ACCESS FULL WHL_LOAN_TB 2 K 42 K 107041

It scans the entire table, with a cost of 107041. That works out to be like 10 minutes for that table.

You need to do specify your own indexes to use via joins or sub queries, like this:

SELECT L1.LOAN_NBR, L1.LOAN_RGSTRTN_TS
FROM WHL_LOAN_TB L1, WHL_LOAN_TB L2
WHERE L1.LOAN_NBR = L2.LOAN_NBR
   AND L2.LOANSTATUS_CD='500'

Note that I don't actually know how (or what) physical join type to use. But, take a look at the execution plan, and you see the difference.

Operation

 Object Name Rows Bytes Cost
SELECT STATEMENT   2 K 4344
_TABLE ACCESS BY INDEX ROWID WHL_LOAN_TB 1 15 2
__NESTED LOOPS   2 K 60 K 4344
___VIEW index$_join$_002 2 K 26 K 3857
____HASH JOIN   2 K 26 K
_____INDEX FAST FULL SCAN WHL_LOAN_IDX 2 K 26 K 515
_____INDEX FAST FULL SCAN WHL_LOAN_IDX01 2 K 26 K 515
___INDEX RANGE SCAN WHL_LOAN_IDX 1 2

Lightning fast in comparison. With a cost of 4344, you get a 2464% speed increase.

Now, do a similar query in SQL Server, and get the same results either way.

SELECT OrderId, ShipName
FROM Orders
WHERE CustomerID='VINET'

Again, OrderID and CustomerID are indexed, but ShipName is not. Here's what SQL Server does:

  • SELECT - 0%
    • BOOKMARK LOOKUP - 20%
      • INDEX SEEK - 80%

Differently formatted explain plans, but you get the idea.

2 Comments

  • Do you ever analyze your tables, alex? You might find that the cost based optimizer works _significantly_ better (and picks the plans you'd expect) when you do that.

  • Oracle's dyn. query optimizer is getting up to par with SqlServer's btw. I'm not sure on which ancient Oracle box you've tested it ;)



    Also, I'd like to remind you that the current fastest DB in the world, DB2 (according to the TPC-C list), doesn't have an optimizer on board as you know it from SqlServer, as it compiles all stored procedures for example to C code and compiles these into programs.

Comments have been disabled for this content.