请问,这里的50000是如何估算出来的?谢谢!
解决方案 »
- Oracle 11g 问题求教
- Ora-01722 :無效數字問題
- 这个instead of 触发器有问题吗,郁闷!
- xp sp2系统,应下什么版本的ORACLE,大牛们帮忙给个地址~~~~~~~~
- oracle 字符集问题
- Oracle中怎么把一个日期转为日本和历的格式?
- 我建的DBLINK为什么总是报”ORA-12154: TNS: 无法处理服务名“的错呢?代码如下:
- 谁能回答这个问题?
- 在ORACLE 9I中能否实现SQLSERVER中的FULL OUTER JOIN!!十万火急!!!!
- 各位大大,有谁知道怎么删除一个tablespace中的datafile?
- 一个小问题
- Oracle保存数据,CLOB字段类型都有一个空格是什么原因
所以,我估计,你看到那本书上这样说的时候是有上下文的。
但是有个缺点就是可能会出现序列值存在表的列可能不会连续的
你说50000我觉得有点不可思议 用100 应该就够用了
-- 比如说:我有一张表,每秒钟插入近7条记录:
music@SZTYORA> SELECT max(logid) max_logid,
2 min(logid) min_logid,
3 max(logid)-min(logid) as lines
4 FROM musicloguser_detail
5 WHERE timed >= trunc(sysdate-1)
6 AND timed < trunc(sysdate); MAX_LOGID MIN_LOGID LINES
---------- ---------- ----------
26475360 25887629 587731music@SZTYORA> SELECT COUNT(logid)
2 FROM musicloguser_detail
3 WHERE timed >= trunc(sysdate-1)
4 AND timed < trunc(sysdate);COUNT(LOGID)
------------
587732music@SZTYORA> select 587732/1440/60 from dual;587732/1440/60
--------------
6.8024537music@SZTYORA>-- 这个表的 logid字段,就是用序列号递增的,为了维护每30到60秒 cache此表相关的序列一次的话,
-- 我就应该:
ALTER sequence musicloguser_seq cache 400;
-- cache 400的话,能维持差不多一分钟一次
-- (没有考虑业务高峰期,其实应该考虑业务高峰期每秒插入数据行的条数更准确)
-- 将MUSICLOGUSER_SEQ 每次CACHE 1000个,以提高下面存储过程的运行效率!
ALTER SEQUENCE MUSICLOGUSER_SEQ CACHE 1000;-- 往musicloguser表中插入数据(同时插入相应的日志表)
CREATE OR REPLACE PROCEDURE musicloguser_INS_PROC(v_sxml VARCHAR2, v_timed TIMESTAMP)
IS
v_sxml2 VARCHAR2(4000);
v_a1 VARCHAR2(4000);
v_c1 VARCHAR2(4000);
v_c2 VARCHAR2(4000);
v_e1 VARCHAR2(4000);
v_k VARCHAR2(4000);
v_nm VARCHAR2(4000);
v_s VARCHAR2(4000);
v_v1 VARCHAR2(4000);
v_q2 VARCHAR2(4000);
v_v2 VARCHAR2(4000);
v_v3 VARCHAR2(4000);
v_p2 VARCHAR2(4000);
v_mobile VARCHAR2(4000);
v_mintimed TIMESTAMP(6);
v_logid NUMBER(38,0);
v_cnt NUMBER(18,0);
v_sign NUMBER(18,0);
BEGIN
SELECT musicLogUser_seq.nextval INTO v_logid FROM DUAL; -- 往musicloguser表中插入数据
INSERT INTO musicloguser(logid, sxml, timed) VALUES(v_logid, v_sxml, v_timed);
COMMIT; IF v_sxml like '%&#x%' THEN
v_sxml2 := unistr(REPLACE(REPLACE(REPLACE(v_sxml,';&#x','\'),'&#x','\'),';<','<'));
ELSE
v_sxml2 := v_sxml;
END IF; v_a1 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<a1>.*?</a1>'),'<a1>',''),'</a1>','')),null);
v_c1 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<c1>.*?</c1>'),'<c1>',''),'</c1>','')),null);
v_c2 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<c2>.*?</c2>'),'<c2>',''),'</c2>','')),null);
v_e1 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<e1>.*?</e1>'),'<e1>',''),'</e1>','')),null);
v_k := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<k>.*?</k>'),'<k>',''),'</k>','')),null);
v_nm := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<nm>.*?</nm>'),'<nm>',''),'</nm>','')),null);
v_s := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<s>.*?</s>'),'<s>',''),'</s>','')),null);
v_v1 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<v1>.*?</v1>'),'<v1>',''),'</v1>','')),null);
v_q2 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<q2>.*?</q2>'),'<q2>',''),'</q2>','')),null);
v_v2 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<v2>.*?</v2>'),'<v2>',''),'</v2>','')),null);
v_v3 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<v3>.*?</v3>'),'<v3>',''),'</v3>','')),null);
v_p2 := nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<p2>.*?</p2>'),'<p2>',''),'</p2>','')),null); INSERT INTO musicloguser_detail(C1,R2,C2,K1,E1,U1,T1,K,T,F,G,W,NM,S,V1,O1,FNN,PID,A1,B1,Q2,V2,V3,AC,MN,Z,P2,LOGID,TIMED)
VALUES( v_c1,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<r2>.*?</r2>'),'<r2>',''),'</r2>','')),null),
v_c2,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<k1>.*?</k1>'),'<k1>',''),'</k1>','')),null),
v_e1,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<u1>.*?</u1>'),'<u1>',''),'</u1>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<t1>.*?</t1>'),'<t1>',''),'</t1>','')),null),
v_k,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<t>.*?</t>'),'<t>',''),'</t>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<f>.*?</f>'),'<f>',''),'</f>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<g>.*?</g>'),'<g>',''),'</g>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<w>.*?</w>'),'<w>',''),'</w>','')),null),
v_nm,v_s,v_v1,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<o1>.*?</o1>'),'<o1>',''),'</o1>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<fn>.*?</fn>'),'<fn>',''),'</fn>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<pid>.*?</pid>'),'<pid>',''),'</pid>','')),null),
v_a1,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<b1>.*?</b1>'),'<b1>',''),'</b1>','')),null),
v_q2,v_v2,v_v3,
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<ac>.*?</ac>'),'<ac>',''),'</ac>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<mn>.*?</mn>'),'<mn>',''),'</mn>','')),null),
nvl(trim(replace(replace(REGEXP_SUBSTR(v_sxml2,'<z>.*?</z>'),'<z>',''),'</z>','')),null),
v_p2,v_logid,v_timed); IF v_a1 IS NOT NULL THEN
SELECT COUNT(logid), NVL(SUM(decode(sign,1,1,0)),0) INTO v_cnt, v_sign FROM musicinstalllog WHERE imsi=v_a1;
IF v_cnt = 0 AND v_k = 'userlogin' AND nvl(v_q2,'0')<>'0' AND nvl(v_p2,'0')<>'0' THEN
INSERT INTO musicinstalllog(logid,mobile,imsi,cid,pid,ver,logcreationdate,phonetype,sign)
VALUES (v_logid,v_e1,v_a1,v_q2,v_p2,v_c1,v_timed,v_c2,0);
ELSIF v_cnt <> 0 AND nvl(v_e1,'0')<>'0' AND v_sign=0 THEN
UPDATE musicinstalllog SET mobile=v_e1, logcreationdate=v_timed, logmodifydate=sysdate, sign=1 WHERE imsi = v_a1;
END IF;
END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END;
/