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
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
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.
|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
Again, OrderID and CustomerID are indexed, but ShipName is not. Here's what SQL Server does:
Differently formatted explain plans, but you get the idea.