一、利用Oracle分析函数实现多行数据合并为一行
1、 demo场景以oracle自带库中的表emp为例
2、 select ename deptno from emp order by deptno;
3、 ENAME DEPTNO CLARK KING MILLER smITH ADAMS FORD SCOTT JONES ALLEN BLAKE MARTIN JAMES TURNER WARD
4、现在想要将同一部门的人给合并成一行记录如何做呢？如下
5、 ENAME DEPTNO CLARK KING MILLER ADAMS FORD JONES SCOTT smITH ALLEN BLAKE JAMES MARTIN TURNER WARD
6、通常我们都是自己写函数或在程序中处理这里我们利用oracle自带的分析函数row_number()和sys_connect_by_path来进行sql语句层面的多行到单行的合并并且效率会非常高
7、对deptno进行row_number()按ename排位并打上排位号
8、 select deptno ename row_number() over(partition by deptno order by deptno ename) rank
9、 from emp order by deptno ename;
10、 DEPTNO ENAME RANK CLARK KING MILLER ADAMS FORD JONES SCOTT smITH ALLEN BLAKE JAMES MARTIN TURNER WARD可看出经过row_number()后部门人已经按部门和人名进行了排序并打上了一个位置字段rank
11、利用oracle的递归查询connect by进行表内递归并通过sys_connect_by_path进行父子数据追溯串的构造这里要针对ename字段进行构造使之合并在一个字段内（数据很多只截取部分）
12、 select deptno ename rank level as curr_level
13、 ltrim(sys_connect_by_path(ename)) ename_path from(
14、 select deptno ename row_number() over(partition by deptno order by deptno ename) rank
15、 from emp order by deptno ename) connect by deptno= prior deptno and rank= prior rank;
16、各部门递归后的数据量都是(+n)/* n即 deptno=数据量(+)/*=;
17、 deptno=数据量(+)/*=; deptno=数据量(+)/*=;
18、 DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH CLARK CLARK KING CLARK KING MILLER CLARK KING MILLER KING KING MILLER KING MILLER MILLER MILLER
19、 DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH ADAMS ADAMS FORD ADAMS FORD JONES ADAMS FORD JONES SCOTT ADAMS FORD JONES SCOTT smITH ADAMS FORD JONES SCOTT smITH FORD FORD JONES FORD JONES SCOTT FORD JONES SCOTT smITH FORD JONES SCOTT smITH JONES JONES SCOTT JONES SCOTT smITH JONES SCOTT smITH SCOTT SCOTT smITH SCOTT smITH smITH smITH
20、这里我们仅列出deptno=的至此我们应该能否发现一些线索了即每个部门中 curr_level最高的那行有我们所需要的数据那后面该怎么办取出那个数据？对了继续用row_number()进行排位标记然后再按排位标记取出即可
21、对deptno继续进行row_number()按curr_level排位
22、 select deptno ename_path row_number() over(partition by deptno order by deptno curr_level desc) ename_path_rank from(select deptno ename rank level as curr_level
23、 ltrim(sys_connect_by_path(ename)) ename_path from(
24、 select deptno ename row_number() over(partition by deptno order by deptno ename) rank
25、 from emp order by deptno ename) connect by deptno= prior deptno and rank= prior rank);
26、 DEPTNO ENAME_PATH ENAME_PATH_RANK CLARK KING MILLER CLARK KING KING MILLER CLARK KING MILLER DEPTNO ENAME_PATH ENAME_PATH_RANK ADAMS FORD JONES SCOTT smITH ADAMS FORD JONES SCOTT FORD JONES SCOTT smITH ADAMS FORD JONES FORD JONES SCOTT JONES SCOTT smITH ADAMS FORD FORD JONES SCOTT smITH JONES SCOTT ADAMS JONES smITH SCOTT FORD这里还是仅列出deptno为的至此应该很明了了在进行一次查询取ename_path_rank为的即可获得我们想要的结果
27、获取想要排位的数据即得部门下所有人多行到单行的合并
28、 select deptno ename_path from(select deptno ename_path
29、 row_number() over(partition by deptno order by deptno curr_level desc) ename_path_rank
30、 from(select deptno ename rank level as curr_level
31、 ltrim(sys_connect_by_path(ename)) ename_path from(
32、 select deptno ename row_number() over(partition by deptno order by deptno ename) rank
33、 from emp order by deptno ename) connect by deptno= prior deptno and rank= prior rank))
二、ORACLE select 递归查询
1、START WITH定义数据行查询的初始起点；
2、CONNECT BY prior定义表中的各个行是如何联系的；
3、connect by后面的"prior"如果缺省，则只能查询到符合条件的起始行，并不进行递归查询；
4、条件2：col_1= col_2，col_1是父键（它标识父），col_2是子键（它标识子）。
5、条件3过滤递归前相应节点及其子节点，如果上级节点不满足则下级节点自动过滤掉；
6、条件4过滤递归后相应的节点或子节点，如果上级节点不满足则下级结点自动提升一级。
7、CURRVAL AND NEXTVAL使用序列号的保留字
8、LEVEL显示层次树中特定行的层次或级别
9、CONNECT_BY_ROOT返回当前层的根节点(当前行数据所对应的最高等级节点的内容)
10、SYS_CONNECT_BY_PATH(<column>,<char>)函数实现将从父节点到当前行内容以"path"或者层次元素列表的形式显示出来
11、CONNECT_BY_ISCYCLE须带参数NOCYCLE，当前行中引用了某个父亲节点的内容并在树中出现了循环，如果循环显示"1"，否则就显示"0"。
12、CONNECT_BY_ISLEAF判断当前行是不是叶子。如果是叶子显示"1"，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示"0"
13、而在 Oracle 10g中，只要指定"NOCYCLE"就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE，如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示"1"，否则就显示"0"。
14、create table test(superid varchar2(20),id varchar2(20),mc varchar2(20));
15、insert into test values('0','1','A1');
16、insert into test values('0','2','A2');
17、insert into test values('1','11','A11');
18、insert into test values('1','12','A12');
19、insert into test values('2','21','A21');
20、insert into test values('2','22','A22');
21、insert into test values('11','111','A111');
22、insert into test values('11','112','A112');
23、insert into test values('12','121','A121');
24、insert into test values('12','122','A122');
25、insert into test values('21','211','A211');
26、insert into test values('21','212','A212');
27、insert into test values('22','221','A221');
28、insert into test values('22','222','A222');
29、select level||'级' jc,lpad('',(level-1)*4)||id id,mc
30、start with superid='0' connect by prior id=superid;
31、select level||'级' jc,connect_by_isleaf mxf,lpad('',(level-1)*4)||id id,mc
32、start with superid='0' connect by prior id=superid;
33、--给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with... connect by...
34、--功能：实现按照superid分组,把id用";"连接起来
35、--实现：以下两个例子都是通过构造2个伪列来实现connect by连接的。
三、Oracle内部函数调用追踪器
1、基于以前开发的一个用于监控线程的CPU使用状况的小工具 TopShow我开发了一个用于追踪Oracle内部函数调用的追踪器——OraTracer你可以用该工具追踪监控Oracle多个内部函数的调用情况还可以尝试探测函数的输入参数的值也可以打印追踪点被触发时的调用堆栈追踪可以设置在整个Oracle进程的级别也可以设置在某个线程以追踪特定的会话
2、捕获oracle整个实例中被执行的SQL语句
3、首先在与可执行文件相同的目录下设置追踪点文件 TracePoints txt内容如下
4、与函数名用空格相隔的数值为探测的参数数量如果再加上*N则表示尝试将双字节数字作为指针对待递归获取其执行的值后面的数字为递归深度例如对于第一个追踪点函数名为 _opiprs探测个参数递归探测指针数据的深度为
5、然后从进程列表中选择 ORACLE EXE不要选择任何线程
6、最后点击 Trace按钮一旦有语句被上述函数调用你就可以从监控窗口看到这些语句
7、[::]User call:_rpisplu(TID:)
8、 select privilege# level from sysauth$ connect by grantee#=prior privilege# and privilege#> start with grantee#=: and privilege#>
9、[::]User call:_rpisplu(TID:)
10、 alter session set NLS_LANGUAGE= AMERICAN NLS_TERRITORY= AMERICA NLS_CURRENCY=$ NLS_ISO_CURRENCY= AMERICA NLS_NUMERIC_CHARACTERS= NLS_DATE_FORMAT= DD MON RR NLS_DATE_LANGUAGE= AMERICAN NLS_SORT= BINARY
11、[::]User call:_opiprs(TID:)
12、 alter session set NLS_LANGUAGE= AMERICAN NLS_TERRITORY= AMERICA NLS_CURRENCY=$ NLS_ISO_CURRENCY= AMERICA NLS_NUMERIC_CHARACTERS= NLS_DATE_FORMAT= DD MON RR NLS_DATE_LANGUAGE= AMERICAN NLS_SORT= BINARY
13、[::]User call:_rpisplu(TID:)
14、 select sysdate+/(*) from dual
15、[::]User call:_rpisplu(TID:)
16、 DECLARE job BINARY_INTEGER:=:job; next_date DATE:=:mydate; broken BOOLEAN:= FALSE; BEGIN EMD_MAINTENANCE EXECUTE_EM_DBMS_JOB_PROCS();:mydate:= next_date; IF broken THEN:b:=; ELSE:b:=; END IF; END;
17、理解SQL是如何被执行计划驱动执行的
18、我们知道查询计划实际上就是驱动Oracle通过特定函数及顺序来获取数据我们可以通过追踪这些函数来理解执行计划
19、首先下载以下文件解压重命名为 TracePoints txt放到OraTracer exe所在目录
20、然后获取到你需要追踪的会话的SPID
21、 HELLODBA>select distinct spid from v$mystat m v$session s v$process p where s sid=m sid and s paddr=p addr;
22、从进程列表中选择ORACLE EXE=>从线程列表中选择TID为的线程=>点击 Trace按钮
23、 HELLODBA>select* from demo t_test where owner= DEMO and object_name like T_TEST%;
24、 OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
25、 LAST_DDL_TIME TIMESTAMP STATUS T G S
26、—————————————————————————————————————–—————————–
27、——–——————————————
28、注意为了避免回滚调用也被追踪你最好在追踪之前先运行一次该语句
29、我们可以从追踪窗口看到数据fetch调用情况
30、[::]User call:_qertbFetchByRowID(TID:)
31、[::]User call:_qerixtFetch(TID:)
32、[::]User call:_qertbFetchByRowID(TID:)
33、[::]User call:_qerixtFetch(TID:)
34、有了这样的追踪记录你可以尝试将他们与执行计划中节点映射
35、 HELLODBA>select* from demo t_test where owner= DEMO and object_name like T_TEST%;
36、———————————————————
37、——————————————————————————————
38、| Id| Operation| Name| Rows| Bytes| Cost(%CPU)| Time|
39、——————————————————————————————
40、|| TABLE ACCESS BY INDEX ROWID| T_TEST|||()|::|==>_qertbFetchByRowID
41、|*| INDEX RANGE SCAN| T_TEST_IDX|||()|::|==>_qerixtFetch
42、打印某个特定函数被调用时的线程调用堆栈
43、我们这里追踪 _kkeAdjSingTabCard设置追踪点
44、函数名后的*N指定输出的调用个数为无限制
45、然后获取到你需要追踪的会话的SPID
46、 HELLODBA>select distinct spid from v$mystat m v$session s v$process p where s sid=m sid and s paddr=p addr;
47、从进程列表中选择ORACLE EXE=>从线程列表中选择TID为的线程=>点击 Trace按钮
48、 HELLODBA>explain plan for select/*+full(t)*/ count(*) from demo t_test t;
49、我们就可以从监控窗口获取到该函数被调用时的整个调用堆栈的情况
50、[::]User call:_kkeAdjSingTabCard(TID:)
51、× d b(ORACLE EXE!_kkoCopyPreds+)
52、× d f c(ORACLE EXE!__PGOSF __apaRequestBindCapture+)
53、× b eb(ORACLE EXE!_kksminimalTypeCheck+)
54、× b ce(ORACLE EXE!_kksSetNLSHandle+)
55、× e(ORACLE EXE!_kxsReleaseRuntimeLock+)
56、× e cf(ORACLE EXE!_kksParseCursor+)
57、× f b(ORACLE EXE!_kksxscpat+)
58、× c b(KERNEL dll!GetModuleFileNameA+)
好了，本文到此结束，如果可以帮助到大家，还望关注本站哦！
