Oracle DBA Tips Corner
Return to the Oracle DBA Tips Corner.
|
Oracle DBA Tips Corner
|
Fun with SQL
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
In common usage, SQL supports four types of SQL statements:
This type of SQL statement implements the standard relational operations
such as SELECTION, PROJECTION and JOIN. The SELECT statement forms the basis for many
other SQL operations. You can use SELECT statements to query tables, create views,
in subqueries, or when creating a table defined as the result set of query.
DML statements are used to allow data in the
database to be added, amended or deleted. DML functionality is provided by
the use of INSERT, UPDATE and DELETE statements.
This is the language that allows for objects to
be created or altered. Provided below are a few example Oracle DDL statements:
This is the language that allows for transaction control.
Provided below are a few example Oracle DCL statements:
The development of SQL is governed by standards. A major revision of the SQL standard was completed
in 1992, called SQL2. SQL3 is a newer revision that supports object extensions and will be
partially implemented starting with Oracle8.
How can I transform a subquery involving the IN clause to a Join?
New Statement:
How can I transform a statement involving an OR condition to a UNION ALL?
New Statement:
How do I eliminate duplicate values in a table?
Method 1:
Method 2:
Method 3:
Method 4:
NOTE: If you where to create an index
on the joined fields in the inner loop, it may be possible to eliminate N^2
operations as there would be no need to loop through the entire table on each
pass be a record.
How can I get a count of the different data values in a column?
How can I get count/sum RANGES of data values in a column?
How can I dynamically generate a list of comma-separated items?
Consider the following CREATE CONTROLFILE example:
Can I retrieve only the Nth row from a table?
Method 2:
NOTE: Always remember that
there is no explicit order in a relational database.
Can I retrieve only rows X to Y from a table?
Can I retrieve EVERY Nth row from a table?
Retrieve the TOP N Rows from a table?
Method 2: The following workaround will work with prior releases
Is it possible to dump/examine the exact content of a database column?
Can I code a MATRIX Report using SQL?
Tree-Structured (Hierarchical) Queries
NOTE: Using the "EMP" table from the SCOTT/TIGER schema makes
a great test table since it has a "self-referencing" relation. (The MGR column contains the employee
number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree you are. Oracle can handle queries
with a depth up to 255 levels.
The "START WITH" clause is used to specify the start of the tree. More than one record can match
the starting condition. One disadvantage of having a "CONNECT BY PRIOR" clause is that you cannot
perform a JOIN to other tables.
Insert Large Number of Sequence Values using UNION
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Structured Query Language (SQL) is an implementation of a "data sublanguage" used in
almost all relational database systems.
SQL was developed by IBM in the early 1970s for use in System R, and is "de facto" standard, as
well as an ISO and ANSI standard.
Original Statement:
SELECT e.empno, e.ename
FROM emp e
WHERE e.deptno IN (
SELECT d.deptno
FROM dept d
WHERE d.loc = 'CHICAGO'
);
SELECT e.empno, e.ename
FROM emp e, dept d
WHERE d.loc = 'CHICAGO'
AND d.deptno = e.deptno;
Original Statement:
SELECT dname, loc
FROM dept
WHERE loc = 'CHICAGO'
OR loc = 'NEW YORK';
SELECT dname, loc
FROM dept
WHERE loc = 'CHICAGO'
UNION ALL
SELECT dname, loc
FROM dept
WHERE loc = 'NEW YORK';
Provided below are four methods for identifying or removing duplicate rows from
a table:
DELETE FROM emp a
WHERE rowid > (
SELECT min(rowid)
FROM emp b
WHERE a.emp_id = b.emp_id
);
CREATE TABLE emp2 AS
SELECT distinct *
FROM emp;
DROP TABLE emp;
RENAME emp2 TO emp;
DELETE FROM emp
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM emp
GROUP BY emp_id
);
DELETE FROM emp a
WHERE EXISTS (
SELECT 'true'
FROM emp b
WHERE b.emp_id = a.emp_id
AND b.rowid < a.rowid
);
SELECT
dname
, sum(decode(job, 'CLERK', 1, 0)) Clerk
, sum(decode(job, 'SALESMAN', 1, 0)) Salesman
, sum(decode(job, 'MANAGER', 1, 0)) Manager
, sum(decode(job, 'ANALYST', 1, 0)) Analyst
, sum(decode(job, 'PRESIDENT', 1, 0)) President
FROM
emp e
, dept d
WHERE
e.deptno (+) = d.deptno
GROUP BY
dname;
DNAME CLERK SALESMAN MANAGER ANALYST PRESIDENT
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING 1 0 1 0 1
OPERATIONS 0 0 0 0 0
RESEARCH 2 0 1 2 0
SALES 1 4 1 0 0
A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).
SELECT
job
, sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"
, sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"
, sum(decode(greatest(sal,0), least(sal,999), 1, 0)) "Range 0-1000"
FROM emp
GROUP BY job;
JOB Range 3000-6000 Range 1000-3000 Range 0-1000
--------- --------------- --------------- ------------
ANALYST 2 0 0
CLERK 0 2 2
MANAGER 0 3 0
PRESIDENT 1 0 0
SALESMAN 0 4 0
I often find myself needing to dynamically generate a list of comma-separated items
(i.e. a list of database files separated by commas). The output format, for example,
would be:
... Datafile1, Datafile2, Datafile3 ...
Instead of thinking of the list as a linear set of items, re-write the
set of items each on a separate line as follows:
...
Datafile1
, Datafile2
, Datafile3
...
Notice that a comma is used on each line except for the first item. We
can now take advantage of a popular Analytic Function (RANK) to distinguish
the first line.
SET HEADING OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 9000
SET LINESIZE 135
SET TRIMSPOOL ON
SET TRIMOUT ON
SET ECHO OFF
SET SQLBLANKLINES ON
SPOOL cr_control_file_example.sql
prompt CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
prompt MAXLOGFILES 16
prompt MAXLOGMEMBERS 3
prompt MAXDATAFILES 100
prompt MAXINSTANCES 8
prompt MAXLOGHISTORY 3182
prompt LOGFILE
prompt GROUP 1 (
prompt '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log',
prompt '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
prompt ) SIZE 50M,
prompt GROUP 2 (
prompt '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log',
prompt '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
prompt ) SIZE 50M,
prompt GROUP 3 (
prompt '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log',
prompt '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
prompt ) SIZE 50M
prompt DATAFILE
SELECT
CASE WHEN ROW_NUMBER() OVER(ORDER BY name) = 1
THEN
' ''' || name || ''''
ELSE
' , ''' || name || ''''
END AS file_name
FROM v$datafile;
prompt CHARACTER SET WE8ISO8859P1
SPOOL OFF
------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 3182
LOGFILE
GROUP 1 (
'/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'
) SIZE 50M,
GROUP 2 (
'/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'
) SIZE 50M,
GROUP 3 (
'/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log'
, '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'
) SIZE 50M
DATAFILE
'+ORCL_DATA1/orcl/datafile/example.263.623550121'
, '+ORCL_DATA1/orcl/datafile/soe.269.623559923'
, '+ORCL_DATA1/orcl/datafile/soeindex.270.623560217'
, '+ORCL_DATA1/orcl/datafile/sysaux.261.623550085'
, '+ORCL_DATA1/orcl/datafile/system.259.623549989'
, '+ORCL_DATA1/orcl/datafile/undotbs1.260.623550053'
, '+ORCL_DATA1/orcl/datafile/undotbs2.264.623550147'
, '+ORCL_DATA1/orcl/datafile/users.265.623550181'
CHARACTER SET WE8ISO8859P1
Method 1:
SELECT
ename
, job
, hiredate
FROM
emp
WHERE
rowid = (
SELECT rowid FROM emp
WHERE rownum <= 3
MINUS
SELECT rowid FROM emp
WHERE rownum < 3
);
SELECT
ename
, job
, hiredate
FROM
emp
WHERE
rownum = 1
AND rowid NOT IN (
SELECT rowid FROM emp
WHERE rownum < 3
);
SELECT
ename
, job
, hiredate
FROM
emp
WHERE
rowid in (
SELECT rowid FROM emp
WHERE rownum <= 7
MINUS
SELECT rowid FROM emp
WHERE rownum < 3
);
Method 1:
SELECT
ename
, job
, hiredate
FROM
emp
WHERE
(rowid,0) in (
SELECT rowid, mod(rownum,4)
FROM emp
);
Method 2: Using Dynamic Views: (available in Oracle7.2 and higher)
SELECT
ename
, job
, hiredate
FROM ( SELECT rownum rn, empno, ename, job, hiredate
FROM emp
) d_table
WHERE mod(d_table.rn,4) = 0;
Method 1: Starting with Oracle8i, you can have an inner-query with an ORDER BY clause
SELECT
ename
, job
, hiredate
, sal
FROM ( SELECT empno, ename, job, hiredate, sal
FROM emp
ORDER BY sal DESC
)
WHERE rownum < 6;
SELECT
ename
, job
, hiredate
, sal
FROM
emp a
WHERE 5 >= ( SELECT count(distinct b.sal)
FROM emp b
WHERE b.sal >= a.sal
)
ORDER BY a.sal DESC
SELECT ename, job, dump(job)
FROM emp
WHERE ename = 'SMITH';
ENAME JOB DUMP(JOB)
---------- --------- ---------------------------
SMITH CLERK Typ=1 Len=5: 67,76,69,82,75
Oracle Type Codes for Column Dumps
Type Code
Oracle Datatype
1
VARCHAR2
2
NUMBER
12
DATE
96
CHAR
SELECT *
FROM ( SELECT job
, sum(decode(deptno, 10, sal)) DEPT10
, sum(decode(deptno, 20, sal)) DEPT20
, sum(decode(deptno, 30, sal)) DEPT30
, sum(decode(deptno, 40, sal)) DEPT40
FROM emp e
GROUP BY job
)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
SELECT lpad(' ', level*4) || ename
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr IS NULL;
Employee Name
------------------------
KING
JONES
JHUNTER
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
Here is an example SQL script that will insert 1,000,000 sequence values
into a table using a series of UNION's and a cartesian product.
DROP TABLE sequence_values
/
CREATE TABLE sequence_values (id NUMBER)
/
INSERT INTO sequence_values
SELECT
millions.x +
hundred_thousands.x +
ten_thousands.x +
thousands.x +
hundreds.x +
tens.x +
ones.x
FROM
(SELECT 0 x FROM dual
UNION SELECT 1 FROM dual
UNION SELECT 2 FROM dual
UNION SELECT 3 FROM dual
UNION SELECT 4 FROM dual
UNION SELECT 5 FROM dual
UNION SELECT 6 FROM dual
UNION SELECT 7 FROM dual
UNION SELECT 8 FROM dual
UNION SELECT 9 FROM dual) ones,
(SELECT 0 x FROM dual
UNION SELECT 10 FROM dual
UNION SELECT 20 FROM dual
UNION SELECT 30 FROM dual
UNION SELECT 40 FROM dual
UNION SELECT 50 FROM dual
UNION SELECT 60 FROM dual
UNION SELECT 70 FROM dual
UNION SELECT 80 FROM dual
UNION SELECT 90 FROM dual) tens,
(SELECT 0 x FROM dual
UNION SELECT 100 FROM dual
UNION SELECT 200 FROM dual
UNION SELECT 300 FROM dual
UNION SELECT 400 FROM dual
UNION SELECT 500 FROM dual
UNION SELECT 600 FROM dual
UNION SELECT 700 FROM dual
UNION SELECT 800 FROM dual
UNION SELECT 900 FROM dual) hundreds,
(SELECT 0 x FROM dual
UNION SELECT 1000 FROM dual
UNION SELECT 2000 FROM dual
UNION SELECT 3000 FROM dual
UNION SELECT 4000 FROM dual
UNION SELECT 5000 FROM dual
UNION SELECT 6000 FROM dual
UNION SELECT 7000 FROM dual
UNION SELECT 8000 FROM dual
UNION SELECT 9000 FROM dual) thousands,
(SELECT 0 x FROM dual
UNION SELECT 10000 FROM dual
UNION SELECT 20000 FROM dual
UNION SELECT 30000 FROM dual
UNION SELECT 40000 FROM dual
UNION SELECT 50000 FROM dual
UNION SELECT 60000 FROM dual
UNION SELECT 70000 FROM dual
UNION SELECT 80000 FROM dual
UNION SELECT 90000 FROM dual) ten_thousands,
(SELECT 0 x FROM dual
UNION SELECT 100000 FROM dual
UNION SELECT 200000 FROM dual
UNION SELECT 300000 FROM dual
UNION SELECT 400000 FROM dual
UNION SELECT 500000 FROM dual
UNION SELECT 600000 FROM dual
UNION SELECT 700000 FROM dual
UNION SELECT 800000 FROM dual
UNION SELECT 900000 FROM dual) hundred_thousands,
(SELECT 0 x FROM dual
UNION SELECT 1000000 FROM dual
UNION SELECT 2000000 FROM dual
UNION SELECT 3000000 FROM dual
UNION SELECT 4000000 FROM dual
UNION SELECT 5000000 FROM dual
UNION SELECT 6000000 FROM dual
UNION SELECT 7000000 FROM dual
UNION SELECT 8000000 FROM dual
UNION SELECT 9000000 FROM dual) millions
/
Table dropped.
Table created.
10000000 rows created.
Sunday, 27-May-2007 03:19:55 EDT
Page Count: 23994