Select a sample of rows from an external table
This operation was introduced in Oracle 9.0.1
This operation is implemented in the following versions
|
This operation has not yet been reproduced in Oracle 10.2
This example was developed using Oracle 9.2.0.1 on Windows 2000
This example requires a directory
CREATE DIRECTORY d000 AS "pathname";
The directory does not need to be added to the utl_file_dir parameter
Create a file within the directory e.g.
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SPOOL t1.csv
BEGIN
dbms_output.enable (100000);
FOR f IN 0..99
LOOP
dbms_output.put_line (f||','||LPAD (TO_CHAR (f),10,'0'));
END LOOP;
END;
/
SPOOL OFF
Create a table definition
CREATE TABLE t1
(
c1 NUMBER,
c2 VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY d000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(
c1 CHAR,
c2 CHAR
)
)
LOCATION ('t1.csv');
);
The statement
SELECT * FROM t1 SAMPLE (10);
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 EXTERNAL TABLE ACCESS (SAMPLE) OF 'T1'
In Oracle 10.2 the statement
SELECT * FROM t1 SAMPLE (10);
generates the following error message
30560, 00000, "SAMPLE clause not allowed"