Subqueries are queries within a query – one SELECT nested within another. They can take the place of column and table references, helping you to formulate queries that otherwise would be more difficult or less efficient to express using join operations.
Following is an example of a subquery filling a position typically occupied by a column name or expression. The goal of the query is to list the number of products per subcategory.
SELECT ps.name, (SELECT COUNT(*) FROM Production.Product p WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID) AS sub_count FROM production.ProductSubcategory ps
The output looks as follows:
The topic of DevOps and and Agile are everywhere, but how often do you hear Source Control thrown into the mix? Not so much in public, but behind the closed doors of technical and development meetings when Agile is in place, it’s a common theme. When source control isn’t part of the combination, havoc ensues and a lot of DBAs working nights on production with broken hearts.
In an interview for the NoCOUG Journal (http://www.nocoug.org/Journal/NoCOUG_Journal_200608.pdf#page=4), Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”
Steven replied: “Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic.
Once upon a time there was a very experienced database administrator who accidentally dropped a 12 TB index from a 55 TB table. The question I had was “How did he fix it?”(read more)
It requires a vast amount of work to organize a technical conference and publish a printed journal every quarter. No sooner has a conference ended and a journal mailed than it is time to start work on the next conference and the next journal. NoCOUG has no funding from Oracle Corporation and a miniscule budget compared to the national and international user groups, but the NoCOUG volunteers have always managed to pull it off, quarter after quarter for 30 long years.(read more)
All issues since May 2001 (Vol. 15 No. 2)(read more)
A few (months/weeks/days/hours)* ago I saw a friend request on Facebook. I looked at their profile which indicated that they were kind-of in my technical arena and the profile picture made me think “Wow – that’s an attractive person!” and I was about to click on the accept button.
I only friend people on Facebook who I know. By that I mean I have either met in real life and liked or have had a LOT of contact with through social media and liked. People who, if they were delayed at Stansted airport at midnight and needed a place to sleep, I’d be happy to go pick them up and bring them home to stay in my spare room.
For quite some time we have been treated nicely by ASM when it comes to transient disk failures. Since 11.1 (if memory serves me right), transient failures won’t cause an ASM disk to be dropped immediately. This is good, because it can potentially save a lot of time! When a disk is dropped from an ASM disk, a rebalance operation is unavoidable. And there is more than a chance of another one following it, because ultimately you want to add the disk back to the disk group. Well, to be fair, this is only applicable for normal or high redundancy disk groups.
The feature I just described very briefly is referred to as ASM Fast Mirror Resync, and documented in the ASM Administrator’s Guide in section Administering Oracle ASM Disk Groups.