ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用
ORA-30009: CONNECT BY 操作内存不足
存储13700000001 到 13799999999 所有的手机号，共有99999999个
SELECT *
FROM (SELECT ROWNUM SN FROM DUAL CONNECT BY LEVEL <= 13799999999) X
WHERE X.SN > 13700000001
SELECT ROWNUM FROM xmltable('1 to 6');
CREATE TABLE T_YH_20170705_LHR NOLOGGING
AS
SELECT 137 || (LPAD(ROWNUM, 8,'0')) x
FROM DUAL
CONNECT BY LEVEL <= 99999999;
DROP TABLE T_YH_20170705_LHR;
CREATE TABLE T_YH_20170705_LHR NOLOGGING AS
SELECT 137 || (LPAD(ROWNUM, 8,'0')) x
from xmltable('1 to 99999999');
SELECT COUNT(x) FROM T_YH_20170705_LHR;
今天在11g的环境上制造数据碰到ORA-30009: CONNECT BY 操作内存不足，10g开始支持XML后，改为xmltable就可以了。
SQL> drop table t_range purge;
SQL> create table t_range (id number not null PRIMARY KEY, test_date date) partition by range (test_date)
(
partition p_2014_7 values less than (to_date('2014-08-01', 'yyyy-mm-dd')),
partition p_2014_8 values less than (to_date('2014-09-01', 'yyyy-mm-dd')),
partition p_2014_9 values less than (to_date('2014-10-01', 'yyyy-mm-dd')),
partition p_2014_10 values less than (to_date('2014-11-01', 'yyyy-mm-dd')),
partition p_2014_11 values less than (to_date('2014-12-01', 'yyyy-mm-dd')),
partition p_2014_12 values less than (to_date('2015-01-01', 'yyyy-mm-dd')),
partition p_max values less than (MAXVALUE)
) nologging;
SQL> insert /*+append */ into t_range select rownum,
to_date(to_char(sysdate - 120, 'J') +
trunc(dbms_random.value(0, 120)),
'J')
from dual
connect by level <= 2000000;
insert /*+append */ into t_range select rownum,
*
第 1 行出现错误:
ORA-30009: CONNECT BY 操作内存不足
已用时间: 00: 00: 10.28
SQL> rollback;
回退已完成。
SQL> insert /*+append */ into t_range select rownum,
to_date(to_char(sysdate - 120, 'J') +
trunc(dbms_random.value(0, 120)),
'J')
from xmltable('1 to 2000000');
已创建2000000行。
已用时间: 00: 00: 28.76
SQL> commit;
Oracle XMLTable 使用教程与实例
从Oracle 10g开始，甲骨文公司新增了XQuery和XMLTable两个功能作为处理XML的武器。 XMLQuery一样，您可以使用XQuery语言构造XML数据和查询XML和关系数据。你可以使用XMLTable从XQuery查询结果创建关系表和列。
本文我们将了解Oracle XMLTable函数，并且通过例子介绍XMLTable函数的用法。
考虑到员工会有一些XML数据，所以我们创建一个EMPLOYEES表：
Create TABLE EMPLOYEES
(
id NUMBER,
data XMLTYPE
)
表创建完成后，我们往表里插入一些数据:
SQL Code
1 | Insert INTO EMPLOYEES |
注意：XML包含了员工的相关数据，在我们开始学习之前我们首先明确几个数据：
1、有4名员工在我们的XML文件
2、每个员工都有通过属性定义一个唯一的员工id emplid
3、每个员工也有一个属性type，定义雇员是否是管理员或用户。
4、每个员工都有四个子节点： firstname ， lastname ， age和email
5、年龄是多少
现在我们可以使用Oracle XMLTable函数从XML中检索不同的信息。
1、学习XPath表达式
使用XMLTable函数之前最好知道一点关于XPath。XPath使用路径表达式来选择XML文档中的节点或节点列表。看下面的列表：
Expression | Description |
nodename | 选择所有名称为"nodename"的节点 |
/ | 选择根节点 |
// | 从当前节点选择文档中相匹配的节点，无论他们在哪里 |
. | 选择当前节点 |
.. | 选择当前节点的父节点 |
@ | 选择属性 |
employee | 选择所有名称为"employee"的节点 |
employees/employee | 选择所有子节点为employee的employees节点 |
//employee | 选择所有employee的元素，无论他们在哪里 |
下面的表达式称为谓词列表。谓词在方括号中定义 [ ... ]。他们被用来找到一个特定的节点或包含一个特定值的节点。
Path Expression | Result |
/employees/employee[1] | 选择第一个employee节点,它是employees的子节点。 |
/employees/employee[last()] | 选择最后一个employee元素，它是employees的子节点 |
/employees/employee[last()-1] | 选择是employees子元素的倒数第二个employee元素 |
//employee[@type='admin'] | 选择所有具有与'admin'的值的属性命名类型的employee元素 |
其他更多的表达式可以参考Oracle官方手册
2、Oracle XMLTable函数的基础知识
读取Employees中所有firstname和lastname
在这个查询中,我们使用XMLTable函数从EMPLOYEES表解析XML内容。
select t.id, x.*
from employees t,
xmltable('/Employees/Employee' passing t.data columns firstname
varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname') x
where t.id = 1;
注XMLTable函数的语法：
XMLTable(''
PASSING <xml column>
COLUMNS <new column name> <column type> PATH <XQuery path>)
XMLTABLE函数包含一个XQuery行表达式和由一个或多个列表达式组成的COLUMNS子句。在上面的语句中，行表达式是 XPath /Employees/Employee。PASSING子句中的t.data指的是employees表中的XML列中的数据。
COLUMNS 子句用于将XML数据转换成关系数据，这里每个参数都定义了一个列名和SQL数据类型。在上面的查询中，我们定义了firstname 和 lastname列并指向PATH的firstname 和 lastname或者选定的节点。
输出：
使用text()读取节点值
在上面的教程中，我们读取到了firstname / lastname节点。通常我们还需要获取节点的文本值，下面的例子中，我们选取/Employees/Employee/firstname路径，并使用text()获取节点的值。
下面查询employees中所有的firstname
select t.id, x.*
from employees t,
xmltable('/Employees/Employee/firstname' passing t.data columns
firstname varchar2(30) path 'text()') x
where t.id = 1
输出：
不仅仅是text()表达式，Oracle还提供了其他很多有用的表达式，如 item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string。
读取所选节点的属性
XML节点定了相关属性，我们也可以读取到节点的这些属性，下面的查询是找出employee节点的type属性：
select emp.id, x.*
from employees emp,
xmltable('/Employees/Employee' passing emp.data columns firstname
varchar2(30) path 'firstname',
type varchar2(30) path '@type') x;
输出：
使用ID读取特定的记录
select t.id, x.*
from employees t,
xmltable('/Employees/Employee[@emplid=2222]' passing t.data columns
firstname varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname') x
where t.id = 1;
输出：
读取所有类型是admin的员工的firstname 和 lastname
select t.id, x.*
from employees t,
xmltable('/Employees/Employee[@type="admin"]' passing t.data columns
firstname varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname') x
where t.id = 1;
输出：
读取年龄超过40的所有员工的firstname 和 lastname
select t.id, x.*
from employees t,
xmltable('/Employees/Employee[age>40]' passing t.data columns
firstname varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname',
age varchar2(30) path 'age') x
where t.id = 1;
输出：
本文由UncleToo翻译整理，转载请注明出处！
原文（英文）地址：http://viralpatel.net/blogs/oracle-xmltable-tutorial/
About Me
.............................................................................................................................................
● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店
.............................................................................................................................................
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2145464/，如需转载，请注明出处，否则将追究法律责任。
©著作权归作者所有：来自51CTO博客作者小麦苗DB宝的原创作品，如需转载，请注明出处，否则将追究法律责任更多相关文章
- 【DB笔试面试490】 如何导出csv和html文件？
- 使用kubeadm搭建高可用k8s v1.16.3集群
- 【GoCN酷Go推荐】Html解析利器-goquery库
- Oracle 12c 新SQL提示（hint）
- 【DB笔试面试602】在Oracle中，如何从执行计划初步判断SQL的性能问
- 【DB笔试面试745】在Oracle中，RAC环境下的Redo文件可以放在节点本
- 【DB笔试面试739】在Oracle中，如何获取集群的名称（Cluster name）？
- 【DB笔试面试726】在Oracle中，RAC中的Public、Private、Virtual、
- 【DB笔试面试719】在Oracle中，什么是OCR、OLR和VF？