How CASE expression manipulates with NULL in Oracle SQL

February 4, 2014 by Leave a comment 

My next post is an answer to the below question about Oracle SQL CASE expression and the way how it manipulates with NULL values compared to DECODE function. This topic in fact is very common in Oracle database developers’ area.

===============
Hi Kirill,

Could you please help me to clarify below mention doubt.

DECODE:-

select decode (null,null,’true’,’false’) as value from dual

OUTPUT:- TRUE

CASE:-

select case null when null then ‘true’ else  ‘false’ end as Case_Test from dual

OUTPUT :- FALSE

Why above mention queries are giving different output  and what is the difference between case and decode?

==============

CASE expression treats NULLs in Oracle database SQL a bit differently compared to DECODE. You have to understand that the Boolean expressions usually result in TRUE or FALSE , but NULLs introduce a third possible result which is : UNKNOWN . At the same time NULL is not the same as UNKNOWN. See an example below:

sum + NULL results in NULL (this is a Scalar expression.)
NULL = NULL results in UNKNOWN (this is a Boolean expression.)
sum < NULL results in UNKNOWN (this is a Boolean expression.)

Ok. Now let’s explore the Oracle SQL CASE expression itself.

Oracle database SQL CASE expression

Oracle database SQL – CASE expression

CASE introduces two ways of conditional expressions: Simple CASE and Searched CASE. Read more »

Oracle database housekeeping methods – Unix log rotation

January 10, 2014 by Leave a comment 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite the housekeeping or purging of those files got not simplified, unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other OS methods and tools to accomplish Oracle database housekeeping.

This time I share a method of rotating Oracle database log files using Unix logrotate mechanism. The below example will rotate the Oracle alert.log and listener.log files in the following way:

– Rotation is based on the file size (you can use “M” or “k” for Mb and Kb)
– Unix Logrotate utility creates a copy of the original log file, then truncates it
– The rotated files will be compressed with Unix gzip utility with the following name <logfile>-yyyymmdd.gz
– Only recent 20 rotated log files are kept
– Rotation frequency is default = weekly
– No errors generated if the log file is missing
Read more »

Oracle 11g files housekeeping methods

January 3, 2014 by 2 Comments 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. However housekeeping or purging of those files got not simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files Oracle database generates in ADR. Thus, you should use the other OS tools to accomplish the same. Below I list common Oracle files that must be cleaned periodically with associated housekeeping methods I usually use on Linux servers.

Logs rotation

– Linux log rotate mechanism used
– alert.log, listener.log, backup logs and any other log files affected
– Files are purged and zipped for archiving purpose

Read more about Oracle database housekeeping using Unix logrotate mechanism.
Read more »

Oracle Exadata X4 database machine – software insights

December 6, 2013 by Leave a comment 

The newest Oracle Exadata Database Machine X4 is available for ordering! After I reviewed in my last article some hardware insights of Oracle Exadata X4, let’s talk about the software options, improvements and new features that will be available soon in the new Exadata.

If you order an Oracle Exadat X4 system you can choose between two Oracle database versions: 11g or 12c.  The relevant database version will be pre-installed by Oracle Advanced Customer Support (ACS) Services with additional Grid infrastructure software including Oracle ASM and RAC. Prerequisite is that you have enough database and RAC licenses.

Oracle Exadata X4 Machine will be delivered with the new Exadata software version 11.2.3.3.0 that provides the following major capabilities:

– Automatic Flash compression on X3 and X4 systems
– Improved support for consolidation of multiple databases
– Many management improvements
– Many robustness and availability improvements

See below some major new Exadata software featurues that are going to be incorporated to the new X4 machines.
Read more »

Oracle Exadata X4 database machine – hardware insights

November 30, 2013 by Leave a comment 

The last version of Oracle Exadata Database Machine X3 was announced shortly before the Oracle Open World 2012 Conference. It was great to see at that time more power for the same price, 1/8 rack option and the new Platinum Services on the top. General availability date for the new Oracle Exadata X4-2 is not finally communicated at the moment but I guess it will be released already early in 2014. Below I give some insights of the expected hardware changes  compared to Exadata X3.

As with the previous version Exadata X4 will provide large performance and capacity increases with no changes in Exadata Hardware or Software Prices. Increased CPU cores may require more database licenses though.

Hardware changes in Oracle Exadata X4-2 Database Server

– 50% More Database Cores (2 Twelve-Core Intel® Xeon® E5-2697 v2 Processors x 2.7GHz)
Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email