Fri May 03 13:19:43 GMT 2024 From /weblog/database/vendor

redshift

All insights from Redshift academic paper: Amazon Redshift re-invented in 2022 - https://blog.det.life[..]redshift-heres-what-i-found-85c31a59fd19

(google search) (amazon search)

Thu Aug 11 13:31:24 GMT 2022 From /weblog/database/vendor

mysql

It is surprising for me that it is this easy - http://alan.blog-city.com/read/1204253.htm

MySQL Proxy for table partition - http://www.hscale.org/display/HSCALE/Home http://pero.blogs.aprilmayjune.org/ http://forge.mysql.com/wiki/MySQL_Proxy

NoSQL way to access data in MySQL - http://yoshinorimatsunobu.blogspot.com[..]0/10/using-mysql-as-nosql-story-for.html

The lock level of mysql - http://sgdev-blog.blogspot.com.au[..]ql-transaction-isolation-levels-and.html

MySQL rewriteBatchedStatements configuration property - https://vladmihalcea.com/mysql-rewritebatchedstatements

(google search) (amazon search)

Tue Oct 15 04:06:14 GMT 2019 From /weblog/database/vendor

misc

Few distributed SQL DB vendor information - http://www.infoq.com/news/2013/11/sql-newsql-nosql

Approximation DB - http://highscalability.com[..]e-200x-faster-without-having-to-pay.html

Discuss and compare difference technology of querying journey(log) data - http://www.infoq.com[..]on-log-and-data-storage-query-techniques

SAP HANA - https://www.google.com/search?q=sap+hana

(google search) (amazon search)

Sat Mar 31 08:14:54 GMT 2018 From /weblog/database/vendor

mssql

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.

Other that, statement.executeBatch() will throws exception: http://lab.msdn.microsoft.com[..]kid=e13a8009-7466-4803-ba19-6bfd4b5f8966

New function of SQL server 2014, In-Memory Optimization tables, suppose to be faster? - http://www.infoq.com/news/2013/09/MOT-Indexes MOT table also support native compiled query - http://www.infoq.com/news/2013/09/Compiled-Queries , and lock free write? http://www.infoq.com/news/2013/09/Lock-Free-Writes

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. - http://www.infoq.com/news/2013/09/Clustered-Columnstore

A tool from M$ to help sql server user to manage change of database: http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/ . Other similar product is DBGhost, also only work for SQL server http://www.innovartis.co.uk/home.aspx

There is a free version of SQL server 2005 call SQL server express, which just like MSDE http://www.microsoft.com/sql/editions/express/redistregister.mspx

Here is a comparison: http://www.sqlmag.com[..]le/ArticleID/49618/sql_server_49618.html , 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?

Import CSV to SQL server - http://www.sqlservercurry.com[..]oad-comma-delimited-file-csv-in-sql.html

Shriking DB cause performance problem... not sure how long this finding is hold - http://blog.sqlauthority.com[..]eases-fragmentation-reduces-performance/

http://beyondrelational.com[..]ver-storage-internals-part-1-basics.aspx

Get the metadata - http://gigi.nullneuron.net[..]-metadata-from-sql-server-catalog-views/ https://simpleprogrammer.com[..]icrosoft-sql-server-metadata-developers/

Date functions - http://www.essentialsql.com[..]roduction-to-sql-servers-date-functions/

Enhancement of 2016 - https://www.infoq.com/news/2016/06/SQl-Server-2016-Performance

Tool to rescue sql server - https://www.infoq.com/news/2017/02/SQL-First-Responder https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit http://firstresponderkit.org/

Prevent locking with NOLOCK / READPAST - https://www.techrepublic.com[..]-and-readpast-table-hints-in-sql-server/

(google search) (amazon search)

Tue Oct 18 17:41:16 GMT 2016 From /weblog/database/vendor

pgsql

http://www.javacodegeeks.com[..]0/introduction-to-postgresql-pljava.html

PostgreSQL schema is way fast and easy to change than MySQL, although maybe slower - https://www.pgrs.net[..]grating-from-mysql-to-postgresql-slides/

Subindexes that can save you space and make updates faster by reusing an existing multi-column index.
Expression indexes that speed up queries with WHERE clauses matching a function of the columns instead of raw values.
Partial indexes that are much smaller than a complete index because they don't contain data that the app doesn't care about.
http://blog.scoutapp.com[..]l-indexing-tricks-that-surprise-everyone

Experience sharing, stored proc is better? http://www.infoq.com/cn/news/2016/10/postgres-and-financial-arch

(google search) (amazon search)

Wed Jun 22 23:23:32 GMT 2016 From /weblog/database/vendor

oracle

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 - http://thedailywtf.com/forums/65743/ShowPost.aspx

Oracle SQL links - http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html

Pure java solution to full text search - http://www.infoq.com/news/2007/10/lucene-oracle http://marceloochoa.blogspot.com[..]nning-lucene-inside-your-oracle-jvm.html http://dbprism.cvs.sourceforge.net/dbprism/ojvm/

Oracle does allow definitions of user-defined types.Please see
http://download.oracle.com[..]er.111/b28286/sql_elements001.htm#i46376

User-defined types can then be used for column definitions. See e.g.
the fourth example given here
http://download.oracle.com[..].111/b28286/statements_7002.htm#i2062833

Oracle also allows creation of a table based on one user-defined type,
i.e. an "object table".
See here
http://download.oracle.com[..].111/b28286/statements_7002.htm#i2159410

User-defined types can be changed; see
http://download.oracle.com[..].111/b28286/statements_4002.htm#i2057828
Oracle even offers an option as to whether existing data in tables
based on previous type definition is converted or not; see
http://download.oracle.com[..].111/b28286/statements_4002.htm#i2079300
There are a couple of pages in the Object-Relational Developer's Guide explaining the consequences, briefly...

Note: all links above are to Oracle 11g documentation, but many, if not all, of these features have been available since 9i.

row base permission - http://technotes.towardsjob.com[..]/oracle/virtual-private-database-oracle/

How fetch size can affect performance - http://www.skill-guru.com[..]jdbc-performance-tuning-with-fetch-size/

Recycle bin for Oracle 10g - http://mahmoudoracle.blogspot.gr[..]ecycle-bin-in-database.html#.UNXprSUqthH

Oracle won't put null in index... how can we find the column with issue out? - http://blog.jooq.org[..]12/the-index-youve-added-is-useless-why/

You can have java like stacetrace at PLSQL - http://blog.jooq.org/2014/08/29/plsql-backtraces-for-debugging/

Need to be careful about using rownum to limit the result in oracle - http://www.monkeycancode.com[..]racle-limit-returned-records-with-rownum

(google search) (amazon search)

Fri Aug 28 10:19:53 GMT 2015 From /weblog/database/vendor

sybase

If you get following exception with Sybase ASE drive:

'System.Reflection.TargetInvocationException, Msg=Exception has been thrown by the target of an invocation.
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)
at ?.AseGetDriverVersion(StringBuilder A_0, Int32 A_1)
at Sybase.Data.AseClient.AseConnection.?()
at Sybase.Data.AseClient.AseConnection..cctor()
--- End of inner exception stack trace ---
at Sybase.Data.AseClient.AseConnection..ctor(String connectionString)

Please make sure the program can see sybdrvado115.dll, msvcp71.dll and msvcr71.dll in that same directory

http://www.elsasoft.org/SqlSpec.htm

===================

Query to check blocking at sybase:
select spid,suser_name(suid),cmd,status,blocked,time_blocked,physical_io from master..sysprocesses where blocked <> 0




(google search) (amazon search)

Wed Jan 30 09:24:04 GMT 2008 From /weblog/database/vendor

oracle cookbook

After default install of oracle at win32, here is the step of creating user:

1) sqlplus /noloh (enter sqlplus shell without login)
2) connect oracle/oracle as sysdba (super user connection)
3) CREATE USER CITY IDENTIFIED BY city DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE DEFAULT ACCOUNT UNLOCK;
4) GRANT "CONNECT" TO CITY;
5) GRANT "RESOURCE" TO CITY;
grant dba to CITY;

-- Grant/Revoke system privileges
grant create view to CITY;
grant unlimited tablespace to CITY;

imp CITY/city full=y file=c:\xxxxx.dmp

6) sqlplus city (try login)

About create table space

create tablespace TBL01 datafile 'H:\oracle\oradata\ora9\TBL01.dbf' size 50M extent management local segment space management auto;

Other useful views
1) sys.dba_tablespaces;
2) sys.dba_users
3) v$database
4) dba_data_files
5) v$datafile

Start and stop oracle batch
net start OracleCSService
net start OracleDBConsole[db name]
net start OracleOraDb10g_home1iSQL*Plus
net start OracleOraDb10g_home1TNSListener
net start OracleService[db name]

net stop OracleCSService
net stop OracleDBConsole[db name]
net stop OracleOraDb10g_home1iSQL*Plus
net stop OracleOraDb10g_home1TNSListener
net stop OracleService[db name]

An useful link: http://agents.csie.ntu.edu.tw/Projects/VF/docs/use_oracle.txt

How to install oracle in Redhat 9 - http://www.linuxdevcenter.com/lpt/a/4141

Check store procedure issues: select * from user_errors

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.

Show plsql function detail implementation: select text from user_source where name = MY_PROCEDURE order by line; - http://forums.oracle.com/forums/thread.jspa?threadID=515948

It is possible to get NullPointerException from oracle driver with IBM JDK... it look like IBM issue - http://www.ibm.com[..]s/forums/message.jspa?messageID=13980641

plsql result caching - http://gojko.net[..]ed-up-database-code-with-result-caching/

Oracle date foramt - http://www.oradev.com/oracle_date_format.jsp

(google search) (amazon search)