-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUBSTR("ENAME",1)='KING')
SQL> create index emp_e_i on emp(substr(ename,1));
Index created.
SQL> select * from emp where substr(ename,1)='KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 1426330053
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | EMP_E_I | 1 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SUBSTR("ENAME",1)='KING')
8.序列
自动产生唯一值
是一个共享对象
典型的用于创建主键值
可替代应用程序代码
如果将序列值缓存在内存中可以提交访问效率
create sequence ...
increment by ...
start with ...
...
SQL> create sequence s increment by 1 start with 50;
Sequence created.
SQL> insert into dept values(s.nextval,'D'||s.nextval,'LL'||s.nextval);
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
54
56 TX
50 D50 LL50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select s.currval from dual;
CURRVAL
----------
50
SQL> desc user_sequences
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SQL> select s.currval+increment_by from user_sequences where sequence_name='S';
S.CURRVAL+INCREMENT_BY
----------------------
51
SQL> alter sequence s cycle cache 10;
Sequence altered.
SQL> select s.currval+increment_by from user_sequences where sequence_name='S';
S.CURRVAL+INCREMENT_BY
----------------------
51
SQL> select last_number from user_sequences where sequence_name='S';
LAST_NUMBER
-----------
51
SQL> insert into dept values(s.nextval,'D'||s.nextval,'LL'||s.nextval);
1 row created.
SQL> select last_number from user_sequences where sequence_name='S';
LAST_NUMBER
-----------
61
SQL> select s.currval+increment_by from user_sequences where sequence_name='S';
S.CURRVAL+INCREMENT_BY
----------------------
52
SQL> alter sequence s maxvalue 55;
Sequence altered.
SQL> create table dept1 as select * from dept;
Table created.
SQL> insert into dept1 values(s.nextval,'D'||s.nextval,'LL'||s.nextval);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row |