Using jOOQ’s Implicit Join From Within the JOIN .. ON Clause

Starting with jOOQ 3.11, type safe implicit JOIN have been made available, and they've been enhanced to be supported also in DML statements in jOOQ 3.17. Today, I'd like to focus on a somewhat weird but really powerful use-case for implicit JOIN, when joining additional tables from within an explicit JOIN's ON clause. The use … Continue reading Using jOOQ’s Implicit Join From Within the JOIN .. ON Clause

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 = … Continue reading How to Quickly Enumerate Indexes in Oracle 11gR2

Oracle Tip: v$sql Table or View does not Exist

If we want to analyse execution plans on the SQL console, we probably need to find a SQL_ID first, which we can the pass to the DBMS_XPLAN.DISPLAY_CURSOR function. One way to find this SQL_ID is by querying the v$sql table first, e.g.: SELECT last_active_time, sql_id, child_number, sql_text FROM v$sql WHERE upper(sql_fulltext) LIKE '%SOME_SQL_TEXT%' ORDER BY … Continue reading Oracle Tip: v$sql Table or View does not Exist