oracle快速返回,如何让查询先以最快速返回一些记录

编程入门行业动态更新时间:2024-10-11 15:24:57

oracle<a href=https://www.elefans.com/category/jswz/34/1771431.html style= 快速返回,如何让查询先以最快速返回一些记录"/>

oracle快速返回,如何让查询先以最快速返回一些记录

-- 楼上的贴多了，再贴一下：

-- #################################################################################### --

-- 详细的效率测试脚本(生成表测试数据略)

----------------------------------------------------------------------------------

-- 例如：我有一张表第每天2亿的数据量，我是按天间隔分区，然后按小子子分区)

-- ######################################################################################################### --

-- Step 1: 创建表：(按天间隔分区小时子分区)

CREATE TABLE BIEE.DW_ADS_ADI_VADS_HOUR2

(

DATE_ID NUMBER(8),

HOUR_ID NUMBER(2),

SITE_ID number(1),

SUB_CHANNEL_ID number(7),

CITY_ID number(7),

FADI number(38,0),

RADI number(38,0),

date_time as (to_date(date_id,'YYYYMMDD'))

)

PARTITION BY RANGE (date_time) INTERVAL(NUMTODSINTERVAL(1,'day'))

STORE IN (adv_m01_tbs,adv_m02_tbs,adv_m03_tbs,adv_m04_tbs,adv_m05_tbs,adv_m06_tbs,adv_m07_tbs,adv_m08_tbs,adv_m09_tbs,adv_m10_tbs,adv_m11_tbs,adv_m12_tbs)

SUBPARTITION BY LIST (HOUR_ID)

SUBPARTITION TEMPLATE

( SUBPARTITION p_0 VALUES(0),

SUBPARTITION p_1 VALUES(1),

SUBPARTITION p_2 VALUES(2),

SUBPARTITION p_3 VALUES(3),

SUBPARTITION p_4 VALUES(4),

SUBPARTITION p_5 VALUES(5),

SUBPARTITION p_6 VALUES(6),

SUBPARTITION p_7 VALUES(7),

SUBPARTITION p_8 VALUES(8),

SUBPARTITION p_9 VALUES(9),

SUBPARTITION p_10 VALUES(10),

SUBPARTITION p_11 VALUES(11),

SUBPARTITION p_12 VALUES(12),

SUBPARTITION p_13 VALUES(13),

SUBPARTITION p_14 VALUES(14),

SUBPARTITION p_15 VALUES(15),

SUBPARTITION p_16 VALUES(16),

SUBPARTITION p_17 VALUES(17),

SUBPARTITION p_18 VALUES(18),

SUBPARTITION p_19 VALUES(19),

SUBPARTITION p_20 VALUES(20),

SUBPARTITION p_21 VALUES(21),

SUBPARTITION p_22 VALUES(22),

SUBPARTITION p_23 VALUES(23)

)

(PARTITION P20121201_LS VALUES LESS THAN (TO_DATE('20121201','YYYYMMDD')) TABLESPACE adv_m11_tbs)

PARALLEL;

CREATE TYPE ads_stockpivot_ot AS OBJECT

(

DATE_ID NUMBER(8,0),

HOUR_ID NUMBER(2,0),

SITE_ID NUMBER(1,0),

SUB_CHANNEL_ID NUMBER(7,0),

CITY_ID NUMBER(7,0),

FADI NUMBER(38,0),

RADI NUMBER(38,0)

)

-- 创建这个对象的集合，用于定义函数的返回值类型

CREATE TYPE ads_stockpivot_nnt AS TABLE OF ads_stockpivot_ot

-- ######################################################################################################### --

-- 方法一：“普通的表值函数”

CREATE OR REPLACE FUNCTION fun_ads_adi_vads_hour2(

i_from_date_time IN DATE DEFAULT TRUNC(SYSDATE-1),

i_to_date_time IN DATE,

i_from_hour_id IN NUMBER,

i_to_hour_id IN NUMBER,

i_site_id IN NUMBER,

i_sub_channel_id IN NUMBER,

i_city_id IN NUMBER

)

RETURN ads_stockpivot_nnt

-- PIPELINED

l_ads_stockpivot_ot ads_stockpivot_nnt := ads_stockpivot_nnt();

BEGIN

l_ads_stockpivot_ot := ads_stockpivot_nnt();

FOR i in (SELECT date_id, hour_id, site_id, sub_channel_id, city_id, fadi, radi

FROM DW_ADS_ADI_VADS_HOUR2

WHERE date_time >= nvl(i_from_date_time,trunc(sysdate-1))

AND date_time <= nvl(i_to_date_time,trunc(sysdate-1))

AND hour_id >= nvl(i_from_hour_id,hour_id)

AND hour_id <= nvl(i_to_hour_id,hour_id)

AND site_id = nvl(i_site_id,site_id)

AND sub_channel_id = nvl(i_sub_channel_id,sub_channel_id)

AND city_id = nvl(i_city_id,city_id) )

LOOP

l_ads_stockpivot_ot.extend;

/* 通过扫描对集合数据进行处理 */

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count) := ads_stockpivot_ot(NULL,NULL,NULL,NULL,NULL,NULL,NULL);

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).date_id := i.date_id;

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).hour_id := i.hour_id;

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).site_id := i.site_id;

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).sub_channel_id := i.sub_channel_id;

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).city_id := i.city_id;

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).fadi := i.fadi;

l_ads_stockpivot_ot(l_ads_stockpivot_ot.count).radi := i.radi;

END LOOP;

RETURN(l_ads_stockpivot_ot);

EXCEPTION WHEN OTHERS THEN

RETURN NULL;

END;

set timing on;

-- 测试1.1 查11天的数据，出错

SELECT count(*) FROM TABLE(fun_ads_adi_vads_hour2(to_date('20121205','yyyymmdd'),to_date('20121215','yyyymmdd'),9,12,null,null,null));

第 1 行出现错误:

ORA-04030: 在尝试分配 69656 字节 (callheap,KTI call freeable small pool) 时进程内存不足

已用时间: 00: 00: 39.29

-- 查看应该返回的记录行数：

select count(*) from DW_ADS_ADI_VADS_HOUR2

where date_time>=to_date('20121205','yyyymmdd')

and date_time<=to_date('20121215','yyyymmdd')

and hour_id >= 9

and hour_id <= 12;

COUNT(*)

----------

22921976

已选择 1 行。

已用时间: 00: 00: 02.70

-------------------------------------

-- 测试1.2 查2天的数据还行

SELECT count(*) FROM TABLE(fun_ads_adi_vads_hour2(to_date('20121205','yyyymmdd'),to_date('20121206','yyyymmdd'),9,12,null,null,null));

COUNT(*)

----------

4167632

已选择 1 行。

已用时间: 00: 00: 25.31

-- 验证返回的记录行数是否正确：

select count(*) from DW_ADS_ADI_VADS_HOUR2

where date_time>=to_date('20121205','yyyymmdd')

and date_time<=to_date('20121206','yyyymmdd')

and hour_id >= 9

and hour_id <= 12;

COUNT(*)

----------

4167632

已选择 1 行。

已用时间: 00: 00: 00.54

-- ######################################################################################################### --

-- 方法二：“PIPELINED表值函数”

CREATE OR REPLACE FUNCTION fun_ads_adi_vads_hour2(

i_from_date_time IN DATE DEFAULT TRUNC(SYSDATE-1),

i_to_date_time IN DATE,

i_from_hour_id IN NUMBER,

i_to_hour_id IN NUMBER,

i_site_id IN NUMBER,

i_sub_channel_id IN NUMBER,

i_city_id IN NUMBER

)

RETURN ads_stockpivot_nnt

PIPELINED

CURSOR l_cur(c_from_date_time in date, c_to_date_time in date, c_from_hour_id in number, c_to_hour_id in number, c_site_id in number, c_sub_channel_id in number, c_city_id in number)

SELECT date_id, hour_id, site_id, sub_channel_id, city_id, fadi, radi

FROM DW_ADS_ADI_VADS_HOUR2

WHERE date_time >= nvl(c_from_date_time,trunc(sysdate-1))

AND date_time <= nvl(c_to_date_time,trunc(sysdate-1))

AND hour_id >= nvl(c_from_hour_id,hour_id)

AND hour_id <= nvl(c_to_hour_id,hour_id)

AND site_id = nvl(c_site_id,site_id)

AND sub_channel_id = nvl(c_sub_channel_id,sub_channel_id)

AND city_id = nvl(c_city_id,city_id);

l_curs l_cur%rowtype;

l_ads_stockpivot_ot ads_stockpivot_ot := ads_stockpivot_ot(NULL,NULL,NULL,NULL,NULL,NULL,NULL);

BEGIN

OPEN l_cur(i_from_date_time, i_to_date_time, i_from_hour_id, i_to_hour_id, i_site_id, i_sub_channel_id, i_city_id);

LOOP

FETCH l_cur INTO l_curs;

/* 通过扫描对集合数据进行处理 */

l_ads_stockpivot_ot.date_id := l_curs.date_id;

l_ads_stockpivot_ot.hour_id := l_curs.hour_id;

l_ads_stockpivot_ot.site_id := l_curs.site_id;

l_ads_stockpivot_ot.sub_channel_id := l_curs.sub_channel_id;

l_ads_stockpivot_ot.city_id := l_curs.city_id;

l_ads_stockpivot_ot.fadi := l_curs.fadi;

l_ads_stockpivot_ot.radi := l_curs.radi;

PIPE ROW(l_ads_stockpivot_ot);

EXIT WHEN l_cur%NOTFOUND;

END LOOP;

CLOSE l_cur;

RETURN;

EXCEPTION WHEN OTHERS THEN

CLOSE l_cur;

RETURN;

END;

-- 测试2.1 查11天的数据(不报错，但执行了3分钟29秒多)

SELECT count(*) FROM TABLE(fun_ads_adi_vads_hour2(to_date('20121205','yyyymmdd'),to_date('20121215','yyyymmdd'),9,12,null,null,null));

COUNT(*)

----------

22921977

已选择 1 行。

已用时间: 00: 03: 29.67

-- 测试2.2 查2天的数据还行(不报错，但较1.2 的测试运行时间要长)

SELECT count(*) FROM TABLE(fun_ads_adi_vads_hour2(to_date('20121205','yyyymmdd'),to_date('20121206','yyyymmdd'),9,12,null,null,null));

COUNT(*)

----------

4167633

已选择 1 行。

已用时间: 00: 00: 37.85

-- ######################################################################################################### --

-- 方法三：“PIPELINED + BULK COLLECT 表值函数”

CREATE OR REPLACE FUNCTION fun_ads_adi_vads_hour2(

i_from_date_time IN DATE DEFAULT TRUNC(SYSDATE-1),

i_to_date_time IN DATE,

i_from_hour_id IN NUMBER,

i_to_hour_id IN NUMBER,

i_site_id IN NUMBER,

i_sub_channel_id IN NUMBER,

i_city_id IN NUMBER

)

RETURN ads_stockpivot_nnt

PIPELINED

CURSOR l_cur(c_from_date_time in date, c_to_date_time in date, c_from_hour_id in number, c_to_hour_id in number, c_site_id in number, c_sub_channel_id in number, c_city_id in number)

SELECT date_id, hour_id, site_id, sub_channel_id, city_id, fadi, radi

FROM DW_ADS_ADI_VADS_HOUR2

WHERE date_time >= nvl(c_from_date_time,trunc(sysdate-1))

AND date_time <= nvl(c_to_date_time,trunc(sysdate-1))

AND hour_id >= nvl(c_from_hour_id,hour_id)

AND hour_id <= nvl(c_to_hour_id,hour_id)

AND site_id = nvl(c_site_id,site_id)

AND sub_channel_id = nvl(c_sub_channel_id,sub_channel_id)

AND city_id = nvl(c_city_id,city_id);

TYPE l_cur_aat IS TABLE OF l_cur%ROWTYPE

INDEX BY BINARY_INTEGER;

l_curs l_cur_aat;