carfield.com.hk architecture.txt 2017-09-22T07:29:28Z 2017-09-22T07:29:28Z <br/>IT Hare: Ultimate DB Heresy: Single Modifying DB Connection. Part I. Performanc - <a href="http://highscalability.com/blog/2016/11/22/it-hare-ultimate-db-heresy-single-modifying-db-connection-pa.html">http://highscalability.com[..]y-single-modifying-db-connection-pa.html</a> <br/><br/>How yandex.metrica improve DB reading - <a href="http://highscalability.com/blog/2017/9/18/evolution-of-data-structures-in-yandexmetrica.html">http://highscalability.com[..]of-data-structures-in-yandexmetrica.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-09-22T07:29:28Z uuid.txt 2017-09-08T16:15:42Z 2017-09-08T16:15:42Z <br/><a href="https://segment.com/blog/a-brief-history-of-the-uuid/">https://segment.com/blog/a-brief-history-of-the-uuid/</a> <br/><a href="http://www.infoq.com/cn/articles/talk-about-the-history-of-uuid">http://www.infoq.com/cn/articles/talk-about-the-history-of-uuid</a> <br/><br/>If using uuid as PK is good idea? - <a href="https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439">https://tomharrisonjr.com[..]-as-primary-keys-be-careful-7b2aa3dcb439</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-09-08T16:15:42Z misc.txt 2017-08-21T07:40:53Z 2017-08-21T07:40:53Z <br/>How to do limit resultset correctly - <a href="http://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/">http://blog.jooq.org[..]ination-with-your-in-house-db-framework/</a> <br/><br/>Using function result as table - <a href="http://blog.jooq.org/2014/07/07/postgresqls-table-valued-functions/">http://blog.jooq.org[..]7/07/postgresqls-table-valued-functions/</a> <br/><br/>Sample of using pivot - <a href="http://blog.jooq.org/2014/08/05/are-you-using-sql-pivot-yet-you-should/">http://blog.jooq.org[..]/are-you-using-sql-pivot-yet-you-should/</a> and unpivot - <a href="http://blog.jooq.org/2016/01/18/impress-your-coworkers-by-using-sql-unpivot/">http://blog.jooq.org[..]ess-your-coworkers-by-using-sql-unpivot/</a> <br/><br/>Sample of using intersect - <a href="http://blog.jooq.org/2015/08/04/intersect-the-underestimated-two-way-in-predicate/">http://blog.jooq.org[..]the-underestimated-two-way-in-predicate/</a> <br/><br/>Using temp table, rownum, and CTE - <a href="http://javarevisited.blogspot.hk/2016/07/how-to-remove-duplicate-rows-from-table-SQL-database.html">http://javarevisited.blogspot.hk[..]licate-rows-from-table-SQL-database.html</a> <br/><br/><a href="http://www.sohamkamani.com/blog/2016/07/07/a-beginners-guide-to-sql/">http://www.sohamkamani.com[..]log/2016/07/07/a-beginners-guide-to-sql/</a> <br/><br/>Free books - <a href="http://www.java67.com/2017/08/5-free-sql-books-for-beginners-and-experienced-pdf-download.html">http://www.java67.com[..]inners-and-experienced-pdf-download.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-08-21T07:40:53Z transaction.txt 2017-08-06T01:37:23Z 2017-08-06T01:37:23Z <br/>Transaction, same as all the concept in computing, is an approach and trade-off - <a href="http://www.allthingsdistributed.com/2007/12/eventually_consistent.html">http://www.allthingsdistributed.com[..]d.com/2007/12/eventually_consistent.html</a> <br/><br/>Transaction file system - <a href="http://www.infoq.com/news/2008/01/file-systems-transactions">http://www.infoq.com/news/2008/01/file-systems-transactions</a> <a href="http://myjavatricks.com/jtfs.aspx">http://myjavatricks.com/jtfs.aspx</a> <br/><br/>Explanation about database isolation level - <a href="http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html">http://highscalability.com[..]-and-their-effects-on-performance-a.html</a> <a href="https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html">https://begriffs.com[..]08-01-practical-guide-sql-isolation.html</a> <br/><br/>A nice article explain about transaction process - <a href="http://www.theserverside.com/articles/article.tss?l=Nuts-and-Bolts-of-Transaction-Processing">http://www.theserverside.com[..]Nuts-and-Bolts-of-Transaction-Processing</a> <br/><br/><a href="http://vladmihalcea.com/2014/01/05/a-beginners-guide-to-acid-and-database-transactions/">http://vladmihalcea.com[..]guide-to-acid-and-database-transactions/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-08-06T01:37:23Z time series.txt 2017-07-26T02:57:03Z 2017-07-26T02:57:03Z <br/>Some information about Time Series:<br/><br/><a href="http://en.wikipedia.org/wiki/S-PLUS">http://en.wikipedia.org/wiki/S-PLUS</a> <br/><a href="http://download-west.oracle.com/docs/cd/A87860_01/doc/inter.817/index.htm">http://download-west.oracle.com[..]ocs/cd/A87860_01/doc/inter.817/index.htm</a> <br/><a href="http://www.sas.com/">http://www.sas.com/</a> <br/><a href="http://oracledmt.blogspot.com/2006/10/time-series-revisited.html">http://oracledmt.blogspot.com/2006/10/time-series-revisited.html</a> <br/><a href="http://oracledmt.blogspot.com/2006/01/time-series-forecasting-part-1_23.html">http://oracledmt.blogspot.com[..]1/time-series-forecasting-part-1_23.html</a> <br/><br/>Discussion of time in time series database - UTC or local - <a href="http://quant.stackexchange.com/questions/7229/time-in-time-series-database-utc-or-local">http://quant.stackexchange.com[..]ime-in-time-series-database-utc-or-local</a> <br/><br/>Market data:<br/><a href="http://cs.nyu.edu/cs/faculty/shasha/fintime.html">http://cs.nyu.edu/cs/faculty/shasha/fintime.html</a> <br/><br/>and a book:<br/><a href="http://sql-info.de/sql-notes/developing-time-oriented-database-applications-in-sql.html">http://sql-info.de[..]iented-database-applications-in-sql.html</a> <br/><a href="http://www.cs.arizona.edu/people/rts/tdbbook.pdf">http://www.cs.arizona.edu/people/rts/tdbbook.pdf</a> <br/><br/>Should we use DATE as key or INT as key? <a href="http://quant.stackexchange.com/questions/613/representing-time-series-implementation-level">http://quant.stackexchange.com[..]senting-time-series-implementation-level</a> <br/><br/>Collections of column-oriented stores - <a href="http://quant.stackexchange.com/questions/1392/usage-of-nosql-storage-in-finance">http://quant.stackexchange.com[..]s/1392/usage-of-nosql-storage-in-finance</a> <br/><br/>时序数据库深入浅出之存储篇 - <a href="http://www.infoq.com/cn/articles/storage-in-sequential-databases">http://www.infoq.com/cn/articles/storage-in-sequential-databases</a> <br/><br/>深入浅出时序数据库之压缩篇 - <a href="http://www.infoq.com/cn/articles/condense-in-sequential-databases">http://www.infoq.com/cn/articles/condense-in-sequential-databases</a> <br/><br/>pretreatment - <a href="http://www.infoq.com/cn/articles/pretreatment-in-sequential-databases">http://www.infoq.com[..]les/pretreatment-in-sequential-databases</a> <br/><br/>Why - <a href="http://www.infoq.com/cn/news/2017/07/Why-time-series-database">http://www.infoq.com/cn/news/2017/07/Why-time-series-database</a> <br/><br/>分级存储 - <a href="http://www.infoq.com/cn/articles/hierarchical-storage-of-sequential-databases">http://www.infoq.com[..]archical-storage-of-sequential-databases</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-07-26T02:57:03Z Note of SQL performance tuning.txt 2017-07-21T09:24:58Z 2017-07-21T09:24:58Z <br/>Note of this book - <a href="http://www.ocelot.ca/tuning.htm">http://www.ocelot.ca/tuning.htm</a> <br/><br/>1) Order of "and" and "or" is matter, if no cost-base optimizer<br/><br/>where column1 = 'a' and column2 = 'b' -> where column2 = 'b' and column1 = 'a' if column2 'b' is less likely<br/><br/>2) <> is more expensive than =<br/><br/>3) Can force to use index if SELECT * FROM table WHERE indexed_column > 0<br/><br/>4) UPPER(column) might lose information, use LOWER is better<br/><br/>5) WHERE column = 'WORD' or column = 'word' is always faster than WHERE LOWER(column) = 'word' , even faster version is test if database if case-insensitive: WHERE column = 'WORD' or ('WORD' <> 'word' AND column = 'word')<br/><br/>6) 32bit integer is the fastest datatype for 32bit OS<br/><br/>7) column in (?, ?) is faster than column =? or column = ? for some database, and don't slow in any database<br/><br/>8) For aggregate functions, prevent using having, try to use fewer columns in group by and use subquery if possible<br/><br/>9) In fact, count(1) is not faster than count(*)<br/><br/>10) CAST before aggregate function: SELECT CAST(SUM(column) AS INT) -> SELECT SUM(CAST(column as INT))<br/><br/>11) Use constant as much as possible, e.g.: select * from t1, t2 where t1.c1 = t2.c1 and t1.c1 = 1 -> select * from t1, t2 where t1.c1=1 andn t2.c1=1<br/><br/>12) Information of Join vs Subqueries<br/><br/>13) Information of optimum column type (VARCHAR, TIMESTAMP, WORD size INT and CHAR(1) usually better), shift effect for page<br/><br/>14) prevent NULL for porting issue<br/><br/>15) Low level table physical information<br/><br/>Some related links - <a href="http://www.techartifact.com/blogs/2009/12/sql-optimization-tipsquestions.html">http://www.techartifact.com[..]9/12/sql-optimization-tipsquestions.html</a> <br/><br/>16) Prevent database level transaction, prevent joining, prevent locking ( like auto increment key ), pretty good suggestion - <a href="http://www.aviransplace.com/2015/08/12/mysql-is-a-great-nosql/">http://www.aviransplace.com/2015/08/12/mysql-is-a-great-nosql/</a> <br/><br/>Few good tips, like avoid cursors - <a href="http://www.javaworld.com/article/3209906/application-development/21-rules-for-faster-sql-queries.html">http://www.javaworld.com[..]ent/21-rules-for-faster-sql-queries.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-07-21T09:24:58Z misc.txt 2017-02-22T07:10:51Z 2017-02-22T07:10:51Z <br/>Few distributed SQL DB vendor information - <a href="http://www.infoq.com/news/2013/11/sql-newsql-nosql">http://www.infoq.com/news/2013/11/sql-newsql-nosql</a> <br/><br/>Approximation DB - <a href="http://highscalability.com/blog/2016/11/28/how-to-make-your-database-200x-faster-without-having-to-pay.html">http://highscalability.com[..]e-200x-faster-without-having-to-pay.html</a> <br/><br/>Discuss and compare difference technology of querying journey(log) data - <a href="http://www.infoq.com/cn/articles/trillion-log-and-data-storage-query-techniques">http://www.infoq.com[..]on-log-and-data-storage-query-techniques</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-02-22T07:10:51Z mssql.txt 2017-02-16T02:04:23Z 2017-02-16T02:04:23Z <br/>SQL 2005 JDBC fix the resultset re-read throws exception problem. However, you need to use executeUpdate() for insert,update and delete instead of execute() for SQL 2005 JDBC, otherwise will throw exception on this.<br/><br/>Other that, statement.executeBatch() will throws exception: <a href="http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=e13a8009-7466-4803-ba19-6bfd4b5f8966">http://lab.msdn.microsoft.com[..]kid=e13a8009-7466-4803-ba19-6bfd4b5f8966</a> <br/><br/>Update of newer version - <a href="http://www.infoq.com/news/2012/03/T-SQL-2012">http://www.infoq.com/news/2012/03/T-SQL-2012</a> <br/><br/>New function of SQL server 2014, In-Memory Optimization tables, suppose to be faster? - <a href="http://www.infoq.com/news/2013/09/MOT-Indexes">http://www.infoq.com/news/2013/09/MOT-Indexes</a> MOT table also support native compiled query - <a href="http://www.infoq.com/news/2013/09/Compiled-Queries">http://www.infoq.com/news/2013/09/Compiled-Queries</a> , and lock free write? <a href="http://www.infoq.com/news/2013/09/Lock-Free-Writes">http://www.infoq.com/news/2013/09/Lock-Free-Writes</a> <br/><br/>From microsoft, Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size. - <a href="http://www.infoq.com/news/2013/09/Clustered-Columnstore">http://www.infoq.com/news/2013/09/Clustered-Columnstore</a> <br/><br/>A tool from M$ to help sql server user to manage change of database: <a href="http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/">http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/</a> . Other similar product is DBGhost, also only work for SQL server <a href="http://www.innovartis.co.uk/home.aspx">http://www.innovartis.co.uk/home.aspx</a> <br/><br/>There is a free version of SQL server 2005 call SQL server express, which just like MSDE <a href="http://www.microsoft.com/sql/editions/express/redistregister.mspx">http://www.microsoft.com/sql/editions/express/redistregister.mspx</a> <br/><br/>Here is a comparison: <a href="http://www.sqlmag.com/Article/ArticleID/49618/sql_server_49618.html">http://www.sqlmag.com[..]le/ArticleID/49618/sql_server_49618.html</a> , may be we can use that instead of MSDE 2005 and discoutinue support of SQL server 2000 so that we can test one thing less? <br/><br/>Import CSV to SQL server - <a href="http://www.sqlservercurry.com/2011/01/load-comma-delimited-file-csv-in-sql.html">http://www.sqlservercurry.com[..]oad-comma-delimited-file-csv-in-sql.html</a> <br/><br/>Shriking DB cause performance problem... not sure how long this finding is hold - <a href="http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/">http://blog.sqlauthority.com[..]eases-fragmentation-reduces-performance/</a> <br/><br/><a href="http://beyondrelational.com/blogs/livingforsqlserver/archive/2011/08/08/sql-server-storage-internals-part-1-basics.aspx">http://beyondrelational.com[..]ver-storage-internals-part-1-basics.aspx</a> <br/><br/>Get the metadata - <a href="http://gigi.nullneuron.net/gigilabs/retrieving-table-metadata-from-sql-server-catalog-views/">http://gigi.nullneuron.net[..]-metadata-from-sql-server-catalog-views/</a> <br/><br/>Date functions - <a href="http://www.essentialsql.com/introduction-to-sql-servers-date-functions/">http://www.essentialsql.com[..]roduction-to-sql-servers-date-functions/</a> <br/><br/>Enhancement of 2016 - <a href="https://www.infoq.com/news/2016/06/SQl-Server-2016-Performance">https://www.infoq.com/news/2016/06/SQl-Server-2016-Performance</a> <br/><br/>Tool to rescue sql server - <a href="https://www.infoq.com/news/2017/02/SQL-First-Responder">https://www.infoq.com/news/2017/02/SQL-First-Responder</a> <a href="https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit">https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit</a> <a href="http://firstresponderkit.org/">http://firstresponderkit.org/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-02-16T02:04:23Z database comparison.txt 2017-01-03T01:58:28Z 2017-01-03T01:58:28Z <br/>Wiki of common DBMS comparison - <a href="http://en.wikipedia.org/wiki/Comparison_of_SQL_database_management_systems">http://en.wikipedia.org[..]rison_of_SQL_database_management_systems</a> <br/><br/>Somebody comment that postgresql is less competency than MSSQL, see the comment<br/><a href="http://www.baubels.net/blog/?p=88">http://www.baubels.net/blog/?p=88</a> <br/><br/>Alternative DBMS - <a href="http://www.webresourcesdepot.com/25-alternative-open-source-databases-engines/">http://www.webresourcesdepot.com[..]ternative-open-source-databases-engines/</a> <br/><br/><a href="http://blog.jooq.org/2013/10/03/the-10-most-popular-db-engines-sql-and-nosql/">http://blog.jooq.org[..]0-most-popular-db-engines-sql-and-nosql/</a> <br/><br/><a href="http://www.xaprb.com/blog/2015/05/25/what-makes-a-solution-mature/">http://www.xaprb.com[..]2015/05/25/what-makes-a-solution-mature/</a> <br/><br/><a href="http://www.javacodegeeks.com/2015/07/mysql-vs-mongodb.html">http://www.javacodegeeks.com/2015/07/mysql-vs-mongodb.html</a> <br/><br/>Comparing major RDBMS - <a href="http://www.infoq.com/cn/articles/select-the-appropriate-rdmbs-for-the-oltp">http://www.infoq.com[..]elect-the-appropriate-rdmbs-for-the-oltp</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2017-01-03T01:58:28Z pgsql.txt 2016-10-18T17:41:16Z 2016-10-18T17:41:16Z <br/><a href="http://www.javacodegeeks.com/2012/10/introduction-to-postgresql-pljava.html">http://www.javacodegeeks.com[..]0/introduction-to-postgresql-pljava.html</a> <br/><br/>PostgreSQL schema is way fast and easy to change than MySQL, although maybe slower - <a href="https://www.pgrs.net/2011/03/25/migrating-from-mysql-to-postgresql-slides/">https://www.pgrs.net[..]grating-from-mysql-to-postgresql-slides/</a> <br/><br/> Subindexes that can save you space and make updates faster by reusing an existing multi-column index.<br/> Expression indexes that speed up queries with WHERE clauses matching a function of the columns instead of raw values.<br/> Partial indexes that are much smaller than a complete index because they don't contain data that the app doesn't care about.<br/><a href="http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-indexing-tricks-that-surprise-everyone">http://blog.scoutapp.com[..]l-indexing-tricks-that-surprise-everyone</a> <br/><br/>Experience sharing, stored proc is better? <a href="http://www.infoq.com/cn/news/2016/10/postgres-and-financial-arch">http://www.infoq.com/cn/news/2016/10/postgres-and-financial-arch</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2016-10-18T17:41:16Z reading.txt 2016-10-13T00:33:54Z 2016-10-13T00:33:54Z <br/>Berkeley CS286: Implementation of Database Systems, Fall 2014‎ > <a href="‎http://www.cs286.net/home/reading-list">‎http://www.cs286.net/home/reading-list</a> <br/><br/>Disambiguating Databases, explaining various aspects of difference datastore approach - <a href="http://queue.acm.org/detail.cfm?ref=rss&id=2696453">http://queue.acm.org/detail.cfm?ref=rss&id=2696453</a> <br/><br/>Basic - <a href="http://ithare.com/databases-101-acid-mvcc-vs-locks-transaction-isolation-levels-and-concurrency/">http://ithare.com[..]action-isolation-levels-and-concurrency/</a> <br/><br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2016-10-13T00:33:54Z oracle.txt 2016-06-22T23:23:32Z 2016-06-22T23:23:32Z <br/>Some comment saying that you cannot complain oracle for non-standard setup... However, should I break anything else just to install oracle? Anyway, may be a useful resource of installing oracle cluster - <a href="http://thedailywtf.com/forums/65743/ShowPost.aspx">http://thedailywtf.com/forums/65743/ShowPost.aspx</a> <br/><br/>Oracle SQL links - <a href="http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html">http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html</a> <br/><br/>Pure java solution to full text search - <a href="http://www.infoq.com/news/2007/10/lucene-oracle">http://www.infoq.com/news/2007/10/lucene-oracle</a> <a href="http://marceloochoa.blogspot.com/2007/09/running-lucene-inside-your-oracle-jvm.html">http://marceloochoa.blogspot.com[..]nning-lucene-inside-your-oracle-jvm.html</a> <a href="http://dbprism.cvs.sourceforge.net/dbprism/ojvm/">http://dbprism.cvs.sourceforge.net/dbprism/ojvm/</a> <br/><br/>Oracle does allow definitions of user-defined types.Please see <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#i46376">http://download.oracle.com[..]er.111/b28286/sql_elements001.htm#i46376</a> <br/><br/>User-defined types can then be used for column definitions. See e.g. <br/>the fourth example given here <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2062833">http://download.oracle.com[..].111/b28286/statements_7002.htm#i2062833</a> <br/><br/>Oracle also allows creation of a table based on one user-defined type, <br/>i.e. an "object table".<br/>See here <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2159410">http://download.oracle.com[..].111/b28286/statements_7002.htm#i2159410</a> <br/><br/>User-defined types can be changed; see <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4002.htm#i2057828">http://download.oracle.com[..].111/b28286/statements_4002.htm#i2057828</a> <br/>Oracle even offers an option as to whether existing data in tables <br/>based on previous type definition is converted or not; see <br/><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4002.htm#i2079300">http://download.oracle.com[..].111/b28286/statements_4002.htm#i2079300</a> <br/>There are a couple of pages in the Object-Relational Developer's Guide explaining the consequences, briefly...<br/><br/>Note: all links above are to Oracle 11g documentation, but many, if not all, of these features have been available since 9i.<br/><br/>row base permission - <a href="http://technotes.towardsjob.com/oracle/virtual-private-database-oracle/">http://technotes.towardsjob.com[..]/oracle/virtual-private-database-oracle/</a> <br/><br/>How fetch size can affect performance - <a href="http://www.skill-guru.com/blog/2010/07/25/jdbc-performance-tuning-with-fetch-size/">http://www.skill-guru.com[..]jdbc-performance-tuning-with-fetch-size/</a> <br/><br/>Recycle bin for Oracle 10g - <a href="http://mahmoudoracle.blogspot.gr/2012/11/recycle-bin-in-database.html#.UNXprSUqthH">http://mahmoudoracle.blogspot.gr[..]ecycle-bin-in-database.html#.UNXprSUqthH</a> <br/><br/>Oracle won't put null in index... how can we find the column with issue out? - <a href="http://blog.jooq.org/2014/05/12/the-index-youve-added-is-useless-why/">http://blog.jooq.org[..]12/the-index-youve-added-is-useless-why/</a> <br/><br/>You can have java like stacetrace at PLSQL - <a href="http://blog.jooq.org/2014/08/29/plsql-backtraces-for-debugging/">http://blog.jooq.org/2014/08/29/plsql-backtraces-for-debugging/</a> <br/><br/>Need to be careful about using rownum to limit the result in oracle - <a href="http://www.monkeycancode.com/oracle-limit-returned-records-with-rownum">http://www.monkeycancode.com[..]racle-limit-returned-records-with-rownum</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2016-06-22T23:23:32Z database style.txt 2016-06-06T08:09:19Z 2016-06-06T08:09:19Z <br/>A pointer to various database style discussion, like [application database], [integration database], [silly database], [smart database] ...<br/><br/><a href="http://www.pervasivecode.com/blog/2007/08/02/rails-and-the-notion-of-stupid-databases-being-a-good-idea/">http://www.pervasivecode.com[..]n-of-stupid-databases-being-a-good-idea/</a> <br/><br/>BDD for database design <br/><br/>Requirements are behavior,<br/>Provides "ubiquitous language" for analysis,<br/>Acceptance criteria should be executable.<br/>Design constraints should be made into executable tests.<br/><br/>- <a href="http://www.methodsandtools.com/archive/archive.php?id=78">http://www.methodsandtools.com/archive/archive.php?id=78</a> <br/><br/><a href="http://dublintech.blogspot.hk/2016/06/agile-databases.html">http://dublintech.blogspot.hk/2016/06/agile-databases.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2016-06-06T08:09:19Z JDBC performance tips.txt 2016-02-01T04:26:28Z 2016-02-01T04:26:28Z <br/>Some tips, like use of addBatch()<br/><br/><a href="http://rgarg.blogspot.com/2005/10/jdbc-performance-pointers.html">http://rgarg.blogspot.com/2005/10/jdbc-performance-pointers.html</a> <br/><a href="http://www.theserverside.com/articles/content/JDBCPerformance_PartIII/article.html">http://www.theserverside.com[..]ent/JDBCPerformance_PartIII/article.html</a> <br/><br/>Nice discussion about preparestatement caching<br/><br/><a href="http://www.theserverside.com/news/thread.tss?thread_id=27528">http://www.theserverside.com/news/thread.tss?thread_id=27528</a> <a href="http://www.ibm.com/developerworks/java/library/j-jdbcnew/">http://www.ibm.com/developerworks/java/library/j-jdbcnew/</a> <br/><br/><a href="http://blog.jooq.org/2014/05/26/yet-another-10-common-mistakes-java-developer-make-when-writing-sql-you-wont-believe-the-last-one/">http://blog.jooq.org[..]iting-sql-you-wont-believe-the-last-one/</a> <br/><br/>Statement.getWarnings() is expensive - <a href="https://plumbr.eu/blog/io/how-we-accidentally-doubled-our-jdbc-traffic-with-hibernate">https://plumbr.eu[..]-doubled-our-jdbc-traffic-with-hibernate</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2016-02-01T04:26:28Z grouping.txt 2016-01-22T03:39:27Z 2016-01-22T03:39:27Z <br/>There is way to drop the dummy aggregate function in group by - <a href="http://blog.jooq.org/2015/12/10/sql-group-by-and-functional-dependencies-a-very-useful-feature/">http://blog.jooq.org[..]onal-dependencies-a-very-useful-feature/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2016-01-22T03:39:27Z sybase.txt 2015-08-28T10:19:53Z 2015-08-28T10:19:53Z <br/>If you get following exception with Sybase ASE drive:<br/><br/>'System.Reflection.TargetInvocationException, Msg=Exception has been thrown by the target of an invocation.<br/>Exception details: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Sybase.Data.AseClient.AseConnection' threw an exception. ---> System.DllNotFoundException: Unable to load DLL 'sybdrvado115a.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)<br/> at ?.AseGetDriverVersion(StringBuilder A_0, Int32 A_1)<br/> at Sybase.Data.AseClient.AseConnection.?()<br/> at Sybase.Data.AseClient.AseConnection..cctor()<br/> --- End of inner exception stack trace ---<br/> at Sybase.Data.AseClient.AseConnection..ctor(String connectionString)<br/><br/>Please make sure the program can see sybdrvado115.dll, msvcp71.dll and msvcr71.dll in that same directory<br/><br/><a href="http://www.elsasoft.org/SqlSpec.htm">http://www.elsasoft.org/SqlSpec.htm</a> <br/><br/>===================<br/><br/>Query to check blocking at sybase: <br/>select spid,suser_name(suid),cmd,status,blocked,time_blocked,physical_io from master..sysprocesses where blocked <> 0<br/><br/><br/><br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2015-08-28T10:19:53Z index.txt 2015-07-28T07:43:43Z 2015-07-28T07:43:43Z <br/>Case study about how to find out suitable index - <a href="http://samsaffron.com/archive/2011/05/02/A+day+in+the+life+of+a+slow+page+at+Stack+Overflow#">http://samsaffron.com[..]e+life+of+a+slow+page+at+Stack+Overflow#</a> <br/><br/>A simple example of how to use EXPLAIN to find out the bottleneck of the query and add suitable index to suitable column - <a href="http://hackmysql.com/case4">http://hackmysql.com/case4</a> <br/><br/>Bitmap Index vs. B-tree Index: Which and When? - <a href="http://www.oracle.com/technology/pub/articles/sharma_indexes.html">http://www.oracle.com/technology/pub/articles/sharma_indexes.html</a> <a href="http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq/perf21c.htm">http://publib.boulder.ibm.com[..]/v5r3/index.jsp?topic=/rzajq/perf21c.htm</a> <br/><br/>script to check if index is missing at foreign key<br/><pre><br/>select table_name, constraint_name,<br/> cname1 || nvl2(cname2,','||cname2,null) ||<br/> nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||<br/> nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||<br/> nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)<br/> columns<br/> from ( select b.table_name,<br/> b.constraint_name,<br/> max(decode( position, 1, column_name, null )) cname1,<br/> max(decode( position, 2, column_name, null )) cname2,<br/> max(decode( position, 3, column_name, null )) cname3,<br/> max(decode( position, 4, column_name, null )) cname4,<br/> max(decode( position, 5, column_name, null )) cname5,<br/> max(decode( position, 6, column_name, null )) cname6,<br/> max(decode( position, 7, column_name, null )) cname7,<br/> max(decode( position, 8, column_name, null )) cname8,<br/> count(*) col_cnt<br/> from (select substr(table_name,1,30) table_name,<br/> substr(constraint_name,1,30) constraint_name,<br/> substr(column_name,1,30) column_name,<br/> position<br/> from sys.user_cons_columns ) a,<br/> sys.user_constraints b<br/> where a.constraint_name = b.constraint_name<br/> and b.constraint_type = 'R'<br/> group by b.table_name, b.constraint_name<br/> ) cons<br/> where col_cnt > ALL<br/> ( select count(*)<br/> from sys.user_ind_columns i<br/> where i.table_name = cons.table_name<br/> and i.column_name in (cname1, cname2, cname3, cname4,<br/> cname5, cname6, cname7, cname8 )<br/> and i.column_position <= cons.col_cnt<br/> group by i.index_name<br/> ) <br/></pre><br/><a href="http://chriswongdevblog.blogspot.com.au/2013/01/dont-forget-to-index-your-oracle.html">http://chriswongdevblog.blogspot.com.au[..]01/dont-forget-to-index-your-oracle.html</a> <br/><br/>Explain how postgres indexes work, look like combine index is not that useful - <a href="http://dublintech.blogspot.com.au/2015/07/postgres-indexes.html">http://dublintech.blogspot.com.au/2015/07/postgres-indexes.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2015-07-28T07:43:43Z data.txt 2015-07-05T00:25:32Z 2015-07-05T00:25:32Z <br/>The Data Analytics Handbook - <a href="https://www.teamleada.com/handbook">https://www.teamleada.com/handbook</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2015-07-05T00:25:32Z view.txt 2015-06-09T09:22:45Z 2015-06-09T09:22:45Z <br/><a href="http://blog.jooq.org/2015/06/02/what-exactly-are-sql-views/">http://blog.jooq.org/2015/06/02/what-exactly-are-sql-views/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2015-06-09T09:22:45Z column-db.txt 2015-05-09T08:11:37Z 2015-05-09T08:11:37Z <br/>Normally, we store object like<br/><br/> 1,Smith,Joe,40000;2,Jones,Mary,50000;3,Johnson,Cathy,44000;<br/><br/>However, some say store as <br/><br/> 1,2,3;Smith,Jones,Johnson;Joe,Mary,Cathy;40000,50000,44000;<br/><br/>is better for many cases...<br/><br/><a href="http://www.infoq.com/news/2007/09/row-vs-column-dbs">http://www.infoq.com/news/2007/09/row-vs-column-dbs</a> <br/><br/>ROW base DB are dead end? - <a href="http://slideshot.epfl.ch/play/suri_stonebraker">http://slideshot.epfl.ch/play/suri_stonebraker</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2015-05-09T08:11:37Z migration.txt 2014-07-02T07:41:26Z 2014-07-02T07:41:26Z <br/><a href="http://blog.jooq.org/2014/06/25/flyway-and-jooq-for-unbeatable-sql-development-productivity/">http://blog.jooq.org[..]unbeatable-sql-development-productivity/</a> <br/><br/>Ruby tool to support Database versioning, look like this can work independence from Ruby on Rails - <a href="http://www.google.com/search?hl=en&lr=&q=Ruby+Rails+Migrations&btnG=Search">http://www.google.com[..]&lr=&q=Ruby+Rails+Migrations&btnG=Search</a> <br/>An example coding with this tool - <a href="http://rails.aizatto.com/2007/05/27/activerecord-migrations-without-rails/">http://rails.aizatto.com[..]7/activerecord-migrations-without-rails/</a> <a href="http://wiki.rubyonrails.org/rails/pages/UnderstandingMigrations">http://wiki.rubyonrails.org/rails/pages/UnderstandingMigrations</a> <br/><br/>Links about Database versioning: <a href="http://www.cmcrossroads.com/cgi-bin/cmwiki/view/CM/CMandDatabases">http://www.cmcrossroads.com/cgi-bin/cmwiki/view/CM/CMandDatabases</a> <br/><br/>Eclipse plugin - <a href="http://dbcopy.sourceforge.net/en/dbcopy/index.html">http://dbcopy.sourceforge.net/en/dbcopy/index.html</a> but I cannot get it working, instead, <a href="http://dbcopyplugin.sourceforge.net/">http://dbcopyplugin.sourceforge.net/</a> work a lot better. However it is better to install using installation jars (and source) of SQuirreL, otherwise there may be version not map issue. <br/><br/>Another new tool, migrate4j - <a href="http://karussell.wordpress.com/2009/12/23/simple-database-migration-in-java/">http://karussell.wordpress.com[..]12/23/simple-database-migration-in-java/</a> <br/><br/>Comments of difference approach of DB deployment - <a href="https://www.simple-talk.com/sql/database-administration/database-deployment-challenges/">https://www.simple-talk.com[..]stration/database-deployment-challenges/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2014-07-02T07:41:26Z primary key.txt 2014-06-25T06:22:18Z 2014-06-25T06:22:18Z <br/>What GUID provide? <a href="http://jasegroup.com/do-you-really-need-a-guid-database-design/">http://jasegroup.com/do-you-really-need-a-guid-database-design/</a> <br/><br/>Why primary key are cluster key? <a href="http://www.thescripts.com/forum/thread81459.html">http://www.thescripts.com/forum/thread81459.html</a> <br/><br/>Management key yourself but not letting framework to do that for you - <a href="http://www.onjava.com/lpt/a/6718">http://www.onjava.com/lpt/a/6718</a> <br/><br/>Comparing compound key and single key - <a href="http://vladmihalcea.com/2014/06/20/database-primary-key-flavors/">http://vladmihalcea.com/2014/06/20/database-primary-key-flavors/</a> <br/><br/>Some online resource discuss about GUID:<br/><br/><a href="http://www.codinghorror.com/blog/archives/000817.html">http://www.codinghorror.com/blog/archives/000817.html</a> <br/><a href="http://www.codinghorror.com/blog/archives/000399.html">http://www.codinghorror.com/blog/archives/000399.html</a> <br/><a href="http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/6d61dbf80d6f0fb6?hl=en&lr=&ie=UTF-8&oe=UTF-8&rnum=14">http://groups.google.com[..]0fb6?hl=en&lr=&ie=UTF-8&oe=UTF-8&rnum=14</a> <a href="http://www.informit.com/articles/printerfriendly.asp?p=25862&rl=1">http://www.informit.com/articles/printerfriendly.asp?p=25862&rl=1</a> <br/><a href="http://krow.livejournal.com/497839.html">http://krow.livejournal.com/497839.html</a> <br/><a href="http://en.wikipedia.org/wiki/Globally_Unique_Identifier">http://en.wikipedia.org/wiki/Globally_Unique_Identifier</a> <br/><a href="http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html">http://databases.aspfaq.com[..]-should-i-choose-for-my-primary-key.html</a> <br/><a href="http://blog.maxindelicato.com/2008/12/how-to-organize-a-database-tables-keys-for-scalability.html">http://blog.maxindelicato.com[..]atabase-tables-keys-for-scalability.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2014-06-25T06:22:18Z Graph.txt 2014-06-18T03:28:50Z 2014-06-18T03:28:50Z <br/><a href="http://www.infoq.com/articles/data-modeling-graph-databases">http://www.infoq.com/articles/data-modeling-graph-databases</a> <br/><br/><a href="http://thinkaurelius.com/2012/03/22/understanding-the-world-using-tables-and-graphs/">http://thinkaurelius.com[..]nding-the-world-using-tables-and-graphs/</a> <br/><br/><a href="http://markorodriguez.com/2011/02/23/knowledge-representation-and-reasoning-with-graph-databases/">http://markorodriguez.com[..]tion-and-reasoning-with-graph-databases/</a> <br/><br/><a href="http://markorodriguez.com/2013/01/09/on-graph-computing/">http://markorodriguez.com/2013/01/09/on-graph-computing/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2014-06-18T03:28:50Z mysql.txt 2014-05-26T07:30:30Z 2014-05-26T07:30:30Z <br/>It is surprising for me that it is this easy - <a href="http://alan.blog-city.com/read/1204253.htm">http://alan.blog-city.com/read/1204253.htm</a> <br/><br/>MySQL Proxy for table partition - <a href="http://www.hscale.org/display/HSCALE/Home">http://www.hscale.org/display/HSCALE/Home</a> <a href="http://pero.blogs.aprilmayjune.org/">http://pero.blogs.aprilmayjune.org/</a> <a href="http://forge.mysql.com/wiki/MySQL_Proxy">http://forge.mysql.com/wiki/MySQL_Proxy</a> <br/><br/>NoSQL way to access data in MySQL - <a href="http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html">http://yoshinorimatsunobu.blogspot.com[..]0/10/using-mysql-as-nosql-story-for.html</a> <br/><br/>The lock level of mysql - <a href="http://sgdev-blog.blogspot.com.au/2014/05/mysql-transaction-isolation-levels-and.html">http://sgdev-blog.blogspot.com.au[..]ql-transaction-isolation-levels-and.html</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2014-05-26T07:30:30Z normalization.txt 2014-04-25T01:51:18Z 2014-04-25T01:51:18Z <br/>A discussion against(?) 100% normalization - <a href="http://www.codinghorror.com/blog/archives/001152.html">http://www.codinghorror.com/blog/archives/001152.html</a> <br/><br/>Short overview of normalization rules - <a href="http://www.datamodel.org/NormalizationRules.html">http://www.datamodel.org/NormalizationRules.html</a> <br/><br/><a href="https://www.simple-talk.com/sql/t-sql-programming/database-normalization-basics/">https://www.simple-talk.com[..]ogramming/database-normalization-basics/</a> <br/><br/><script type="text/javascript"><!--google_ad_client = "pub-9426659565807829";google_ad_slot = "9359905831";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script> 2014-04-25T01:51:18Z