a simple tutorial about joins

Here is an excellent tutorial about basics of joins: https://www.youtube.com/watch?v=mhs4vwTSY-U

In fact, videos of Oresoft LWC seems very good for self training :  https://www.youtube.com/user/TheOresoft/playlists

Oracle performance tuning by Guy Harrison

I watched a great lecture on Oracle performance tuning by Guy Harrison and wanted to share it with my notes. The lecture can be watched here:
https://www.youtube.com/watch?v=Ah1xjCl6Axg   (part 1)
https://www.youtube.com/watch?v=f-C3nqaqND0 (part 2)
.. and my notes taken while watching:

• application --(sql)--> database server --(request blocks)--> server memory --(request blocks)--> IO subsytem

          Problems in one layer may be caused or cured in the higher layer.

          Therefore, tune db top-down 

• app code
• sql
• contention
• memory
• disk IO

• Make Oracle work smarter, not harder.. do not load it too much (donkey example in the lecture..)
• Nulls can not be indexed, so null equality queries work slower. Use some predefined value instead of nulls.
• Use concatenated index while searching multiple columns, reduces the IO requests drastically. but inserts get slower at the same time.
• Try to cache returned results, so do not go to database unless you need to.
• Use bind variables (otherwise -> parse overhead). use PreparedStatement in java.
• Minimize lock duration, use optimiztic locking
• Reduce network traffic by using array interface (setFetchSize) and stored procedure for complex sql s.
• help the optimizer (table statistics etc.)
• fix bad sql if optimizer makes suboptimal choice.
• Bad sql -> with highest CPU or IO consumption.
• V$SQL -> sys.dm_exec_requests
• order by, group by, join two tables without index -> performs a sort
• hash joins are more efficient than sort-merge
• not having enough memory to sort is bad..