| Basic Select Statements |
| Select All Columns and All Records in a Single Table or View |
SELECT *
FROM <table_name>; |
SELECT *
FROM all_tables; |
| Select Named Columns |
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>; |
SELECT table_name, tablespace_name, num_rows
FROM all_tables; |
Create Table As (CTAS)
Note: Redo only created when in ARCHIVE LOG mode |
CREATE TABLE <table_name> AS
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>; |
CREATE TABLE t AS
SELECT *
FROM all_tables;
SELECT * FROM t; |
| SELECTs can go anywhere |
SELECT DECODE((SELECT 'x' FROM DUAL), (
SELECT 'x' FROM DUAL), (SELECT 'y' FROM dual)) AS RESULT
FROM (SELECT 'm' FROM dual)
WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual)
AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual)
AND NVL((SELECT NULL FROM dual ), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual)
ORDER BY (SELECT 1 FROM dual); |
| |
| Select Unique Values |
All
"All" is the default. Using it is optional |
SELECT [ALL | DISTINCT | UNIQUE] <column_name_list>
FROM <table_name>; |
SELECT ALL object_type
FROM all_objects; |
| Distinct |
SELECT DISTINCT <column_name_list>
FROM <table_name>; |
SELECT DISTINCT object_type
FROM all_objects; |
| Unique |
SELECT UNIQUE <column_name_list>
FROM <table_name>; |
SELECT UNIQUE object_type
FROM all_objects; |
| |
| Select Statement With SAMPLE Clause |
Sample Clause Returning 1% Of Records and the BLOCK option.
Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does
not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.
In 11.2.0.1 block sampling appears to be very unreliable. |
SELECT *
FROM <table_name>
SAMPLE [BLOCK] (percentage_of_rows); |
CREATE TABLE t AS
SELECT object_name
FROM all_objects;
SELECT COUNT(*)
FROM t;
SELECT COUNT(*) * 0.01
FROM t;
SELECT *
FROM t
SAMPLE(1);
/
/
/
EXPLAIN PLAN FOR
SELECT *
FROM t
SAMPLE(1);
SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 714 | 17136 | 60 (7)| 00:00:01 |
| 1 | TABLE ACCESS SAMPLE| T | 714 | 17136 | 60 (7)| 00:00:01 |
------------------------------------------------------------------------
SELECT /*+ FULL */ COUNT(*)
FROM t
SAMPLE BLOCK (1);
/
/
/
/
/
/
/
/
EXPLAIN PLAN FOR
SELECT /*+ FULL */ COUNT(*)
FROM t
SAMPLE BLOCK (1);
SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS SAMPLE| T | 714 | 8568 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------ |
| |
| Select Statement With SEED Clause |
| Oracle Database always returns the same number of records given the same seed value |
SELECT *
FROM <table_name>
SAMPLE (percentage_of_rows)
SEED (<integer>; |
SELECT *
FROM t
SAMPLE(1) SEED (1);
/
/
/
SELECT *
FROM t
SAMPLE(1) SEED(5);
/
/
/ |
| |
| Select Statement With WHERE Clause |
| Sample Clause Returning 35% Of Records After Filtering With A WHERE Clause |
SELECT *
FROM <table_name>
SAMPLE (3.5)
WHERE .... |
SELECT COUNT(*)
FROM t
WHERE object_name LIKE '%J%';
SELECT COUNT(*) * 0.35
FROM t
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%'; |
| |
| Select Statement With GROUP BY Clause |
| Select with Group By Clause |
SELECT <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>; |
SELECT object_type, COUNT(*)
FROM all_objects
GROUP BY object_type; |
| |
| Select Statement With HAVING Clause |
| Select With Having Clause |
SELECT <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>
HAVING <aggregating_op_result> <condition> <value>; |
SELECT object_type, COUNT(*)
FROM all_objects
GROUP BY object_type
HAVING COUNT(*) < 6;
SELECT object_type, COUNT(*)
FROM all_objects
GROUP BY object_type
HAVING COUNT(*) > 5; |
| |
| Scalar Select |
| Select In Select Clause |
SELECT (
SELECT <single_value
FROM <table_name>
FROM <table_name>; |
| SELECT (SELECT 1 FROM dual) FROM dual; |
| |
| Select Statement Using Functions |
| Date Function Example |
SELECT
<date_function(<column_name>))
FROM <table_name>; |
desc all_objects
SELECT object_name, TO_DATE(timestamp, 'YYYY-MM-DD:HH24:MI:SS')
FROM all_objects
WHERE rownum < 11; |
| Numeric Function Example |
SELECT <numeric_function(<column_name>))
FROM <table_name>; |
desc user_extents
SELECT SUM(bytes)/1024/1024 USED_MB
FROM user_extents;
SELECT segment_type, SUM(bytes)/1024/1024 USED_MB
FROM user_extents
GROUP BY segment_type; |
| String Function Example |
SELECT <string_function(<column_name>))
FROM <table_name>; |
desc all_objects
SELECT object_name, LOWER(object_name) LOWER_ONAME
FROM all_objects
WHERE rownum < 11; |
| |
| Select For Update |
| Lock Record(s) |
SELECT <column_name_list)
FROM <table_name_list>
FOR UPDATE; |
CREATE TABLE parents (
pid NUMBER(10),
cash NUMBER(10,2));
CREATE TABLE children
(cid NUMBER(10),
fid NUMBER(10),
fin_level VARCHAR2(35));
DECLARE
CURSOR x_cur IS
SELECT pid
FROM parents;
x_rec x_cur%ROWTYPE;
x NUMBER(10,2) := 18000.64;
y NUMBER(10,2) := 100;
BEGIN
DELETE FROM parents;
DELETE FROM children;
FOR i IN 1..25
LOOP
INSERT INTO parents
VALUES (y, x);
x := x+1235.31;
y := y-1;
END LOOP;
y := 0;
OPEN x_cur;
LOOP
FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%NOTFOUND;
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
END LOOP;
CLOSE x_cur;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE cursor_loop3 IS
CURSOR x_cur IS
SELECT pid, cash
FROM parents
WHERE cash < 35000
FOR UPDATE;
BEGIN
FOR x_rec IN x_cur
LOOP
UPDATE parents
SET cash = FLOOR(cash)
WHERE CURRENT OF x_cur;
END LOOP;
COMMIT;
END cursor_loop3;
/ |
| FOR UPDATE with NOWAIT |
See Deadlocks Demo page |
| FOR UPDATE with WAIT |
See Deadlocks Demo page |
| FOR UPDATE with SKIP LOCKED |
See Deadlocks Demo page |
| |
| Partition Select |
| Select From Named Partition |
SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>); |
CREATE TABLE pt (
deptno NUMBER(10),
state VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION nw VALUES ('OR', 'WA'),
PARTITION sw VALUES ('AZ', 'CA', 'NM'));
INSERT INTO pt VALUES (1, 'WA');
INSERT INTO pt VALUES (1, 'OR');
INSERT INTO pt VALUES (1, 'CA');
SELECT COUNT(*) FROM pt;
SELECT COUNT(*) FROM pt PARTITION(nw);
SELECT COUNT(*) FROM pt PARTITION(sw); |
| |
| CASE Insensitive Select |
| Select From Named Partition |
SELECT DISTINCT
<column_name_list>
FROM <table_name> PARTITION (<partition_name>); |
conn / as sysdba
GRANT select ON v_$nls_parameters TO uwclass;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';
conn uwclass/uwclass
CREATE TABLE cis_test (
col1 VARCHAR2(10));
INSERT INTO cis_test VALUES ('one');
INSERT INTO cis_test VALUES ('TWO');
INSERT INTO cis_test VALUES ('thRee');
INSERT INTO cis_test VALUES ('FouR');
INSERT INTO cis_test VALUES ('fiVE');
SELECT * FROM cis_test;
SELECT col1 FROM cis_test ORDER BY 1;
ALTER SESSION SET nls_sort=binary_ci;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';
SELECT col1 FROM cis_test ORDER BY 1; |
| |
| PL/SQL Select Into |
| Selecting In PL/SQL Objects |
SELECT <clause>
INTO <clause>
FROM <clause>
WHERE <clause> |
CREATE TABLE t (
testcol NUMBER(3));
CREATE SEQUENCE seq;
SELECT seq.NEXTVAL FROM dual;
/
/
INSERT INTO t
(testcol)
VALUES
(seq.NEXTVAL);
/
/
SELECT * FROM t;
-- this will fail
BEGIN
SELECT seq.NEXTVAL FROM dual;
END;
/
set serveroutput on
DECLARE
x INTEGER;
BEGIN
SELECT seq.NEXTVAL
INTO x
FROM dual;
dbms_output.put_line(x);
END;
/
/
/ |
| SELECT into ROWTYPE |
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;
set serveroutput on
DECLARE
trec t%ROWTYPE;
BEGIN
SELECT *
INTO trec
FROM t
WHERE rownum = 1;
dbms_output.put_line(trec.table_name);
END;
/ |
