精华内容
下载资源
问答
  • sys_guid:-- SYS_GUID MULTI SESSION TESTdrop table t_raw;drop table tmp1;drop table tmp2;drop table tmp3;drop table tmp4;create table tmp1 cache as select 1 dummy from dual connect by level ;create ...

    Hi Andrew,

    thank you very much, I remove append and I made my tests again:

    sequence:-- SEQUENCE MULTI SESSION TESTdrop table t_seq;drop table tmp1;drop table tmp2;drop table tmp3;drop table tmp4;create table tmp1 cache as select 1 dummy from dual connect by level < 1000000;create table tmp2 cache as select 1 dummy from tmp1;create table tmp3 cache as select 1 dummy from tmp1; create table tmp4 cache as select 1 dummy from tmp1;drop sequence seq1 ;  create sequence seq1 start with 100000000 ; -- default 20 cache    create table t_seq      ( id     number(9) primary key      , filler varchar2(1000)      );  alter system switch logfile;alter system checkpoint;alter system flush buffer_cache;select /*+ full(tmp1) */ count(*) from tmp1; -- to make sure table is in buffer_cacheselect /*+ full(tmp2) */ count(*) from tmp2; -- to make sure table is in buffer_cacheselect /*+ full(tmp3) */ count(*) from tmp3; -- to make sure table is in buffer_cacheselect /*+ full(tmp4) */ count(*) from tmp4; -- to make sure table is in buffer_cachedrop table tmp_log;create table tmp_log(mydata varchar2(4000), optime timestamp);create or replace PROCEDURE    sp_log(p varchar2) as  PRAGMA AUTONOMOUS_TRANSACTION;begin  insert into tmp_log values (p , systimestamp);  commit;end;/create or replace procedure sp_test_seq(p number) asbegin  sp_log('START ' || p);    if p = 1 then    insert  into t_seq        select seq1.nextval ,'sdfsf' from tmp1;     elsif p = 2 then    insert into t_seq        select seq1.nextval ,'sdfsf' from tmp2;     elsif p = 3 then    insert  into t_seq        select seq1.nextval ,'sdfsf' from tmp3;     elsif p = 4 then    insert into t_seq        select seq1.nextval ,'sdfsf' from tmp4;     end if;  commit;     sp_log('END ' || p);end;/declare  x_time date := sysdate + 1/1440;begin  dbms_scheduler.create_job(job_name => 'TEST_SEQ1',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_seq(1); end;',                            enabled=> true,                            start_date=> x_time                          );  dbms_scheduler.create_job(job_name => 'TEST_SEQ2',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_seq(2); end;',                            enabled=> true,                            start_date=> x_time                          );  dbms_scheduler.create_job(job_name => 'TEST_SEQ3',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_seq(3); end;',                            enabled=> true,                            start_date=> x_time                          );  dbms_scheduler.create_job(job_name => 'TEST_SEQ4',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_seq(4); end;',                            enabled=> true,                            start_date=> x_time                          );end;/select * from user_scheduler_jobs where job_name like 'TEST%';select * from tmp_log order by optime ;START 2 26/11/2016 09.03.46.051495000 AMSTART 4 26/11/2016 09.03.46.061503000 AMSTART 3 26/11/2016 09.03.46.063793000 AMSTART 1 26/11/2016 09.03.46.065353000 AMEND 3  26/11/2016 09.06.44.423445000 AMEND 1  26/11/2016 09.06.44.425813000 AMEND 2  26/11/2016 09.06.44.587602000 AMEND 4  26/11/2016 09.06.44.589351000 AM

    so the shortest one: 178 seconds  and the longest one is same 178 seconds. there were too many concurrency  event at the sessions.

    sys_guid:-- SYS_GUID MULTI SESSION TESTdrop table t_raw;drop table tmp1;drop table tmp2;drop table tmp3;drop table tmp4;create table tmp1 cache as select 1 dummy from dual connect by level < 1000000;create table tmp2 cache as select 1 dummy from tmp1;create table tmp3 cache as select 1 dummy from tmp1; create table tmp4 cache as select 1 dummy from tmp1;create table t_raw      ( id     raw(16) primary key      , filler varchar2(1000)      );alter system switch logfile;alter system checkpoint;alter system flush buffer_cache;select /*+ full(tmp1) */ count(*) from tmp1; -- to make sure table is in buffer_cacheselect /*+ full(tmp2) */ count(*) from tmp2; -- to make sure table is in buffer_cacheselect /*+ full(tmp3) */ count(*) from tmp3; -- to make sure table is in buffer_cacheselect /*+ full(tmp4) */ count(*) from tmp4; -- to make sure table is in buffer_cachedrop table tmp_log;create table tmp_log(mydata varchar2(4000), optime timestamp);create or replace PROCEDURE    sp_log(p varchar2) as  PRAGMA AUTONOMOUS_TRANSACTION;begin  insert into tmp_log values (p , systimestamp);  commit;end;/create or replace procedure sp_test_guid(p number) asbegin  sp_log('START ' || p);    if p = 1 then    insert  into t_raw        select sys_guid() ,'sdfsf' from tmp1;     elsif p = 2 then    insert  into t_raw        select sys_guid() ,'sdfsf' from tmp2;     elsif p = 3 then    insert  into t_raw        select sys_guid() ,'sdfsf' from tmp3;     elsif p = 4 then    insert into t_raw        select sys_guid() ,'sdfsf' from tmp4;     end if;  commit;     sp_log('END ' || p);end;/declare  x_time date := sysdate + 1/1440;begin  dbms_scheduler.create_job(job_name => 'TEST_GUID1',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_guid(1); end;',                            enabled=> true,                            start_date=> x_time                          );  dbms_scheduler.create_job(job_name => 'TEST_GUID2',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_guid(2); end;',                            enabled=> true,                            start_date=> x_time                          );  dbms_scheduler.create_job(job_name => 'TEST_GUID3',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_guid(3); end;',                            enabled=> true,                            start_date=> x_time                          );  dbms_scheduler.create_job(job_name => 'TEST_GUID4',                            job_type => 'PLSQL_BLOCK',                            job_action => 'begin sp_test_guid(4); end;',                            enabled=> true,                            start_date=> x_time                          );end;/select * from user_scheduler_jobs where job_name like 'TEST%';select * from tmp_log order by optime ;START 4 26/11/2016 09.09.27.068091000 AMSTART 2 26/11/2016 09.09.27.118555000 AMSTART 1 26/11/2016 09.09.27.834022000 AMSTART 3 26/11/2016 09.09.27.838218000 AMEND 3  26/11/2016 09.09.31.322627000 AMEND 2  26/11/2016 09.09.32.905619000 AMEND 4  26/11/2016 09.09.32.939839000 AMEND 1  26/11/2016 09.09.33.647524000 AM

    the shortest one: 3.5 seconds, the longest one: 5.8 seconds.

    展开全文
  • 在oracle8i以后提供了一个生成不重复的数据的一个函数sys_guid()一共32位,生成的依据主要是时间和机器码,具有世界唯一性,类似于java中的UUID(都是世界唯一的)。SYS_GUIDSYS_GUID同Oracle管理员所使用的传统的序列...

    在oracle8i以后提供了一个生成不重复的数据的一个函数sys_guid()一共32位,生成的依据主要是时间和机器码,具有世界唯一性,类似于java中的UUID(都是世界唯一的)。

    SYS_GUID

    SYS_GUID同Oracle管理员所使用的传统的序列(sequence)相比具有诸多优势。一个序列生成器只是简单地创建从给定的起点开始的一系列整数值,而且它被用在选择陈述式的时候自动地递增该系列。

    序列生成器所生成的数字只能保证在单个实例里是唯一的,这就不适合将它用作并行或者远程环境里的主关键字,因为各自环境里的序列可能会生成相同的数字,从而导致冲突的发生。SYS_GUID会保证它创建的标识符在每个数据库 里都是唯一的。

    此外,序列必须是DML陈述式的一部分,因此它需要一个到数据库的往返过程(否则它就不能保证其值是唯一的)。SYS_GUID源自不需要对数据库进行访问的时间戳和机器标识符,这就节省了查询的消耗。

    使用

    SYS_GUID() 生成32位的唯一编码。来生成唯一主键

    例如:

    create table test

    (

    id raw(16) default sys_guid() primary key,

    name varchar2(100)

    );

    insert into test values(sys_guid(),'t1');

    这样就能生成唯一的主键id了,插入数据时不用插入id列。

    SYS_GUID作为一个主关键字?

    create table use_seq_table(id integer );

    create sequence use_seq_sequence;

    insert into use_seq_table values (use_seq_sequence_value.nextval );

    create table use_guid_table(id raw (16));

    insert into use_guid_table(sys_guid());

    很多应用程序都依靠序列生成器来创建数据行的主关键字,这些数据行没有一个明显的主值,这也就是说,在这样的数据集里一条记录的创建就会让数据列发生改变。因此,管理员可能会对在表格中将SYS_GUID用作主关键字而不使用序列数感兴趣。这在对象在不同机器的不同数据库里生成以及需要在后来合并到一起的情况下很有用。

    但是,SYS_GUID所生成的值是一个16位的原始值。序列所生成的整数不会使用16位(的值),除非它达到了10的30次方(每个字节有两位),而且数字是相当独特的:

    SQL>select dump (123456789012345678901234567890) from dual ;

    较短的值就意味着用于表格和索引的存储空间更少,以及查询访问的时间更短。

    使用SYS_GUID或者序列会在数据库使用周期里的某些地方造成性能上的消耗,对于SYS_GUID而言,性能上的影响在查询时间和 创建时间上(在表格里要创建更多的块和索引以容纳数据)。对序列而言,性能上的影响在查询期间,在这个时候,SGA序列的缓冲区被用光。在缺省情况下,一 个序列一次会缓冲20个值。如果数据库没有使用这些值就关闭了,它们就会被丢失。

    SYS_GUID生成的值的另一个显著的不足之处是,管理这些值会变得困难得多。你必须(手动)输入它们或者通过脚本来填充它们,或者将它们作为Web参数来传递。

    出于这些原因,将SYS_GUID作为一个主关键字不是一个很好主意,除非是在一个并行的环境里或者希望避免使用管理序列生成器的情况下。

    总结

    相比于JAVA的uuid,SYS_GUID优点就是生成的字符串是唯一的,但其和UUID有同样的弊端:生成的序列过长并且没有规律不方便记忆。SYS_GUID的应用场景是在于:当数据库某字段设置为唯一,则可用次生成(比如主键)。

    【编辑推荐】

    【责任编辑:赵宁宁 TEL:(010)68476606】

    点赞 0

    展开全文
  • I am looking for a way to get hibernate to use oracle's SYS_GUID() function when inserting new rows. Currently my DB tables have SYS_GUID() as the default so if hibernate simply generated SQL that omi...

    I am looking for a way to get hibernate to use oracle's SYS_GUID() function when inserting new rows. Currently my DB tables have SYS_GUID() as the default so if hibernate simply generated SQL that omited the value it should work.

    I have everything working, but it is currently generating the UUID/GUID in code using the system-uuid generator:

    @Id

    @GeneratedValue(generator = "system-uuid")

    @GenericGenerator(name = "system-uuid", strategy = "uuid")

    @Column(name = "PRODUCT_ID", unique = true, nullable = false)

    public String getId() {

    return this.productId;

    }

    This is fine, but I would prefer that the guids were generated by the database so they will be sequential and potentially have better performance. Plus I would just like to know how to configure this.

    I am using annotations for configuration, but xml configuration examples are awesome as well.

    Here is a sample table definition (in case it matters):

    CREATE TABLE SCHEMA_NAME.PRODUCT

    (

    PRODUCT_ID RAW(16) DEFAULT SYS_GUID() NOT NULL,

    PRODUCT_CODE VARCHAR2(10 CHAR) NOT NULL,

    PRODUCT_NAME VARCHAR2(30 CHAR) NOT NULL,

    PRODUCT_DESC VARCHAR2(512 CHAR)

    )

    UPDATE:

    Mat's sollution of using "guid" worked, here is the sql generated:

    Hibernate:

    select rawtohex(sys_guid())

    from dual

    Hibernate:

    insert into PRODUCT

    (PRODUCT_CODE, PRODUCT_DESC, LOB_ID, PRODUCT_NAME, PROVIDER_ID, PRODUCT_ID)

    values (?, ?, ?, ?, ?, ?)

    It seems that using the columns default value in an insert is not possible, so the choice is between an application generated guid and a database round trip.

    解决方案

    You might be able to use the "guid" generator. See this post from the Hibernate forum. It looks like they added support for Oracle using SYS_GUID() a while back, but the documentation still says they only support SQL Server and MySQL.

    I haven't worked with JPA annotations yet, but here is an example using XML configuration:

    EDIT: In regards to your second question, I think you are asking why Hibernate can't do something like this:

    INSERT INTO PRODUCT (PRODUCT_ID, /* etc */)

    SELECT SYSGUID(), /* etc */

    The reason is that Hibernate must know what the object's ID is. For example, consider the following scenario:

    You create a new Product object and save it. Oracle assigns the ID.

    You detach the Product from the Hibernate session.

    You later re-attach it and make some changes.

    You now want to persist those changes.

    Without knowing the ID, Hibernate can't do this. It needs the ID in order to issue the UPDATE statement. So the implementation of org.hibernate.id.GUIDGenerator has to generate the ID beforehand, and then later on re-use it in the INSERT statement.

    This is the same reason why Hibernate cannot do any batching if you use a database-generated ID (including auto-increment on databases that support it). Using one of the hilo generators, or some other Hibernate-generated ID mechanism, is the only way to get good performance when inserting lots of objects at once.

    展开全文
  • GBase 8s SYS_GUID 函数

    2021-11-30 11:11:13
    GBase 8s SYS_GUID 函数 用法介绍 语法格式 SYS_GUID() SYS_GUID 函数生成并返回一个全球唯一标识符,它由 16 个字节组成。在大多数平台,生成的标识符由主机标识符、执行函数的进程或线程标识符、和进程或线程的一...

    GBase 8s SYS_GUID 函数

    用法介绍

    语法格式

    SYS_GUID()
    

    SYS_GUID 函数生成并返回一个全球唯一标识符,它由 16 个字节组成。在大多数平台,生成的标识符由主机标识符、执行函数的进程或线程标识符、和进程或线程的一个非重复的值(字节序列)。

    样例

    以下示例,用户使用 SYS_GUID() 函数获得一个全球唯一标识符。

    SELECT sys_guid() FROM sysmaster:sysdual;
    
    展开全文
  • create table t(x raw(16...insert into t(x)select sys_guid() from dual connect by rownum <=5;commit;select x,regexp_replace(x,'(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{4})','4321-65-87-9-'...
  • sys_guid()函数解析

    2021-07-16 09:40:44
    是一种生成不重复的数据的一个函数sys_guid()一共32位,生成的依据主要是时间和机器码,具有世界唯一性,类似于java中的UUID(都是世界唯一的)。 其优点就是生成的字符串是唯一的,但其和UUID有同样的弊端:生成的...
  • 很相似只有一位不一样,这是Linux版本的不好的地方,很难分辨,windwos 版本没有这个问题,如果系统是Linux AS4,安装Oracle 9.2... select sys_guid() from dual connect byrownum <10;SYS_GUID()--------------...
  • ORACLE 函数 SYS_GUID()

    2020-11-30 12:50:48
    为啥往表里添加数据不会生效?哪位大神指点下 <p><img alt="" height="322" src="https://img-ask.csdnimg.cn/upload/1606711758195.png" width="459" /></p>
  • codeforces716E (点分治)ProblemDigit Tree 题目大意 给一棵树,有边权1~9. 询问有多少个点对(i,j),将i--j路径上的数字依次连接后所形成新数字可以被k整除.gcd(K,10)=1 解题分析 点分治 ...A手机等的网络...(UIApplic...
  • create table t(x raw(16...insert into t(x)sys_guid() from dual connect by rownum <=5;commit;select x,regexp_replace(x,'(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{4})','4321-65-87-9-') as fm...
  • CREATE or REPLACE FUNCTION "sys_guid"()RETURNS "pg_catalog"."varchar" AS $BODY$DECLARE v_seed_value varchar(32);BEGINselectmd5(inet_client_addr()::varchar ||timeofday() ||inet_server_addr()::varcha...
  • web框架 之 Tornado初识 Tornado : tornadoweb server是使用python编写出来的一个轻量级.高可伸缩性和非阻塞IO的Web服务器软件,其特点是采用epoll非阻塞IO,相应快速,可处理数千 ...Android Studio -修改LogCat的...
  • USERID varchar2(50) default SYS_GUID() not null primary key, USERNAME varchar2(20) not null, USERPWD varchar2(30) not null ); insert into T_USER (USERNAME,USERPWD) values('张三','999999');
  • 首先说下问题:需求是在进行批量插入时并返回这些数据的主键id(这里有一个问题就是我这里的主键是利用了sys_guid()生成的,而不是创建了索引生成的)。如果你是创建了索引生成的主键ID,网上确实有解决此问题的方法,...
  • Oracle中的:sys_guid唯一标识和传统的序列sequence的区别 SYS_GUID (),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成。例如:SELECT sys_guid() from dual Oracle8i...
  • select substr(sys_guid(),1,8)||'-'||substr(sys_guid(),9,4)||'-'||substr(sys_guid(),13,4)||'-'||substr(sys_guid(),17,4)||'-'||substr(sys_guid(),20,12) FROM table ———————————————— 版权...
  • Oracle提供的函数 sys_guid() 用于获取32位uuid,简单使用为 select sys_guid() from dual; 是一种生成不重复的数据的一个函数sys_guid()一共32位,生成的依据主要是时间和机器码,具有世界唯一性,类似于java中的...
  • oralce数据库中sys_guid()和newid() 使用NEWID() 生成guid SELECT NEWID() FROM DUAL; 查询出的ID有分隔符 ORACLE也有相同的函数,sys_guid() ,但是生成的格式跟newid()中的有区别。 sys_guid() 生成的guid不...
  • select sys_guid() from dual

    2020-10-22 17:37:42
    在Oracle数据库内有一种特殊的表Dual。Dual表是Oracle中的一个实际存在的表,任何用户均可...函数sys_guid()生成不重复的数据优点就是生成的字符串是唯一的。 select sys_guid() from dual // 获取唯一的字符串 ...
  • Oracle中可以用SYS_GUID()来生成一个guidSELECT sys_guid() FROM DUAL 可以获得一条guid 具体在SQL语句中可以这样使用:insert into table_name(g_uig, 其他的列名) values(sys_guid(), 其他列名的值)如果要像SQL的...
  • Oracle 中的sys_guid()

    千次阅读 2020-03-13 16:13:22
    Oracle 中的sys_guid() sys_guid(),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个十六进制32位的全球唯一的标识符 。在大多数平台,生成的标识符由主机标符,执行函数的进程或者线程标识符,和进程或线程的一个...
  • 说明:oracle8i版本以后提供了一个生成不重复的数据的函数sys_guid() 样式:一共32位,生成的依据主要是时间和机器码,具有世界唯一性 (和java中的UUID类似) 特点:世界唯一 语句: select sys_guid() from dual ;...
  • 我使用SYS_GUID()从sql端生成uuid值 . 现在我从jpa中获取异常,同时尝试从表中读取uuid由sys_guid()填充的表 .我应该如何从sql生成uuid所以我可以使用java.util.UUID对象从JPA中读取它?我正在使用oracle DB 11g...
  • pgsql -- sys_guid()

    2020-03-12 11:04:52
    数据库查询sys_guid(): select sys_guid(); 使用: insert into user (id, username) values (sys_guid(),‘刘德华’);
  • Oracle 表主键 sys_guid()

    2019-09-26 13:27:15
    SYS_GUID (),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成。在大多数平台,生成的标识符由主机标符,执行函数的进程或者线程标识符,和进程或线程的一个非重复的值(字节...
  • 之前用过sys_guid()方法生成过32位id,时间一长忘掉了。正所谓'好记性不如烂笔头',今天记一记。 正题 select sys_guid() from dual; 可以看到,直接乱码了。 原因:SYS_GUID 以16位RAW类型值形式返回一个...
  • postgresql 函数 sys_guid()

    千次阅读 2019-07-03 09:34:47
    CREATE or REPLACE FUNCTION "sys_guid"() RETURNS "pg_catalog"."varchar" AS $BODY$ DECLARE v_seed_value varchar(32); BEGIN select md5( inet_client_addr()::varchar || timeofday() || inet_server...
  • 解决Oracle - sys_guid() 函数乱码问题

    千次阅读 2020-01-03 09:45:34
    SYS_GUID () 是Oracle 8i 后提供的函数。 SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成。在大多数平台,生成的标识符由主机标符,执行函数的进程或者线程标识符,和进程或线程的一个非重复的值...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 11,886
精华内容 4,754
关键字:

SYS_GUID()