Oracle Profiling Functions Overview

Oracle Profiling Functions Overview

Profiling functions in Oracle are similar to aggregate functions, but for each set of records, no matter how many rows, the aggregate function returns only one row of values, and the parse function returns a value for each row of records. This set of records, called the Parse Function (window), determines the scope of the data to be processed, which can be physically determined by the specified number of rows, or logically by relative offsets. The parse function is always executed after a clause operation other than an order, so it cannot appear in the Where, group by, and so on, only in the select list and the ORDER BY clause.

Preparing test data

Execute the following statement under Scott user to create the test table and test data:

CREATE TABLE Lw_sales (dept_id varchar2 (6), sale_date date, Goods_type varchar2 (4), sale_cnt number (10)); COMMENT on table lw_sales is ' Sales data test table.  
'/SET DEFINE off; Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/16/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G00 ', 400); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 04/05/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G00 ', 400); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/17/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G00 ', 700); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 04/13/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G00 ', 900); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 04/18/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G01 ', 300); Insert into Lw_salES (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/15/2013 00:00:00 ', ' mm/dd/yyyy hh24:mi  
: SS '), ' G01 ', 30; Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 04/15/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G01 ', 400); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 03/10/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G01 ', 200); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 03/06/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G01 ', 70); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/17/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G01 ', 400); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 04/07/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G02 ', 300); Insert into Lw_sales (dept_id, Sale_date, GOods_type, sale_cnt) Values (' S01 ', to_date (' 03/14/2013 00:00:00 ', ' mm/dd/yyyy HH24:MI:SS '), ' G02 ', 300; Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 03/09/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G02 ', 900); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/16/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G02 ', 800); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 04/22/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G03 ', 300); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/21/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G03 ', 200); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/17/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G03 ', 600); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) valuEs (' S02 ', to_date (' 04/05/2013 00:00:00 ', ' mm/dd/yyyy HH24:MI:SS '), ' G03 ', 800); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 04/03/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G03 ', 200); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/06/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G03 ', 800); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/09/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G04 ', 800); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/07/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G04 ', 30); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 04/20/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G04 ', 900); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S01 ', to_date (' 03/21/2013 00:00:00 ', ' mm/dd/yyyy HH24:MI:SS '), ' G04 ', 800; Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/02/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G04 ', 500); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/10/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G04 ', 500); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 04/06/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G04 ', 300); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/07/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G05 ', 400); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S02 ', to_date (' 03/06/2013 00:00:00 ', "  
Mm/dd/yyyy HH24:MI:SS '), ' G05 ', 200); Insert into Lw_sales (dept_id, Sale_date, Goods_type, sale_cnt) Values (' S00 ', to_date (' 03/06/2013 00:00:00 ', " Mm/dd/yyyyHH24:MI:SS '), ' G05 ', 500); COMMIT;