===========================================================
如何sql查询出连续号码段
===========================================================
昨天在itpub看到这个帖子, 问题觉得有意思,, 就仔细想了想. 也给出了一种解决办法..:-)
问题求助,请高手指点..
我有一个表结构，
fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125
(第二个字段内可能是连续的数据，可能存在断点。)
怎样能查询出来这样的结果,查询出连续的记录来。
就像下面的这样？
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125
方法一: 引用自hmxxyy.
SQL> select * from gap;
ID SEQ
---------- ----------
1 1
1 4
1 5
1 8
2 1
2 2
2 9
select res1.id, res2.seq str, res1.seq end
from (
select rownum rn, c.*
from (
select *
from gap a
where not exists (
select null from gap b where b.id = a.id and a.seq = b.seq - 1
)
order by id, seq
) c
) res1, (
select rownum rn, d.*
from (
select *
from gap a
where not exists (
select null from gap b where b.id = a.id and a.seq = b.seq + 1
)
order by id, seq
) d
) res2
where res1.id = res2.id
and res1.rn = res2.rn
/
ID STR END
--------- ---------- ----------
1 1 1
1 4 5
1 8 8
2 1 2
2 9 9
方法二: 使用lag/lead分析函数进行处理.. 楼上的方法确实挺好用就是觉得表扫描/表连接比较多, 可能数据量大了. 速度会比较慢, 当然我的这种方法由于使用分析函数使用的比较频繁.所以排序量可能比上一种要多..
SQL> select fphm,lpad(kshm,8,'0') kshm
2 from t
3 /
FPHM KSHM
---------- ----------------
2014 00000001
2014 00000002
2014 00000003
2014 00000004
2014 00000005
2014 00000007
2014 00000008
2014 00000009
2013 00000120
2013 00000121
2013 00000122
FPHM KSHM
---------- ----------------
2013 00000124
2013 00000125
13 rows selected.
SQL> set echo on
SQL> @bbb.sql
SQL> select fphm,lpad(kshm,8,'0') start_kshm,lpad(prev_prev_kshm,8,'0') end_kshm
2 from (
3 select fphm,kshm,next_kshm,prev_kshm,
4 lag(kshm,1,null) over (partition by fphm order by kshm )next_next_kshm,
5 lead(kshm,1,null) over (partition by fphm order by kshm ) prev_prev_kshm
6 from (
7 select *
8 from (
9 select fphm,kshm,
10 lead(kshm,1,null) over (partition by fphm order by kshm) next_kshm,
11 lag(kshm,1,null) over (partition by fphm order by kshm) prev_kshm
12 from t
13 )
14 where ( next_kshm - kshm <> 1 or kshm - prev_kshm <> 1 )
15 or ( next_kshm is null or prev_kshm is null )
16 )
17 )
18 where next_kshm - kshm = 1
19 /
FPHM START_KSHM END_KSHM
---------- ---------------- ----------------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009
SQL> spool off
查看全文
jametong
发表于:2005.04.27 21:31
::分类:
(
Oracle tips
)
::阅读:(3588次)
::
评论
(23)
===========================================================
如何确定今天是本月的第几周.
===========================================================
1. 我自己想出来的方法.
思路.
a. 先确定元月1号是星期几. 这样可以退出第二周的第一天从哪天开始.
b. 计算出每个月第一天属于全年的那一周.
c. 这样就可以计算出具体某个日期属于哪一天了^_^.
SQL> @bbb.sql
SQL> select date_col,
2 to_number(to_char(trunc(sysdate,'yyyy'),'d')) year_week_day,
3 first_day_week,week_no,week_no - first_day_week + 1 week_no
4 from (
5 select date_col,
6 to_number(to_char(trunc(sysdate,'mm') + 7 - 1 ,'ww')) first_day_week,
7 to_number(to_char(date_col + 7 - 1,'ww')) week_no
8 from (
9 select trunc(sysdate,'mm') + rownum - 1 date_col
10 from dba_objects
11 where rownum <= to_number(to_char(last_day(sysdate),'dd'))
12 )
13 )
14 /
DATE_COL YEAR_WEEK_DAY FIRST_DAY_WEEK WEEK_NO WEEK_NO
--------- ------------- -------------- ---------- ----------
01-APR-05 7 14 14 1
02-APR-05 7 14 14 1
03-APR-05 7 14 15 2
04-APR-05 7 14 15 2
05-APR-05 7 14 15 2
06-APR-05 7 14 15 2
07-APR-05 7 14 15 2
08-APR-05 7 14 15 2
09-APR-05 7 14 15 2
10-APR-05 7 14 16 3
11-APR-05 7 14 16 3
DATE_COL YEAR_WEEK_DAY FIRST_DAY_WEEK WEEK_NO WEEK_NO
--------- ------------- -------------- ---------- ----------
12-APR-05 7 14 16 3
13-APR-05 7 14 16 3
14-APR-05 7 14 16 3
15-APR-05 7 14 16 3
16-APR-05 7 14 16 3
17-APR-05 7 14 17 4
18-APR-05 7 14 17 4
19-APR-05 7 14 17 4
20-APR-05 7 14 17 4
21-APR-05 7 14 17 4
22-APR-05 7 14 17 4
DATE_COL YEAR_WEEK_DAY FIRST_DAY_WEEK WEEK_NO WEEK_NO
--------- ------------- -------------- ---------- ----------
23-APR-05 7 14 17 4
24-APR-05 7 14 18 5
25-APR-05 7 14 18 5
26-APR-05 7 14 18 5
27-APR-05 7 14 18 5
28-APR-05 7 14 18 5
29-APR-05 7 14 18 5
30-APR-05 7 14 18 5
30 rows selected.
SQL> spool off
2. Tom使用的办法. ( 不得不说这个方法比我的方法要简单多咯:-) )
思路:
a. 取出本月1号的前一个星期天的日期
b. 再取出与该星期天的星期的差距.
SQL> @bbb.sql
SQL> select x,next_day(trunc(x,'mm')-8,'sun') next_day,
2 trunc((x-next_day(trunc(x,'mm')-8,'sun'))/7)+1 "week"
3 from (
4 select trunc(sysdate,'mm') + rownum - 1 x
5 from dba_objects
6 where rownum <= to_number(to_char(last_day(sysdate),'dd'))
7 )
8 /
X NEXT_DAY week
--------- --------- ----------
01-APR-05 27-MAR-05 1
02-APR-05 27-MAR-05 1
03-APR-05 27-MAR-05 2
04-APR-05 27-MAR-05 2
05-APR-05 27-MAR-05 2
06-APR-05 27-MAR-05 2
07-APR-05 27-MAR-05 2
08-APR-05 27-MAR-05 2
09-APR-05 27-MAR-05 2
10-APR-05 27-MAR-05 3
11-APR-05 27-MAR-05 3
X NEXT_DAY week
--------- --------- ----------
12-APR-05 27-MAR-05 3
13-APR-05 27-MAR-05 3
14-APR-05 27-MAR-05 3
15-APR-05 27-MAR-05 3
16-APR-05 27-MAR-05 3
17-APR-05 27-MAR-05 4
18-APR-05 27-MAR-05 4
19-APR-05 27-MAR-05 4
20-APR-05 27-MAR-05 4
21-APR-05 27-MAR-05 4
22-APR-05 27-MAR-05 4
X NEXT_DAY week
--------- --------- ----------
23-APR-05 27-MAR-05 4
24-APR-05 27-MAR-05 5
25-APR-05 27-MAR-05 5
26-APR-05 27-MAR-05 5
27-APR-05 27-MAR-05 5
28-APR-05 27-MAR-05 5
29-APR-05 27-MAR-05 5
30-APR-05 27-MAR-05 5
30 rows selected.
SQL> spool off
jametong
发表于:2005.04.26 05:54
::分类:
(
Oracle tips
)
::阅读:(6738次)
::
评论
(201)
===========================================================
how to use dbms_application_info
===========================================================
1. 使用dbms_application_info设置longops的相关信息, 这样我们就可以使用v$session_longops视图来监控我们的程序执行的进度了^_^.
在第一个session中执行一个执行时间比较长的匿名块, 我们再在另一个session中监控, 这个session的执行进度^_^.
SQL> set echo on
SQL> @bbb.sql
SQL> declare
2 rindex binary_integer;
3 slno binary_integer;
4 total number := 900;
5 sofar number := 0;
6 obj binary_integer;
7 cont binary_integer;
8 begin
9 for i in 1..900 loop
10 sofar := sofar + 1;
11 dbms_application_info.set_session_longops(rindex,slno,
12 'test application',obj,cont,sofar,total,'test','seconds');
13 dbms_lock.sleep(1);
14 end loop;
15 end;
16 /
SQL> set echo on
SQL> @ccc.sql
SQL> col sid format 9999
SQL> col opname format a18
SQL> col target_desc format a10 heading 'target |description '
SQL> col sofar format 9999
SQL> col totalwork format 9999 heading 'total | work '
SQL> col units format a10
SQL> col time_remaining format 9999 heading 'time|remaining'
SQL> col elapsed_seconds format 9999 heading 'elapsed|seconds'
SQL> select sid,opname,target_desc,sofar,totalwork,units,time_remaining,elapse
seconds
2 from v$session_longops
3 /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 14 900 seconds 759 12
SQL> set echo off
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 22 900 seconds 838 21
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 25 900 seconds 840 24
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 28 900 seconds 841 27
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 74 900 seconds 815 73
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 135 900 seconds 754 133
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 137 900 seconds 757 136
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 139 900 seconds 761 139
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 142 900 seconds 758 142
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 360 900 seconds 542 361
SQL> /
target total time elapsed
SID OPNAME descriptio SOFAR work UNITS remaining seconds
----- ------------------ ---------- ----- ------ ---------- --------- -------
10 test application test 472 900 seconds 428 472
SQL>
2. 可以使用dbms_application_info的set module/action模块将我们的对应的trace信息进行过滤, 达到对某一块具体的sql执行语句的分析的功能^_^.
APPNAME mod='test' mh=2662839991 act='first step' ah=3692506248
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=57 oct=42 lid=57 tim=195361146957 hv=1346161232 ad='7b37bf68'
alter session set events '10046 trace name context forever,level 12'
END OF STMT
EXEC #1:c=0,e=169,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=195361129557
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 7736508 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=57 oct=3 lid=57 tim=195368885407 hv=1333943659 ad='7b3732ac'
select * from dual
END OF STMT
PARSE #1:c=0,e=239,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=195368885391
BINDS #1:
EXEC #1:c=0,e=247,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=195368886634
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=4,tim=195368887026
WAIT #1: nam='SQL*Net message from client' ela= 639 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=195368888354
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
*** 2005-04-19 23:19:21.116
WAIT #1: nam='SQL*Net message from client' ela= 18042391 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=31802 op='INDEX FULL SCAN SYS_IOT_TOP_31801 '
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=57 oct=47 lid=57 tim=195386948246 hv=480137194 ad='7b1ffae0'
BEGIN dbms_application_info.set_module('test','second step'); END;
END OF STMT
PARSE #1:c=10014,e=9811,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=195386948228
BINDS #1:
APPNAME mod='test' mh=2662839991 act='second step' ah=1111681585
EXEC #1:c=0,e=704,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=195386950096
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 499 p1=1111838976 p2=1 p3=0
.................
.................
这样我们就可以使用sed/awk编辑对应的trace脚本分析对应模块内的sql语句的具体的trace信息, 将对我们没有用户的trace信息过滤出去^_^.
3. 使用dbms_application_info的set_client_info/module/action作为存储系统全局变量的方式..
SQL> create or replace function get_deptno return number is
2 v_deptno number;
3 s_deptno varchar2(20);
4 begin
5 dbms_application_info.read_client_info(s_deptno);
6 v_deptno := to_number(s_deptno);
7 return v_deptno;
8 end;
9 /
Function created.
SQL> create or replace view v_emp as
2 select * from emp where deptno = get_deptno;
View created.
SQL> select * from v_emp;
no rows selected
SQL> exec dbms_application_info.set_client_info('20');
PL/SQL procedure successfully completed.
SQL> select * from v_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL>
4. dbms_application_info的最基本的功能, 可以告诉我们系统正在做什么, 我们可以通过v$session直接定位相关的module/action的对应的session.
SQL> @sessinfo.sql
SQL> col sid format 9999
SQL> col username format a10
SQL> col client_info format a15
SQL> col module format a15
SQL> col action format a15
SQL> select a.sid,a.username,a.client_info,a.module,a.action
2 from v$session a,v$mystat b
3 where a.sid = b.sid
4 and rownum <= 1
5 /
SID USERNAME CLIENT_INFO MODULE ACTION
----- ---------- --------------- --------------- ---------------
10 SCOTT SQL*Plus
SQL> set echo off
SQL> exec dbms_application_info.set_client_info('jametong');
PL/SQL procedure successfully completed.
SQL> exec dbms_application_info.set_module('test','step one');
PL/SQL procedure successfully completed.
SQL> @sessinfo.sql
SID USERNAME CLIENT_INFO MODULE ACTION
----- ---------- --------------- --------------- ---------------
10 SCOTT jametong test step one
SQL> exec dbms_application_info.set_module('test','step two');
PL/SQL procedure successfully completed.
SQL> @sessinfo.sql
SID USERNAME CLIENT_INFO MODULE ACTION
----- ---------- --------------- --------------- ---------------
10 SCOTT jametong test step two
SQL>
jametong
发表于:2005.04.20 00:04
::分类:
(
Oracle tips
)
::阅读:(578次)
::
评论
(0)
===========================================================
how to user bind var with dbms_job
===========================================================
Subhash -- Thanks for the question regarding "DBMS_JOB -- how to pass parameters to the job", version 8.0.5
originally submitted on 11-Mar-2001 23:13 Eastern US time, last updated 11-Apr-2005 13:18Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)
Hi Tom
I have one procedure execute_job with two in parameters
(batchnumber and loaddate). Procedure as follows :-
create or replace procedure execute_job(batchnumber in number, Loaddate in
varchar2)
is
l_theCursor integer default dbms_sql.open_cursor;
l_status integer;
p_sql varchar2(200);
v_job number;
BEGIN
p_sql :='alter session set nls_date_format= ''dd-mon-yyyy
hh24:mi:ss'' ';
dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
l_status := dbms_sql.execute(l_theCursor);
dbms_sql.close_cursor(l_theCursor);
dbms_job.submit(v_job , 'fileimportedi.LoadFileLater
(batchnumber);', Loaddate, null);
commit;
END;
My problem is :-
when i execute my procedure with following parameters :-
Execute execute_job(347,'07-mar-2001 19:10:10');
Error comes :-
ERROR at line 1:
ORA-06550: line 1, column 117:
PLS-00201: identifier 'BATCHNUMBER' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 72
ORA-06512: at "SYS.DBMS_JOB", line 140
ORA-06512: at "WMS.EXECUTE_JOB2", line 13
ORA-06512: at line 1
If i changed my dbms_job.submit as follows :-
dbms_job.submit(v_job , 'fileimportedi.LoadFileLater(347);',
Loaddate, null);
then it's run successfully(no error comes) and job successfully submitted in
user_jobs table.
+++
Can we pass batchnumber as parameter for another packaged procedure or not ? if
yes then how?
Thanks
Subhash
and we said...
What you should do is to create a parameter table:
create table parameters
( jobid number PRIMARY KEY,
batch number,
<any other inputs here>
);
and you would then:
....
dbms_job.submit(v_job , 'fileimportedi.LoadFileLater(JOB);',loadDate );
insert into parameters(v_job,batchnumber);
......
Every job can have access to its job number as a parameter like that. That
would be used to look up the relevant data. The reason for doing this is so you
are submitting the SAME sql string over and over again to the job queue --
fileimportedi.LoadFileLater(JOB);
and not:
fileimportedi.LoadFileLater(1);
fileimportedi.LoadFileLater(2);
fileimportedi.LoadFileLater(3);
which would tend to trash your shared pool with lots of UNIQUE sql.
Your loadFileLater routine should delete from the parameter upon successful
completion or you could submit:
'fileimportedi.LoadFileLater(job);
declare
l_job number default job;
begin
delete from parameter where jobid = l_job;
end;'
which would do it automatically for you if loadfilelater succeeded (the delete
would be skipped if the job failed)
short of that, you would have to code:
dbms_job.submit(v_job ,
'fileimportedi.LoadFileLater(' || batchNumber || ');',
Loaddate, null);
but that, as i said, would generate UNIQUE sql for each job which is BAD.
Reviews
GOTO a page to Bookmark Review | Bottom | Top
DBMS_JOB May 21, 2002
Reviewer: Ruben from Sanjose, CA
Hi,
I am trying to call a pl/sql procedure from a form, i want the form to unbind
after it makes the call to the pl/sql procedure as the procedure is huge and
takes a lot of time to execute, for this i want to call the procedure in a
DBMS_JOB.Please can you illustrate how to go about doing the same.(lets assume
that the procedure needed to be invoked is temp(a,b)).
Followup:
declare
l_job number;
begin
dbms_job.submit( l_job, 'temp( ' || a || ',' || b || ');' );
commit;
end;
is one way to do it.
If you plan on running temp lots with different inputs do this instead:
create table temp_parms( job_id number primary key, a number, b date );
declare
l_job number;
begin
dbms_job.submit( l_job, 'temp( JOB );' );
insert into temp_parms( l_job, a, b );
commit;
end;
and have TEMP query temp_parms by job_id -- which we passed in -- to get its
parameters (makes better use of binding and that is important)
jametong
发表于:2005.04.13 00:21
::分类:
(
Oracle tips
)
::阅读:(365次)
::
评论
(0)
===========================================================
Problem about small table
===========================================================
昨天出于无聊, 测试了一下关于Oracle9idual表的相关处理.
发现一个问题, 对于很小的表, 使用full table 桑蚕并不是最好的选择..:-)
SQL> create table dual_x(dummy varchar2(1) primary key);
Table created.
SQL> insert into dual_x values('X');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL_X'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用普通的全表扫面, 一次全表扫面需要3个consistent read
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL_X'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
SQL> select /*+index_ffs(a)*/ * from dual_x a;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C
ard=41 Bytes=82)
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C
ard=41 Bytes=82)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
我修改这条语句的执行计划, 强制使用index fast full scan, 得到的结果是相同的, 还是3个consistent read.
我们来给这个表坐一下分析, 统计一下表/索引相关的信息.
SQL> analyze table dual_x compute statistics for table for all indexes;
Table analyzed.
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
SQL> spool off
我们可以看到现在这个语句的执行计划为INDEX FULL SCAN, 对应的io为1个consistent read. 这是我们的这个小表的执行计划才是最优的.
由上面的例子, 我们可以得出两个结论.
1. 小表也需要优化, 特别是dual表, 每天使用的频率相当高, 这样调整之后, 可以给系统节约大量io读写.
2. 对于小表而言, 全表扫面不一定是最好的.:-)
之后, 我又对v$bh表做了一下查询, 得到下面的这个结果,
SQL> select index_name from ind where table_name = 'DUAL_X';
INDEX_NAME
------------------------------
SYS_C002610
SQL> select * from v$bh where objd = 31819;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89425 4 xcur 0 0 0
00 Y N N N N N 31819 0
1 89426 1 xcur 0 0 0
00 Y N N N N N 31819 0
SQL> select * from obj where object_name = 'SYS_C002610';
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS_C002610
31820 31820 INDEX
11-APR-05 11-APR-05 2005-04-11:22:25:18 VALID N Y N
SQL> select * from v$bh where objd = 31820;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89441 4 xcur 0 0 0
00 Y N N N N N 31820 0
1 89442 1 xcur 0 0 0
00 Y N N N N N 31820 0
问题: 这个地方只有两条记录, 一个是segment header, 一个是segment data, 但是为什么会产生3个consistent read, 哪位大师能给我解释一下吗:-)
重启数据库之后, 再查询dual_x表, 得到以下的信息, 也就是select * from dual_x 只在v$bh中产生一条记录.
SQL> set autotrace on
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
171 recursive calls
0 db block gets
26 consistent gets
2 physical reads
60 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from v$bh where objd = 31820;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89442 1 xcur 0 0 0
00 Y N N N N N 31820 0
jametong
发表于:2005.04.11 22:22
::分类:
(
Oracle tips
)
::阅读:(381次)
::
评论
(0)
===========================================================
如何使用base64编码解构Oracle rowid信息
===========================================================
1. 先得到base64编码的处理函数.
http://www.experts-exchange.com/Databases/Oracle/Q_21370166.html
base64编码的函数包, 由以上地址下载所得.
Create Or Replace Package B64 Is
B64 Varchar2(64):='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
Function Base64_2Dec(Val Varchar2) Return Number;
Function Dec2_Base64(Val Number) Return Varchar2;
End B64;
/
Create Or Replace Package Body B64 Is
Function Base64_2Dec
(Val Varchar2)
Return Number Is
I Pls_Integer;
J Pls_Integer;
K Pls_Integer:=0;
N Pls_Integer;
V_Out Number(38):=0;
Begin
N:=Length(B64);
For I In Reverse 1..Length(Val) Loop
J:=Instr(B64,Substr(Val,I,1))-1;
If J <0 Then
Raise_Application_Error(-20001,'Invalid Base 64 Number: '||Val);
End If;
V_Out:=V_Out+J*(N**K);
K:=K+1;
End Loop;
Return V_Out;
End;
Function Dec2_Base64
(Val Number)
Return Varchar2 Is
V_In Number;
N Pls_Integer;
V_Out Varchar2(30):='';
Begin
N:=Length(B64);
V_In:=Trunc(Val);
While (V_In>0) Loop
V_Out:=Substr(B64,Mod(V_In,N)+1,1)||V_Out;
V_In:=Trunc(V_In/N);
End Loop;
Return V_Out;
End;
End B64;
/
2. 使用base64解码函数直接解码Oracle Rowid得到Rowid所对应的相关信息:-)
SQL> conn scott/tiger
Connected.
SQL> ed aaa.sql
SQL> @aaa.sql
SQL> select rowid ,
2 B64.Base64_2Dec(substr(rowid,1,6)) object_no ,
3 B64.Base64_2Dec(substr(rowid,7,3)) rel_file_id,
4 B64.Base64_2Dec(substr(rowid,10,6)) block_no,
5 B64.Base64_2Dec(substr(rowid,16,3)) row_no
6 from emp a
7 where rownum <= 5
8 /
ROWID OBJECT_NO REL_FILE_ID BLOCK_NO ROW_NO
------------------ ---------- ----------- ---------- ----------
AAAHIFAABAAAUMSAAA 29189 1 82706 0
AAAHIFAABAAAUMSAAB 29189 1 82706 1
AAAHIFAABAAAUMSAAC 29189 1 82706 2
AAAHIFAABAAAUMSAAD 29189 1 82706 3
AAAHIFAABAAAUMSAAE 29189 1 82706 4
我们再根据对应的数据字典信息, 来确认上面得到的信息.
SQL> select object_id from obj where object_name = 'EMP';
OBJECT_ID
----------
29189
SQL> conn / as sysdba
Connected.
SQL> select owner,segment_name,file_id,block_id,blocks,relative_fno
2 from dba_extents
3 where segment_name = 'EMP'
4 /
OWNER SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO
------ ------------ ---------- ---------- ---------- ------------
SCOTT EMP 1 82705 16 1
SQL>
由于数据段的第一个block为段头(segment header), 实际的数据段从第二个block开始,即 82705 + 1 = 82706
3. 知道对应的记录的相关信息以后, 我们可以直接使用base64编码算法得到Oracle的Rowid
略!!
jametong
发表于:2005.04.07 23:12
::分类:
(
Oracle tips
)
::阅读:(717次)
::
评论
(0)
===========================================================
一个显示对应日期的日历的sql函数^_^.
===========================================================
出于无聊写了这个函数, 供大家赏玩.:-)
上次贴这个数据的时候, 忘记将对应的数据类型贴出来, 这里将他补齐.
create type DATABAK.date_obj as object (
month varchar2(20),
sun char(2),
mon char(2),
tue char(2),
wed char(2),
thu char(2),
fri char(2),
sat char(2)
);
create type DATABAK.date_obj_tab as table of date_obj;
function DATABAK.get_date_str(in_date in date default sysdate)
return date_obj_tab
pipelined
is
date_o date_obj := date_obj(to_char(in_date,'Month'),1,1,1,1,1,1,1);
begin
for rs in (
select
sum(case when date_str = 'sun' then date_d end) sun,
sum(case when date_str = 'mon' then date_d end) mon,
sum(case when date_str = 'tue' then date_d end) tue,
sum(case when date_str = 'wed' then date_d end) wed,
sum(case when date_str = 'thu' then date_d end) thu,
sum(case when date_str = 'fri' then date_d end) fri,
sum(case when date_str = 'sat' then date_d end) sat
from (
select to_number(to_char(date_d,'dd')) date_d,to_char(date_d,'dy') date_str,
to_number(to_char(date_d,'ddd')) - to_number(to_char(date_d,'d')) date_w
from (
select trunc(date_d)+rownum - 1 date_d
from dba_objects a,(
select trunc(in_date,'mm') date_d from dual
) b
where rownum <= to_number(to_char(last_day(date_d),'dd'))
)
)
group by date_w
) loop
date_o.sun := rs.sun;
date_o.mon := rs.mon;
date_o.tue := rs.tue;
date_o.wed := rs.wed;
date_o.thu := rs.thu;
date_o.fri := rs.fri;
date_o.sat := rs.sat;
pipe row (date_o);
end loop;
return;
end;
function DATABAK.get_date_str(in_date in date default sysdate)
return date_obj_tab
pipelined
is
date_o date_obj := date_obj(to_char(in_date,'Month'),1,1,1,1,1,1,1);
begin
for rs in (
select
sum(case when date_str = 'sun' then date_d end) sun,
sum(case when date_str = 'mon' then date_d end) mon,
sum(case when date_str = 'tue' then date_d end) tue,
sum(case when date_str = 'wed' then date_d end) wed,
sum(case when date_str = 'thu' then date_d end) thu,
sum(case when date_str = 'fri' then date_d end) fri,
sum(case when date_str = 'sat' then date_d end) sat
from (
select to_number(to_char(date_d,'dd')) date_d,to_char(date_d,'dy') date_str,
to_number(to_char(date_d,'ddd')) - to_number(to_char(date_d,'d')) date_w
from (
select trunc(date_d)+rownum - 1 date_d
from dba_objects a,(
select trunc(in_date,'mm') date_d from dual
) b
where rownum <= to_number(to_char(last_day(date_d),'dd'))
)
)
group by date_w
) loop
date_o.sun := rs.sun;
date_o.mon := rs.mon;
date_o.tue := rs.tue;
date_o.wed := rs.wed;
date_o.thu := rs.thu;
date_o.fri := rs.fri;
date_o.sat := rs.sat;
pipe row (date_o);
end loop;
return;
end;
实例:
SQL> select * from table(get_date_str());
MONTH SU MO TU WE TH FR SA
-------------------- -- -- -- -- -- -- --
April 1 2
April 3 4 5 6 7 8 9
April 10 11 12 13 14 15 16
April 17 18 19 20 21 22 23
April 24 25 26 27 28 29 30
SQL> select * from table(get_date_str(sysdate-10));
MONTH SU MO TU WE TH FR SA
-------------------- -- -- -- -- -- -- --
March 1 2 3 4 5
March 6 7 8 9 10 11 12
March 13 14 15 16 17 18 19
March 20 21 22 23 24 25 26
March 27 28 29 30 31
SQL>
jametong
发表于:2005.04.07 16:37
::分类:
(
Oracle tips
)
::阅读:(491次)
::
评论
(5)
===========================================================
如何得到钱币的大写形式
===========================================================
今天在cnoug上看到一个朋友问, 如何将对应的数字转换成对应的大写字母, 所以想到了这个..
http://www.cnoug.org/viewthread.php?tid=53058
function getBigMoneyStr(money In number) return varchar2 is
tmp_str varchar2(20);
money_str varchar2(20);
val_j varchar2(20);
len_j number;
k number;
i number;
j number;
m number;
result varchar2(40);
type my_type is varray(20) of varchar2(4);
num constant my_type := my_type('1','2','3','4','5','6','7','8','9','0');
num_str constant my_type := my_type('壹','贰','叁','肆','伍','陆','柒','捌','玖','零');
begin
tmp_str := to_char(money * 100);
len_j := length(tmp_str);
k := 11 - len_j;
result := substr(tmp_str,1,1);
m := 2;
for i in 1..len_j loop
begin
if k = 1 then money_str := '仟';
elsif k = 2 then money_str :='佰';
elsif k = 3 then money_str :='拾';
elsif k = 4 then money_str :='万';
elsif k = 5 then money_str :='仟';
elsif k = 6 then money_str :='佰';
elsif k = 7 then money_str :='拾';
elsif k = 8 then money_str :='元';
elsif k = 9 then money_str :='角';
elsif k = 10 then money_str :='分';
end if;
k := k + 1;
m := i + 1;
val_j := nvl(substr(tmp_str,m,1),'');
if len_j = m + 5 and substr(result,length(result),1) = '0' then
result := substr(result,0,length(result)-1) ||'万0';
end if;
if len_j = m + 1 and substr(result,length(result) ,1) = '0' then
result := substr(result,0,length(result)-1) ||'元0';
end if;
if val_j = '0' and substr(result,length(result),1) = '0' then
goto TheEnd;
end if;
if substr(result,length(result) ,1) != '0' then
result := result || money_str;
end if;
result := result || val_j ;
<<TheEnd>>
null;
end;
end loop;
--除去最后的0
for j in 1..3 loop
begin
if substr(result,length(result),1) = '0' then
result := substr(result,0,length(result) -1);
end if;
end;
end loop;
for i in 1..10 loop
result := replace(result,num(i),num_str(i));
end loop;
return result||'整';
end;
/
例子
SQL> select getBigMoneyStr(2124324.34) from dual;
GETBIGMONEYSTR(2124324.34)
--------------------------------------------------------------------
贰佰壹拾贰万肆仟叁佰贰拾肆元叁角肆分整
SQL> select getBigMoneyStr(2129874.34) from dual;
GETBIGMONEYSTR(2129874.34)
--------------------------------------------------------------------
贰佰壹拾贰万玖仟捌佰柒拾肆元叁角肆分整
SQL>
jametong
发表于:2005.04.07 16:27
::分类:
(
Oracle tips
)
::阅读:(653次)
::
评论
(1)
===========================================================
使用with处理报表处理一例
===========================================================
with sum_obj as(
select
nvl(county_code,'合计') county_code,
sum(case when color_flag = 0 then objective else 0 end) t_red,
sum(case when color_flag = 3 then objective else 0 end) t_yellow,
sum(objective) t_total,
sum(case when kind = 1 and color_flag = 0 then objective else 0 end) a_red,
sum(case when kind = 1 and color_flag = 3 then objective else 0 end) a_yellow,
sum(case when kind = 1 then objective else 0 end) a_total,
sum(case when kind = 2 and color_flag = 0 then objective else 0 end) b_red,
sum(case when kind = 2 and color_flag = 3 then objective else 0 end) b_yellow,
sum(case when kind = 2 then objective else 0 end) b_total
from tjw_core_bind_objective
group by rollup(county_code)
),
sum_real as (
select /*+full(b)*/
nvl(county_code,'合计') county_code,
sum(case when b.march_flag = 0 then 1 else 0 end) t_red,
sum(case when b.march_flag = 3 then 1 else 0 end) t_yellow,
sum(1) t_total,
sum(case when b.kind = 1 and b.march_flag = 0 then 1 else 0 end) a_red,
sum(case when b.kind = 1 and b.march_flag = 3 then 1 else 0 end) a_yellow,
sum(case when b.kind = 1 then 1 else 0 end) a_total,
sum(case when b.kind = 2 and b.march_flag = 0 then 1 else 0 end) b_red,
sum(case when b.kind = 2 and b.march_flag = 3 then 1 else 0 end) b_yellow,
sum(case when b.kind = 2 then 1 else 0 end) b_total
from tjw_core_bind_final a,tjw_core_bind_5 b
where a.user_id = b.user_id
and a.color_flag in (1,2)
and b.march_flag in (0,3)
group by rollup(county_code)
)
select '所有用户',a.county_code,
a.t_red,a.t_red - b.t_red,trunc((a.t_red - b.t_red)/a.t_red * 100, 2 ) t_red_ratio,
a.t_yellow,a.t_yellow - b.t_yellow,trunc((a.t_yellow - b.t_yellow)/a.t_yellow * 100, 2 ) t_yellow_ratio,
a.t_total,a.t_total - b.t_total,trunc((a.t_total - b.t_total)/a.t_total * 100, 2 ) t_total_ratio
from sum_obj a,sum_real b
where a.