Home » Tag Archives: Databases (page 3)

Tag Archives: Databases

How to Quickly Enumerate Indexes in Oracle 11gR2

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

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

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?

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

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 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

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 »

Extending PostgreSQL: Complex Number Data Type

A few years ago I discussed creating user-defined types using PL/java. (Introduction to PostgreSQL PL/Java, part 4: User Defined Types) Today I follow up on the comment that we would use a standard C-language PostgreSQL extension in practice. What is an extension? A PostgreSQL extension is nothing more than bundled SQL statements and an optional native library. SQL-only extensions contain ...

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:

  • Spring Interview QnA
  • Multithreading & Concurrency QnA
  • JPA Minibook
  • JVM Troubleshooting Guide
  • Advanced Java
  • Java Interview QnA
  • Java Design Patterns