OT: Update on the SQL mini-challenge
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 17 Aug 2014 14:01:25 -0700
In case anybody has nothing better to do on a Sunday afternoon then read about
SQL, read Kim Berg Hansen's latest attempts to solve the SQL mini-challenge
published in the latest NoCOUG Journal. http://nocoug.wordpress.com/index/
The challenge was to rewrite the following query without testing for existence
using EXISTS, IN, ANY, ALL, or SUM and in the most efficient way possible as
measured by consistent gets.
SELECT l.location_id, l.cityFROM locations lWHERE EXISTS ( SELECT * FROM
departments d, employees e, jobs j WHERE d.location_id = l.location_id AND
e.department_id = d.department_id AND j.job_id = e.job_id AND (
(e.first_name = 'Steven' AND e.last_name = 'King') OR j.job_title =
'President' OR EXISTS ( SELECT * FROM job_history jh, jobs j WHERE
jh.employee_id = e.employee_id AND j.job_id = jh.job_id AND
j.job_title = 'President' ) ));
Kim has brought it down to two consistent gets and has a goal of just one
consistent get.
The latest NoCOUG Journal is at
http://nocoug.files.wordpress.com/2014/08/nocoug_journal_201408.pdf. The
journal is a dinosaur and costs a huge amount to print but it is a very
beautiful dinosaur and it would be sad if it went extinct.
Other related posts:
- » OT: Update on the SQL mini-challenge - Iggy Fernandez
