`
juji1010
  • 浏览: 117944 次
社区版块
存档分类
最新评论

转---借助内存表处理复杂的oracle查询要求.

 
阅读更多

借助内存表处理复杂的oracle查询要求.

在日常业务处理过程中,我们经常会碰到一些非常规的查询需求, 这些需求我们或者可以借助动态语句,或者其他现有的oracle函数完成查询结果, 但效率往往差强人意.

假设我们有一个客户订单业务表{订单号, 订单客户, 订单日期, 数量, 金额}存储了订单的往来明细数据,订单表中保存最近3个月的往来明细共1000w条记录, 其中客户总量约500000. 并假定在订单表上有针对日期和客户的单独索引.

现在要求提供对任意集合的多个客户的某段时间的订单明细数据.

Select 订单号, 订单客户, 订单日期, 数量, 金额

From 订单业务表

Where 订单日期 between 开始日期 and 结束日期

And 订单客户 in (客户1, 客户2, 客户3…)

面对这种需求, 我们可以要求前台程序传回三个参数, 开始日期, 结束日期, 客户列表(类似于 客户1, 客户2, 客户3, 客户4…)

, 创建测试用表.

Create table t_order_cust(

O_id varchar2(20),

O_customer varchar2(20),

O_date date,

O_qty numeric(18,2),

O_amount numeric(18,2)

);

Create index ind_t_order_cust_01 on t_order_cust(o_customer);

Create index ind_t_order_cust_02 on t_order_cust(o_date);

, 方法1 , 使用动态语句拼凑实现.

针对上述查询, 我们可以拼凑动态语句实现, 如下代码所示.

Declare

V_beg_date date := trunc(sysdate,’month’);

V_end_date date := trunk(sysdate);

V_cust_str varchar2(1000) := ‘’’客户1’’, ’’客户2’’, ’’客户3’’…’;

V_sql_str varchar2(2000) ;

Begin

V_sql_str := ‘select * from t_order_cust

Where o_date between ’ || v_beg_date || ‘ and ’ || v_end_date ||’

And o_customer in (’||v_cust_str||’)’;

Execute immediate v_sql_str;

End;

根据表明细数据的特点我们知道, 客户索引的选择性为 1000w/50w= 20, 而日期索引的选择性为 1000w/(3*30) = 10w, 明显使用日期索引效率极差, 我们只能选择使用客户上的索引,使用这种处理方法的优势是可以用到客户上的索引, in使用索引的效率相对较差, 并且这种处理方式下, oracle每次执行查询都需要重新建立查询执行树, 也是需要一定的额外开销.

, 方法2 使用like查询

除了上面的拼凑动态执行语句的方法之外, 我们可以设想的到的第二种方法就是借助于oracle提供的like功能. 如下代码所示.这种处理方式下对客户列表字符串的要求跟方法一少有区别.

Declare

V_beg_date date := trunc(sysdate,’month’);

V_end_date date := trunk(sysdate);

V_cust_str varchar2(1000) := ‘客户1, 客户2, 客户3…’;

Begin

select * from t_order_cust

Where o_date between v_beg_date and v_end_date

And v_cust_str like ‘%’||o_customer||’%’;

End;

这种处理方式的优点在于代码书写简单, 但由于对客户索引所在字段o_customer做了拼接处理||, 所以将导致客户索引ind_t_order_cust_01无效, 而只能使用效率较差的日期索引. 在数据量较小, 对效率无法造成影响时这种方法可以接受, 但数据量较大时, 这种方法的缺点将是致命的.

, 方法3 使用instr函数处理

Declare

V_beg_date date := trunc(sysdate,’month’);

V_end_date date := trunk(sysdate);

V_cust_str varchar2(1000) := ‘客户1, 客户2, 客户3…’;

Begin

select * from t_order_cust

Where o_date between v_beg_date and v_end_date

And instr(v_cust_str like ,o_custome) >0

End;

这种处理方式的优缺点跟使用like相似, 同样由于对o_customer使用了函数, 导致该索引不可用, 函数索引同样也不适用于这种情况.

, 方法4 使用内存表处理

我们知道, oracle, sqlserver等关系数据库最善于处理的数据类型是集合, 而不是单独的记录. 同样的100条记录, 如果逐条循环处理和批量处理其效率的差别将是几何单位的.

所以, 为了提高查询效率, 我们这里考虑将给定的客户字符串转变为一个集合或者临时表来处理. Oracle使用全局临时表和复杂数据类型集合来支持这一点.

这里我们介绍一下使用复杂数据类型集合来处理的方式.

首先我们定义一个复杂类型.

create or replace type ctl.type_jax_varc2tab is table of varchar2(2000);

然后定义一个函数实现将给定的字符串转换为嵌套内存表.

CREATE OR REPLACE FUNCTION f_jax_str2tab(p_str IN VARCHAR2,
p_sep
varchar2 default ','
)
RETURN ctl.type_jax_varc2tab IS
/******************************************************************
Ver1.0 Created by jaxzhang on 2009-06-08
把字符串(1*2*3*4*5)转换为内存表形式
create or replace type type_jax_varc2tab is table of varchar2(2000);
测试用例:SELECT * FROM TABLE(f_jax_str2tab('1*2*3*4*5','*'));
******************************************************************/

v_str
varchar2(2000);
v_cnt
NUMBER ;
v_numtab type_jax_varc2tab := type_jax_varc2tab();
--返回内存表
BEGIN
select decode(substr(p_str,-1),p_sep,p_str,p_str || p_sep) into v_str from dual;
select length(v_str) - length(REPLACE(v_str, p_sep)) into v_cnt from dual;

FOR i IN 1 .. v_cnt LOOP
v_numtab.
EXTEND;
v_numtab(i) := substr(v_str,
1, instr(v_str, p_sep) - 1);
v_str := substr(v_str, instr(v_str,p_sep) +
1);
END LOOP;

RETURN v_numtab;
EXCEPTION
WHEN OTHERS THEN
v_numtab.
DELETE;
END;
上述函数的功能就是要将类似于客户1,客户2,客户3’的字符串转换为如下形式.

SELECT * FROM TABLE(f_jax_str2tab('客户1,客户2,客户3',','));

COLUMN_VALUE

客户1

客户2

客户3

得到上述的内存表之后, 我们就可以使用类似于一个表或者视图的方式来与正式表t_order_cust关联得到我们需要的查询结果.

Select /*+ ordered use_nl(a b)*/

From TABLE(f_jax_str2tab('客户1,客户2,客户3',',')) a,

T_order_cust b

Where b.o_customer = a.column_value

分享到:
评论

相关推荐

    paho.mqtt.embedded-c-master.zip

    Paho是IBM和Oracle共同发起的一个开源项目,提供了多种编程语言的客户端库,包括本文将要讨论的“paho.mqtt.embedded-c”,这是一个针对嵌入式系统的MQTT客户端实现。 标题中的“paho.mqtt.embedded-c-master.zip”...

    Python库 | windows-oracle-finder-2020.3.6.tar.gz

    本资源“windows-oracle-finder-2020.3.6.tar.gz”是一个针对Python的库,主要用于在Windows环境下寻找Oracle数据库的相关信息。这个库可能是由开发者社区中的某位成员开发的,用于解决特定的问题或者简化与Oracle...

    mysql-installer-web-community-8.0.16.0.msi.7z

    3. **窗口函数**:MySQL 8.0引入了窗口函数,允许用户在聚合操作中进行更复杂的分析,无需借助子查询或者自连接。 4. **增强的安全性**:包括默认使用加密的连接,更强大的用户管理和权限控制,以及改进的审计日志...

    超详细Oracle教程.pdf

    - **自连接**:介绍如何在一个表中进行自我连接,以解决复杂的查询需求。 - **嵌套查询**:教授如何在查询语句中嵌套另一个查询语句,实现更为复杂的数据筛选。 #### 八、子查询 - **简单子查询**:介绍如何在...

    Oracle Database 18c In-Memory技术概述.docx

    In-Memory 外部表是 Oracle Database In-Memory 的一个核心特性,该特性可以对外部表进行存储和处理,以提高查询性能。 In-Memory 表达式是 Oracle Database In-Memory 的一个核心特性,该特性可以对 In-Memory 列...

    windows oracle 18c.txt

    内置JSON数据类型和支持,方便处理半结构化数据,无需额外编写复杂代码即可实现高效查询。 #### 4.3 数据仓库优化 提供了一系列专门针对数据仓库场景的优化功能,如分区表、压缩算法等,有效提升大数据量处理能力。...

    HammerDB-3.3-Win版本的数据库压力测试工具

    《HammerDB-3.3-Win:数据库压力测试利器》 HammerDB-3.3-Win是一款专为IT专业人士设计的数据库...无论是商业数据库还是开源数据库,都可以借助HammerDB进行细致的性能评估,确保在日益复杂的数据环境中保持高效运行。

    Oracle9i spool续写实例

    对于大型数据集,可能需要使用临时表或者游标来存储和处理数据,以避免内存限制。同时,为了保证数据一致性,可能还需要在读写文件前进行锁定,防止并发问题。 总的来说,Oracle 9i的Spool续写需要借助PL/SQL编程来...

    Oracle多行记录合并

    如果需要处理复杂的数据关系,可能需要借助`CONNECT BY`或`MODEL`。在使用这些方法时,务必注意性能和内存使用,特别是处理大量数据时。适当使用索引和分区策略可以优化查询效率。 总的来说,Oracle提供了多种方式...

    ORACLE中SQL查询.docx

    2. 过多的 I/O 操作,最典型的就是未建立恰当的索引,导致对查询表进行全表扫描。 3. 处理了太多的无用记录,如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。 4. 未充分利用数据库提供的功能,...

    借助 SAP 内存数据库,加速创新,推进数字化转型.rar

    该技术以其独特的高性能、实时分析和处理能力,为企业提供了无与伦比的数据管理和业务洞察力。通过利用SAP内存数据库,企业能够即时访问和分析海量数据,实现快速决策和敏捷响应市场变化。这不仅优化了业务流程,...

    用java从Oracle的EMP表读数据并写到EXCEL中,实现oracle表的备份和导入

    7. **读取Excel数据**:从Excel中读取数据的过程与写入相反,使用POI库解析Excel文件,获取工作表,然后遍历单元格,将数据加载到内存中的数据结构。 8. **创建Oracle新表**:在Oracle数据库中,可以使用CREATE ...

    Excel to Oracle 程序实例

    - 执行批量插入操作,可能需要分批处理以避免内存占用过大或数据库锁定问题。 - 在所有数据成功导入后提交事务,如有错误则回滚。 4. **源码结构**: - 项目可能包含一个主程序类,负责调度整个流程。 - Excel...

    Oracle 内存配置

    Oracle数据库的内存配置是性能优化的重要环节,通过合理设置内存参数并结合自动内存管理功能,可以有效提升系统的响应速度和处理能力。同时,借助Oracle提供的各种监控工具,可以及时发现并解决内存使用方面的问题,...

    ORACLE数据库性能优化的分析.pdf

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,以其高级结构化查询语言(SQL)为基础,为各种规模的企业提供数据存储和处理解决方案。随着业务增长和数据量的不断攀升,数据库性能优化成为确保系统稳定性...

    IBM CDC 与ORACLE GOLDEN GATE比较

    - **Oracle Golden Gate**:将捕获到的数据暂时存储在内存中,当内存不足时,会将数据写入磁盘缓存。 - **IBM CDC**:采用缓存文件的形式来记录捕获的数据。 - **传输单位**: - **Oracle Golden Gate**:以...

    海量数据的处理及优化.docx

    - **索引建立**:为大表建立适当的索引,包括复合索引,以优化查询和聚合操作,但也要注意避免过度索引导致的插入性能下降。 - **硬件升级**:增加CPU数量、内存容量,扩大硬盘空间,确保硬件条件满足处理需求。 ...

    Oracle11g从入门到精通英文版

    - **硬件要求**:确保服务器或工作站满足Oracle 11g的最低硬件需求,如处理器速度、内存大小、磁盘空间等。 - **软件环境**:确认操作系统版本及补丁级别符合要求;安装必要的系统软件包。 - **用户权限**:创建具有...

Global site tag (gtag.js) - Google Analytics