|
|
| Re: what is Sample Clause in Select Statement [message #60140 is a reply to message #60138] |
Sun, 18 January 2004 22:43   |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi
well Here is a small story from oracle doc :
sample_clause
The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.
BLOCK
BLOCK instructs Oracle to perform random block sampling instead of random row sampling.
sample_percent
sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to (but not including) 100.
Restrictions on Sampling During Queries
You can specify SAMPLE only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE TABLE ... AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.
Selecting a Sample: Examples
The following query estimates the number of orders in the oe.orders table:
SELECT COUNT(*) * 100 FROM orders SAMPLE (1);
The following example creates a sampled subset of the sample table hr.employees table and then joins the resulting sampled table with departments. This operation circumvents the restriction that you cannot specify the sample_clause in join queries:
CREATE TABLE sample_emp AS
SELECT employee_id, department_id FROM employees SAMPLE(10);
SELECT e.employee_id FROM sample_emp e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Sales';
When you specify SAMPLE, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
