Databases are at the core of virtually all modern information technology systems. Sometimes these databases are exposed, for example, as in data warehouse systems. The centrepiece of a system like that is a powerful industrial database such as Oracle, or Sybase, or Microsoft SQL Server, or something similar. The presence of a database is pretty obvious in that case – only these powerful databases can crunch the immense amounts of data processed by data warehouses.
Read more →As I have already shown you, implicit type conversion is one of the most dangerous features of Oracle SQL and PL/SQL. It is dangerous because it happens automatically without your knowledge and may lead to unpredictable results. These problems are most common when dealing with DATE conversions, but not limited to them.
For example, let’s have a look at this Stackoverflow.com question by James Collins. James had a problem, the following query was slow:
Read more →SYSDATE is one of the most commonly used Oracle functions. Indeed, whenever you need the current date or time, you just type SYSDATE and you’re done. However, sometimes it’s not all that simple. There are a few confusions associated with SYSDATE that are pretty common and, if not understood, can cause a lot of damage.
First of all, SYSDATE returns not just current date, but date and time combined. More precisely, the current date and time down to a second.
Read more →You only need a single look at Sydney Opera House to recognise that it is a work of art. Any masterpiece is like that – you don’t need to do a throughout examination of Mona Lisa’s smile to realise its beauty – you see it instantly. Perfection needs no explanation, it works on subconscious level.
The same applies to the software engineering too. Great code always looks good. It is always carefully formatted, indented and commented.
Read more →It’s a follow-up to the previous post.
As it turned out, implicit date conversions may also prevent Oracle from doing the partition pruning. For example, if you have a table INVOICES with a range partition on INVOICE_DATE field, then expression
SELECT ... WHERE invoice_date >= '01-MAR-09' AND invoice_date < '02-MAR-09' will not perform the partition pruning, whereas
SELECT ... WHERE invoice_date >= TO_DATE('01/03/2009', 'DD/MM/YYYY') AND invoice_date < TO_DATE('02/03/2009', 'DD/MM/YYYY') will.
Read more →