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..

shrinking tables to gain space in database (Oracle Sql Developer)

When you delete rows, the rows may not be physically deleted and you may not get the used space back.  The case is explained in the post: http://laurenthinoul.com/how-to-fix-ora-01654-unable-to-extend-index-in-tablespace/

You can run the following sql to compute statistics :

analyze table   compute statistics;


You can view the statistics as in the picture:

try this sql to shrink:

alter table  enable row movement;
alter table  shrink space;
alter table  disable row movement;

analyze table  compute statistics;
commit;


however, this may give the following error due to the reasons here: http://agstamy.blogspot.it/2009/02/ora-10631-when-trying-to-shrink-table.html

SQL Error: ORA-10631: SHRINK clause should not be specified for this object
 "SHRINK clause should not be specified for this object"
*Cause:    It is incorrect to issue shrink on the object
*Action:   Verify the object name and type and reissue the command

in this case, you may try by removing the indexes, shrinking the table and recreating the indexes.

btw, truncating the table gives you the space back!

a lucene tip

Retrieving large resultsets with lucene may degrade performance as in here: http://stackoverflow.com/questions/9868419/how-to-get-total-count-in-hibernate-full-text-search 

You can choose to show the first top k results, or you can paginate lucene as follows:

1 2 3 4 5 6 7 8 9 10 11

// can get the resultsize easily, so you know how many pages would be.. Integer resultSize = jpaQuery.getResultSize(); // I use 1000 as max results, but it depends.. just try to find the optimum. Integer pageCount = resultSize / MAX_RESULTS + 1; List<Object[]> rawQueryResultList = new ArrayList<>(); for(int page = 0; page < pageCount; page++) { jpaQuery.setFirstResult(page * MAX_RESULTS); jpaQuery.setMaxResults(MAX_RESULTS); rawQueryResultList.addAll(jpaQuery.getResultList()); }

book: learning cypher

Cypher is the query language for Neo4j graph databases. In fact, in my thesis about user profiling, i use cypher and  i am plannig to write a post about cypher after publishing my system for evaluation..
http://www.neo4j.org/learn/cypher  provides good information about cypher.
Moreover, i am planning to review the book on http://bit.ly/QZ5Alw ..
i recommend to try graph databases and cypher if you are dealing with connected-data problems..