How to stabilize complex execution plan which cannot be stabilized by SQL Plan Baseline or SQL Profile
Recently I’ve been called to help with production issue that stopped all POS (Point of Sales) for 2 hours, producing lost for the affected company. I’ve found relatively quickly problematic query, which looks at first very simple. This is the problematic SQL code: SELECT * FROM (SELECT DOCUMENT_DATE, DOCUMENT_TYPE, AMT_TOTAL, DOCUMENT_NUM, FISCALISED, REF_DOCUMENT_NUM, ORDER_TSF_NO, STATUS_LOOKUP, […]
How to index only rows of interest
This is a classical example of what you can achieve by combining several existing features and implemented in a new way, not described within Oracle docs. Here I’m using the fact that Oracle won’t add entry into the index structure if indexed column value is null, in combination with the function-based indexes functionality. I’ve created […]
Case versus good old Join
SQL language gain a lot with introduction of the “Case” statement. Many SQL programmers are using it, as it is more logical way to accomplish some task without even thinking that SQL language is much older then Case statement, and there is always another (more efficient) way to get the same result. In this post […]