The Multi-In List

Cool because it prevents you from writing a long series of “and”s and “or”s

The “Weird” Case Statement

Cool, because it prevents you from having to spell out each individual condition.

Updating “Inline View” Select Statements

Cool, because you can formulate a select statement to see the rows you want to update (or delete), then just tack on an update at the beginning and a set clause at the end, and you’re set.

Merge Statements

Cool because  using a merge may provide better performance than an update statement if you’re trying to use tables in one table to update/insert values into another table.  Not to mention the fact that you’re updating and/or inserting (and even potentially deleting!)  in a single statement as opposed to sending multiple statements (and generating multiple runtime buffers and having multiple parse trees and making multiple explain plans) for each of the statements, instead of just one statement.

Rollups

Cool because you don’t have to do a union all to get totals in the same query, so less typing and better explain plans than this:

Multi-Table Inserts

Cool because you can now do in a single statement what you took previously multiple, so less typing and better performance (only one full table scan) as opposed to this:

Soundex

Cool because it allows you to search by homonyms without worrying about the spelling.  So you can search for “Debby” and “Debbie” and “Debie” all at the same time.

=All, =Any/=Some

Cool, because it saves you typing.

Listagg()

Cool because it saves you lots of typing, performance, etc.  Yes, you can do what listagg() does without actually using the listagg() function…but it sucks (note that you can only do this on Oracle 11r2 and above, which was when the LISTAGG() function was actually created, so there’s never a point where you would use this).

 

Pivot/Unpivot

Cool because it saves you from typing out the condition for each column.

Also, unpivots can save a tremendous amount of typing and multiple table scans .

Which, would otherwise be a separate table scan for each different column that wanted to be captured: