Synonym and index in Oracle, oraclesynonym

Synonym and index in Oracle, oraclesynonym

Notes:

Oracle-Synonyms
-- Use the user name (mode name). Table Name
-- Authorization: grant create synonym to test1 (authorized by the system user ))
-- Private
Create or replace synonym sy_test1 for gcs95.test1;
-- Total
Create public synonym public_sy_test1 for gcs95.test1;
/* Private: other users cannot access */
Select * from sy_test1;
/* Total: All authorized users can access */
Select * from public_sy_test1; -- feature: shields the object owner.
Index:
Definition:
Improve query efficiency

Rowid: physical address of the row in the database table
1> B tree:

It can be said that the final query is rowid
Create index stu_index on table name (column name );
2> reverse key index:
Suitable for scenarios where data is inserted frequently and memory operations can be dispersed;
The query efficiency can also be improved when querying!
Format:
Allocated to the final access
102-201
123-321

Syntax:
Create index index_revers_empno on table name (column name) revers;
3> bitmap index:
Cannot be created on oracle of XE version! Instead of rowid, rowid ing is stored! It means no storage space is occupied!
If a column has a limited data value (repeated values), you can use this column to create a bitmap index,

Table partition:
　　

　　

Code:

1 practice: 2 3 -- synonym 4 5 -- use the user name (mode name ). table Name 6 7 -- authorization: grant create synonym to test1 8 9/* Private: other users cannot access */10 11 select * from sy_test1; 12 13/* Total: all authorized users can access */14 15 select * from public_sy_test1; 16 17 -- feature: shields the object owner, you can directly access this table 18 19 20 21 -- test: create table Depostitor (22 23 actid number not null, 24 25 cardid number not null, 26 27 lastname varchar2 (10) not null, 28 29 firstname varchar2 (10) not null, 30 31 address1 varchar2 (200) not null, 32 33 address2 varchar2 (200), 34 35 address3 varchar2 (200 ), 36 37 blance number (), 38 39 constraint pk_depostor primary key (actid) 40 41); 42 43 -- add B tree index 44 45 create index cd_index on Depostitor (cardid ); drop index cd_index46 47 -- add reverse key index 48 49 create index index_revers_empno on Depostitor (cardid) revers; -- add test data insert Depostitor values (); 50 51 -- select * from depostitor; 52 53 -- cardid queries select * from depostitor where cardid between 1 and 100000;Synonym exercises