Oracle Select Statements

Oracle SELECT Statements Version 11.2.0.3

General Information

Data Dictionary Objects

V$SQL	V_$SQL_BIND_CAPTURE	V$SQL_PLAN_STATISTICS
V$SQLAREA	V_$SQL_BIND_DATA	V$SQL_PLAN_STATISTICS_ALL
V$SQLAREA_PLAN_HASH	V_$SQL_BIND_METADATA	V_$SQL_REDIRECTION
V$SQLTEXT	V$SQL_PLAN	V_$SQL_WORKAREA
V$SQLTEXT_WITH_NEWLINES

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 'Morgan' 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: Specifying 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.

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

The 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);

/
/
/

Sample Clause Statement with a WHERE Clause

Sample Clause Returning 35% Of Records After Filtering With A WHERE Clause

SELECT *
FROM <table_name>
SAMPLE (<percentage_of_rows>)
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 Clauses

See Related Topics Links Below

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 Table 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 that ignores upper/lower case characters

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 INTO a Scalar Variable

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

-- this will succeed
DECLARE
x INTEGER;
BEGIN
SELECT seq.NEXTVAL
INTO x
FROM dual;

dbms_output.put_line(x);
END;
/

/
/

SELECTing INTO a ROWTYPE Variable

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;
/

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: