download here
Sun Feb 07 22:41:23 HKT 2010
From /weblog/database
A pointer to various database style discussion, like [application database], [integration database], [silly database], [smart database] ... http://www.pervasivecode.com[..]n-of-stupid-databases-being-a-good-idea/ BDD for database design Requirements are behavior, Provides "ubiquitous language" for analysis, Acceptance criteria should be executable. Design constraints should be made into executable tests. - http://www.methodsandtools.com/archive/archive.php?id=78
(google search)
(amazon search)
Mon Dec 07 12:01:15 HKT 2009
From /weblog/database/performance
Note of this book - http://www.ocelot.ca/tuning.htm 1) Order of "and" and "or" is matter, if no cost-base optimizer where column1 = 'a' and column2 = 'b' -> where column2 = 'b' and column1 = 'a' if column2 'b' is less likely 2) <> is more expensive than = 3) Can force to use index if SELECT * FROM table WHERE indexed_column > 0 4) UPPER(column) might lose information, use LOWER is better 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') 6) 32bit integer is the fastest datatype for 32bit OS 7) column in (?, ?) is faster than column =? or column = ? for some database, and don't slow in any database 8) For aggregate functions, prevent using having, try to use fewer columns in group by and use subquery if possible 9) In fact, count(1) is not faster than count(*) 10) CAST before aggregate function: SELECT CAST(SUM(column) AS INT) -> SELECT SUM(CAST(column as INT)) 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 12) Information of Join vs Subqueries 13) Information of optimum column type (VARCHAR, TIMESTAMP, WORD size INT and CHAR(1) usually better), shift effect for page 14) prevent NULL for porting issue 15) Low level table physical information Some related links - http://www.techartifact.com[..]9/12/sql-optimization-tipsquestions.html
(google search)
(amazon search)
Thu Sep 24 22:12:22 HKT 2009
From /weblog/database/vendor
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/
(google search)
(amazon search)
Wed Jul 22 12:07:21 HKT 2009
From /weblog/database/objective
A presentation about Politics issues of using ORM persistence - http://www.softwaresummit.com[..]ers/SmithDonaldPoliticsOfPersistence.pdf A checklist of features of ORM library, which, I think, may be too detail to consider that much? http://madgeek.com/Articles/ORMapping/EN/mapping.htm Some problem of ORM nowadays - http://blogs.ittoolbox.com[..]e-ubiquitous-bastardization-of-orm-17100 This post rise a good point, if the ORM tool doesn't provide a simpler solution, why bother to use it? Just because many people using it? http://en.newinstance.it/2006/10/23/trimming-jdbc/ We have been using hibernate for while, actually it is good. But LGPL block us from using it anymore... Here is some other experience about this - http://shinetech.com/pages/viewpage.action?pageId=649 and the discussion of this article - http://www.theserverside.com[..]m/news/thread.tss?thread_id=41174#212825 Some interestig ORM packages without XML http://www.jenkov.com/mrpersister/introduction.tmpl http://www.simpleorm.org/sorm/whitepaper.html http://www.h2database.com/html/jaqu.html And there is one try to use javadoc doclet approach to make all those SQL documented at javadoc also, look like an interesting idea - http://voruta.sourceforge.net/ Advantage of using ORM, but are those really so important? http://ayende.com[..]oWriteYourOwnObjectRelationalMapper.aspx Good source about various pattern and test on ORM - http://blog.xebia.com/category/jpa/implementation-patterns/
(google search)
(amazon search)
Fri Mar 27 23:02:05 HKT 2009
From /weblog/database/performance
An idea of database sharding, basically replicate a unified version for operation required global access - http://itsfrosty.wordpress.com[..]com/2009/03/20/database-sharding-basics/
(google search)
(amazon search)
Fri Mar 27 11:49:44 HKT 2009
From /weblog/database/performance
Oracle can help us to denormalize tables according to query via materialized view, should help performance a lot, probably there is similar feature in other DBMS - http://www.oracle.com[..]ology/products/oracle9i/daily/jul05.html
(google search)
(amazon search)
Fri Feb 13 23:05:40 HKT 2009
From /weblog/database
Difference database sort null in timestamp column in difference way: MySQL: NULL values are presented first, or last if you specify DESC to sort in descending order. Exception: In MySQL 4.0.2 through 4.0.10, NULL values sort first regardless of sort order. - http://dev.mysql.com/doc/mysql/en/problems-with-null.html SQL server 2000, Sybase 12.5 and hsql 1.8.0: NULL values are presented first, or last if you specify DESC to sort in descending order. Oracle 8: NULL values are presented last, or first if you specify DESC to sort in descending order. A more detail default sorting behaviour: {null, -1, +1} // ASC sort for informix, microsoft, mysql and sybase {-1, +1, null} // ASC sort for IBM, Ingres, InterBase, Oracle and PostgreSQL {+1, -1, null} // ASC sort for informix, InterBase, PostgreSQL, microsoft, mysql and sybase {null, +1, -1} // ASC sort for IBM, Ingres and Oracle A discussion about using NULL or not - http://discuss.joelonsoftware.com/default.asp?design.4.403282 http://discuss.joelonsoftware.com/default.asp?design.4.494249 One solution - http://web.onetel.com[..]Manifesto/Missing-info-without-nulls.pdf Null, mean not application or no value? - http://www.livejournal.com/users/djberg96/47074.html Nothing neither equals, nor unequals, to NULL - http://fishbowl.pastiche.org/2009/01/30/why_null_is_special/
(google search)
(amazon search)
Wed Jan 07 23:18:29 HKT 2009
From /weblog/database
List of Database Refactorings Pramod has been working on defining the various refactorings that he's used in the approach defined in our paper on evolutionary database design Here we keep a list of the refactorings that he has defined so far. http://martinfowler.com/dbrefact/ There is some tools support, but not sure if that really work... - http://www.simple-talk.com[..]-database-development-with-sql-refactor/ Video of tutorial presentation, very nice - http://www.infoq.com/presentations/ambler-database-refactoring Article show how driven DB design with test - http://www.methodsandtools.com/archive/archive.php?id=78
(google search)
(amazon search)
Tue Mar 18 01:25:23 HKT 2008
From /weblog/database/objective
A nice article which talk about relation DB and OO. But I have an difference opinions, I think that advantage of relational database is the problem that OO like to prevent. The main advantage of relational database is the relatioal model make it easy to perform reporting. However, relation model is more difficult than hierarchical model (OO model) to enable encapsulation and polymorphism. Turn out, if we stand on OO programmer point of view, we will complaint that most RDBMS is in fact just a giant global variable; If we stand as DBA point of view, we will complaint other thing. Can we create something that optimize for both world? I don’t think so… http://jooto.com/blog/index.php/2005/11/17/databases-and-objects/ Update: a message also discuss about this: http://groups.yahoo.com/group/pragprog/message/5682 Update #2: A further analysis of ORM: http://www.codinghorror.com/blog/archives/000621.html
(google search)
(amazon search)
Wed Jan 30 17:24:04 HKT 2008
From /weblog/database/vendor
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)
Mon Dec 31 17:17:55 HKT 2007
From /weblog/database
Normally, we store object like 1,Smith,Joe,40000;2,Jones,Mary,50000;3,Johnson,Cathy,44000; However, some say store as 1,2,3;Smith,Jones,Johnson;Joe,Mary,Cathy;40000,50000,44000; is better for many cases... http://www.infoq.com/news/2007/09/row-vs-column-dbs
(google search)
(amazon search)
|