Chapter 16 class notes and exercises on sequence, index and synonyms

- sequence: usually used to generate the primary key value, which can be increased or decreased automatically

  • Create sequence

    --  Simple creation
        create sequence emp_seq;
    --  Complex creation
    --  Specify all property value creation
        create sequence emp_seq
               start with 1 --Value from 1
               increment by 1 --Add 1 at a time
               minvalue 1 --Minimum 1
               maxvalue 9999999999 --Maximum 999999999
               nocycle --Don't recycle.
               cache 20; --Cache 20 values
    --  currval and nextval Pseudo column
        select emp_seq.nextval from dual;--Must be used first nextval´╝îIn execution currval
        select emp_seq.currval from dual;
  • Usage sequence

    select * from emp;
    insert into  emp(empno) values (emp_seq.nextval);    
  • Modified sequence

    --  except start with It can't be changed. Everything else can be changed
    alter sequence emp_seq
          increment by 2
          minvalue 5--Cannot be greater than the current value
          maxvalue 10;--Cannot be less than the current value
  • Application of rowid

  • Deletion sequence

    drop sequence emp_seq;

– index: usually used to optimize query speed

  • Create index

    --  Automatic creation
    --  Manually create
    --  single column
        create index emp_ename_index on emp(ename);
    --  Composite index
        create index emp_deptno_job_index on emp(deptno,job);
  • Test index

    1.Create table emp_bak,copy emp Table structure, and all data
    create table emp_bak as select * from emp; 
    2.Batch add data to emp_bak In the table(Unit: million)
    insert into emp_bak select * from emp_bak;     --Multiple repetitions, current 3670016
    3.take empno Column's data is updated to a unique value
    update emp_bak set empno = rownum;             --The first way
    update emp_bak set empno = emp_seq.nextval;    --The second way
    alter table emp_bak modify(empno number(10));
    --primary empno Length is 4, accuracy is not enough, change to length 10
    4.Test efficiency--(With the help of F5(explain plan window)Window analysis SQL Statement execution efficiency)
    select * from emp_bak where empno=3000000;     --No index 0.12 About
    create index emp_empno_index on emp_bak(empno);--Establish empno Column index
    select * from emp_bak where empno=3000000;     --Index 0.015 About
  • Delete index
    drop index emp_empno_index;

– data dictionary

Select * from user menu tables; -- query table

Select * from user & constraints; -- Query constraints
 Select * from user \ cons \ columns; -- Query constraints and related columns

Select * from user [indexes; -- query all indexes of the current user
 Select * from user \ ind \ columns; -- query all indexes and related columns of the current user


  • Create Synonyms

    --Create Synonyms (private private synonym :Belongs to the current user scott)
    create synonym e for emp;--Equivalent to emp Table alias
    --Insufficient permissions, sys To grant authorization
    grant create synonym to scott;
    select * from scott.emp;
    select * from scott.e;
    --Create synonyms( public Public synonym: belongs to a common user, not a specified user)
    create public synonym a for emp;
    --Insufficient permissions, sys To grant authorization
    grant create public synonym to scott;
  • test

    select * from a;
  • drop synonym

    drop synonym e;

Tags: less SQL

Posted on Sun, 12 Jan 2020 10:45:56 -0500 by robos99