SEQUENCE, INDEX, SYNONYM - ORACLE

OTHER DATABASE OBJECTS:
----------------------
SEQUENCE
INDEX
SYNONYM

SEQUENCE - will not generate duplicate value.

create sequence s1
start with
increment by
minvalue //50
maxvalue //200, if u dont specify then maxvalue is unlimited.
cache/nochache
cycle/nocycle // default is nocycle

sequence is a sharable object.
Minvalue is always associated with CYCLE. once maxvalue is reached. Orcale check for CYCLE is enabled are not.
It its enabled then starts from minvalue (ex:51).

Everytime you are insert record, orcale look in data dictionary and increment lastest value with 1. Performance degradation.
Oracle highly recommends to enable CACHE. IF enabled, then oracle brings nextval from Buffer. But not from Data Dictionary, Since
you provided cache value as (ex:10). That means buffer stores next 10 values.

create sequence s1
start with 100
increment by 1
minvalue 50
maxvalue 110
cache 2
cycle;

desc user_sequences;

select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,LAST_NUMBER from user_sequences where SEQUENCE_NAME='s1';

insert into t22 values (s1.nextval,1000);

DROP Sequence s1;

INDEX:
------
INDEX - is a schema Object. Is used for faster data retrival.
Oracle server will automatically manages, when ever there is updates in table.
Recreating the index is faster than updation of index column(means the column used in table on which u created index).
Do not create index on very small table. Peformance degradation.

create index in_dex on emp(employee_id);
exec dbms_stats.gather_table_stats('user30','emp');

* If you dont use index column in where clause, then oracle will go for full table scan.

select index_name,table_name from user_indexes where index_name='in_dex';

how to create Composite index key?
create index in_dex on emp(employee_id,name);

SYNONYM
-------
Private synonym
Public synonym

synonym is just a pointer to a table. used to give shorter name.
if you dont give PUBLIC keyword then it belongs to private synonym.

public:
create public synonym s1 for tablename;
private:
create synonym s1 for tablename;