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开始,甲骨文公司新增了XQueryXMLTable两个功能作为处理XML的武器。 XMLQuery一样,您可以使用XQuery语言构造XML数据和查询XML和关系数据。你可以使用XMLTable从XQuery查询结果创建关系表和列。

本文我们将了解Oracle XMLTable函数,并且通过例子介绍XMLTable函数的用法。

考虑到员工会有一些XML数据,所以我们创建一个EMPLOYEES表:

Create TABLE EMPLOYEES

(

id NUMBER,

data XMLTYPE

)

表创建完成后,我们往表里插入一些数据:

 SQL Code 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

 

Insert INTO EMPLOYEES
     VALUES (1, xmltype ('
    <employee emplid="1111" type="admin">
        John
        Watson
        30
        johnwatson@sh.com
    
    <employee emplid="2222" type="admin">
        Sherlock
        Homes
        32
        sherlock@sh.com
    
    <employee emplid="3333" type="user">
        Jim
        Moriarty
        52
        jim@sh.com
    
    <employee emplid="4444" type="user">
        Mycroft
        Holmes
        41
        mycroft@sh.com
    
</employee emplid=</employee emplid=</employee emplid=</employee emplid='));

注意: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宝典),学习最实用的数据库技术。

ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店

.............................................................................................................................................

ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用
ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用
ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用
ORA-30009: CONNECT BY 操作内存不足--XMLTable的使用




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2145464/,如需转载,请注明出处,否则将追究法律责任。

上一篇:数据库学习笔记3-Mysql查询语句


下一篇:b站数据库课0