Home » Tag Archives: Databases (page 2)

Tag Archives: Databases

Does PostgreSQL Have an ORA-01795-like Limit?

postgresql-logo

The Oracle database requires that no more than 1000 entries be used in a SQL IN portion of a WHERE clause and will throw an ORA-01795 error if that number is exceeded. If a value needs to be compared to more than 1000 values, approaches other than use of IN must be applied. I wondered if this limitation applies to ...

Read More »

A beginner’s guide to read and write skew phenomena

software-development-2-logo

Introduction In my article about ACID and database transactions, I introduced the three phenomena described by the SQL standard: dirty read non-repeatable read phantom read While these are good to differentiate the four isolation levels (Read Uncommitted, Read Committed, Repeatable Read and Serializable), in reality, there are more phenomena to take into consideration as well. The 1995 paper (A Critique ...

Read More »

How to Quickly Enumerate Indexes in Oracle 11gR2

software-development-2-logo

Do you want to know real quick what kind of indexes there are on any given table in your Oracle schema? Nothing simpler than that. Just run the following query: SELECT i.index_name, listagg(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_position) AS columns FROM all_indexes i JOIN all_ind_columns c ON i.index_name = c.index_name WHERE i.table_name = 'FILM_ACTOR' GROUP BY i.index_name The ...

Read More »

Single Quotes in Oracle Database Index Column Specification

software-development-2-logo

In my previous post, I mentioned that a downside of using double quotes to explicitly specify case in Oracle identifiers is the potential for being confused with the use of single quotes for string literals. Although I don’t personally think this is sufficient reason to avoid use of double quotes for identifiers in Oracle, it is worth being aware of ...

Read More »

Downsides of Mixed Identifiers When Porting Between Oracle and PostgreSQL Databases

postgresql-logo

Both the Oracle database and the PostgreSQL database use the presence or absence of double quotes to indicate case sensitive or case insensitive identifiers. Each of these databases allows identifiers to be named without quotes (generally case insensitive) or with double quotes (case sensitive). This blog post discusses some of the potential negative consequences of mixing quoted (or delimited) identifiers ...

Read More »

Benchmarking Aurora vs MySQL: Is Amazon’s New DB Really 5x Faster?

mysql-logo

Amazon Aurora: The next generation of hosted database services put to the test In this post we’re taking a closer look at Amazon Aurora to see how it fares against MySQL and if it delivers on the promise of a 5x performance boost. Recently reaching general availability, Aurora is Amazon’s home grown MySQL compatible database. Currently available on three AWS regions ...

Read More »

Setting PostgreSQL psql Variable Based Upon Query Result

postgresql-logo

When using PostgreSQL‘s psql command-line tool to interact with a PostgreSQL database via operator interaction or script, it is not uncommon to want to set psql variables based on the results of a particular query. While PostgreSQL’s procedural language PL/pgSQL supports approaches such as SELECT INTO and assignment (:=) to set PL/pgSQL variables based on a query result, these approaches ...

Read More »

Procedure-Like Functions in PostgreSQL PL/pgSQL

postgresql-logo

PostgreSQL does not support stored procedures in the sense that a database such as Oracle does, but it does support stored functions. In this post, I look at a few tactics that can make the use of a stored function in PostgreSQL (stored function and its calling code both written in PL/pgSQL) feel like using a stored procedure. These simple ...

Read More »

Mysql versions prior to 5.7 do not fully support two phase commit

mysql-logo

While doing some tests for the recently released generic JCA adapter which is capable of binding remote calls to microservices (as well as other things) into JTA transactions, I discovered a bug in Mysql 5.6 which has been around for nearly ten years. The test scenario was a crash after the “prepare” phase of the XA transaction, after both the ...

Read More »

Want to take your Java skills to the next level?

Grab our programming books for FREE!

Here are some of the eBooks you will get:

  • Advanced Java Guide
  • Java Design Patterns
  • JMeter Tutorial
  • Java 8 Features Tutorial
  • JUnit Tutorial
  • JSF Programming Cookbook
  • Java Concurrency Essentials