carfield.com.hk mssql.txt 2018-03-31T08:14:54Z 2018-03-31T08:14:54Z <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/>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> <a href="https://simpleprogrammer.com/microsoft-sql-server-metadata-developers/">https://simpleprogrammer.com[..]icrosoft-sql-server-metadata-developers/</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/>Prevent locking with NOLOCK / READPAST - <a href="https://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/">https://www.techrepublic.com[..]-and-readpast-table-hints-in-sql-server/</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> 2018-03-31T08:14:54Z 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 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 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 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 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 oracle cookbook.txt 2008-01-30T09:24:04Z 2008-01-30T09:24:04Z <br/>After default install of oracle at win32, here is the step of creating user:<br/><br/>1) sqlplus /noloh (enter sqlplus shell without login)<br/>2) connect oracle/oracle as sysdba (super user connection)<br/>3) CREATE USER CITY IDENTIFIED BY city DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE DEFAULT ACCOUNT UNLOCK;<br/>4) GRANT "CONNECT" TO CITY;<br/>5) GRANT "RESOURCE" TO CITY;<br/>grant dba to CITY;<br/><br/>-- Grant/Revoke system privileges <br/>grant create view to CITY;<br/>grant unlimited tablespace to CITY;<br/><br/>imp CITY/city full=y file=c:\xxxxx.dmp<br/><br/>6) sqlplus city (try login)<br/><br/>About create table space<br/><br/>create tablespace TBL01 datafile 'H:\oracle\oradata\ora9\TBL01.dbf' size 50M extent management local segment space management auto;<br/><br/>Other useful views<br/>1) sys.dba_tablespaces;<br/>2) sys.dba_users<br/>3) v$database<br/>4) dba_data_files<br/>5) v$datafile<br/><br/>Start and stop oracle batch <br/>net start OracleCSService<br/>net start OracleDBConsole[db name]<br/>net start OracleOraDb10g_home1iSQL*Plus<br/>net start OracleOraDb10g_home1TNSListener<br/>net start OracleService[db name]<br/><br/>net stop OracleCSService<br/>net stop OracleDBConsole[db name]<br/>net stop OracleOraDb10g_home1iSQL*Plus<br/>net stop OracleOraDb10g_home1TNSListener<br/>net stop OracleService[db name]<br/><br/>An useful link: <a href="http://agents.csie.ntu.edu.tw/Projects/VF/docs/use_oracle.txt">http://agents.csie.ntu.edu.tw/Projects/VF/docs/use_oracle.txt</a> <br/><br/>How to install oracle in Redhat 9 - <a href="http://www.linuxdevcenter.com/lpt/a/4141">http://www.linuxdevcenter.com/lpt/a/4141</a> <br/><br/>Check store procedure issues: select * from user_errors<br/><br/>If there is join like a.column1 = b.column2, and column1 in DATE datatype while column2 is VARCHAR2 datatype, oracle will auto-cast it in JDeveloper, but always return false if I call that stored procedure using JDBC.<br/><br/>Show plsql function detail implementation: select text from user_source where name = MY_PROCEDURE order by line; - <a href="http://forums.oracle.com/forums/thread.jspa?threadID=515948">http://forums.oracle.com/forums/thread.jspa?threadID=515948</a> <br/><br/>It is possible to get NullPointerException from oracle driver with IBM JDK... it look like IBM issue - <a href="http://www.ibm.com/developerworks/forums/message.jspa?messageID=13980641">http://www.ibm.com[..]s/forums/message.jspa?messageID=13980641</a> <br/><br/>plsql result caching - <a href="http://gojko.net/2007/11/02/speed-up-database-code-with-result-caching/">http://gojko.net[..]ed-up-database-code-with-result-caching/</a> <br/><br/>Oracle date foramt - <a href="http://www.oradev.com/oracle_date_format.jsp">http://www.oradev.com/oracle_date_format.jsp</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> 2008-01-30T09:24:04Z