Optimize JPA one to many with fetch

 N+1 We all know the famous N+1 problem in the ORM world where the generated sql is not that optimized that we have to get the child collections with N number of separate sql query which leads to serious performance problem if there are many levels of objects tree. So we can fix the problem…

My Postgresql snippets

Install psql To get the psql, I simply installed the postgresapp for OSX which bundles the psql . We can start the psql from its GUI, or added its bin to our PATH. The specific path is: /Applications/Postgres.app/Contents/Versions/latest/bin You can also get psql by installing postgresql one click installer. Connect to DB with psql psql -h DbUrl -p…

install ora2pg on mac osx

Recently we need to migrate our on prem oracle database into postgresql on aws RDS. Looks like ora2pg is a nice little tool to mitigate the problem though not perfect. I am running OS X 10.10.5 Yosemite. It already comes with perl v5.18.2 as well as cpan for installing packages. download/install Oracle client The official document…

oracle DBMS_STATS.GATHER_TABLE_STATS

The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to improving performance. DBMS_STATS package is concerned with optimizer statistics only. Given that Oracle sets automatic statistics collection of this kind on by default, DBMS_STATS package is intended for only specialized cases. For GATHER_TABLE_STATS Most enterprise databases, Oracle included, use a…

JPA hibernate @GeneratedValue with sequence on Oracle

When using Oracle, the JPA with hibernate implementation on the @GeneratedValue with sequence would be quite tricky. I am trying to make a summary here. The version used are JPA 2.1 and Hibernate 4.3.11.Final Default generator setting @GeneratedValue with strategy AUTO and default allocationSize(50) This will use the ‘org.hibernate.id.SequenceGenerator’ which will get a SEQ+1 value….

Oracle partition table

We have an oracle table which used to use store proc to populate from other oracle table. Now since our upstream data source migrated to Hive, we need to do it thru jdbc and persist with JPA. And we hit a problem with: ‘inserted partition key does not map to any partition’. Turns out that…

Hive JDBC Architecture

Part 1: What is the actual contract that Hive provides us with? Hive’s contract to users is defined in the HiveInterface class That is – thrift is a communication channel that hive uses to expose its main service : which is the translation of SQL commands into hadoop / mapreduce commands.  The ultimate class invoked…

How JDBC URLs get mapped to connections at runtime

Who cares? I recently found the need to mock out a JDBC url to experiment with a new way of testing sqoop without a hard dependency on a particular database installation.  In order to do this, you first need to understand how it is that, at runtime, JDBC drivers connection call URLs get routed to implementation…

Spring transaction isolation level

Introduction Transaction isolation level is a concept that is not exclusive to the Spring framework. It is applied to transactions in general and is directly related with the ACID transaction properties. Isolation level defines how the changes made to some data repository by one transaction affect other simultaneous concurrent transactions, and also how and when…

MongoDB Queries and Projections

Methods, Queries, and Projections In any MongoDB call—often referred to as a “MongoDB query”—we have four parts: Collection Name – The set of documents we’d like to look at. Collection Method – The MongoDB method that we’ll use to look for documents. Query – A document describing what we’re looking for in our collection. Projection…

nosql modeling notes

When designing a MongoDB schema, you need to start with a question that you’d never consider when using SQL: what is the cardinality of the relationship? Put less formally: you need to characterize your “One-to-N” relationship with a bit more nuance: is it “one-to-few”, “one-to-many”, or “one-to-squillions”? Depending on which one it is, you’d use…

SQL语句的解析过程

SQL语句的解析顺序 简单的说一个sql语句是按照如下的顺序解析的: 1. FROM FROM后面的表标识了这条语句要查询的数据源。和一些子句如,(1-J1)笛卡尔积,(1-J2)ON过滤,(1-J3)添加外部列,所要应用的对象。FROM过程之后会生成一个虚拟表VT1。 (1-J1)笛卡尔积 这个步骤会计算两个相关联表的笛卡尔积(CROSS JOIN) ,生成虚拟表VT1-J1。 (1-J2)ON过滤 这个步骤基于虚拟表VT1-J1这一个虚拟表进行过滤,过滤出所有满足ON 谓词条件的列,生成虚拟表VT1-J2。 (1-J3)添加外部行  如果使用了外连接,保留表中的不符合ON条件的列也会被加入到VT1-J2中,作为外部行,生成虚拟表VT1-J3。 2. WHERE 对VT1过程中生成的临时表进行过滤,满足where子句的列被插入到VT2表中。 3. GROUP BY 这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。 4. HAVING 这个子句对VT3表中的不同的组进行过滤,满足HAVING条件的子句被加入到VT4表中。 5. SELECT 这个子句对SELECT子句中的元素进行处理,生成VT5表。 (5-1)计算表达式 计算SELECT 子句中的表达式,生成VT5-1 (5-2)DISTINCT 寻找VT5-1中的重复列,并删掉,生成VT5-2 (5-3)TOP 从ORDER BY子句定义的结果中,筛选出符合条件的列。生成VT5-3表 ORDER BY 从VT5-3中的表中,根据ORDER BY 子句的条件对结果进行排序,生成VC6表。 客户,订单的查询例子 首先创建一个Customers表,插入如下数据: customerid city FISSA Madrid FRNDO Madrid KRLOS Madrid MRPHS Zion 创建一个Orders表,插入如下数据: orderid…