Orcale Learning (3)

Today, let's take a look at how to manage database objects in the orcale database.

I. Synonyms

1. Solution

The solution is a collection of database objects. The solution contains various objects (tables, views, indexes, synonyms, sequences, database connections, etc ).

Generally, a user corresponds to a solution. Of course, the user can have other solutions.

2. Synonyms

Synonyms are the aliases of solution objects. They are commonly used to simplify objects and improve Object Access Security. They do not occupy actual storage space.

2.1 public synonyms

A public synonym is owned by a special public user group. All users in the database can use the public synonym.

Syntax: Create [or replace] publice synonym synonym_name [schema.] Object

Note: If the user does not have the permission to reference an object of a public synonym, the user cannot use this synonym.

 --  Authorization  
  Grant     Create    Public  Synonym  To  Scott;
  --  Create a public synonym for the dept table  
  Create     Or     Replace     Public  Synonym pubsyn_dept  For  Dept; 

2.2 solution Synonym

Solution synonyms are owned by the users who created the solution. This user can control whether other Users have the right to use their own solution synonyms.

Syntax: Create [or replace] synonym sysnonym_name [schema.] Object

--Authorization
Grant CreateSynonymToScott;
--Create a solution synonym for a dept table
Create Or ReplaceSynonym syn_deptForDept;
 

2.3 Delete solution Synonyms

Syntax: Drop synonym synonym_name

2.4 delete a public Synonym

Syntax: Drop public synonym synonym_name

3. Sequence

A sequence is a named sequence number generator that can generate a series of sequential integers in a serial manner.

Purpose: primary key, foreign key value application requirements, serial number application requirements, sequence generation and definition content

Syntax: Create sequence sequnce_name

[Start with N1] -- specifies the first serial number to be generated

[Increment by N2] -- specifies the interval between serial numbers. The default value is 1. N2 is positive, and the sequence increments. Otherwise, the sequence decreases.

[{Maxvalue N3 | nomaxvalue}] -- maximum or maximum value that can be generated by a sequence

[{Minvalue N4 | nominvalue}] -- minimum or no minimum value that can be generated by the sequence

[{Cache N5 | nocache}] -- specifies the number of serial numbers that can be pre-allocated in the cache. The default value is 20.

[{Cycle | nocycle}] -- specifies whether to cycle after the maximum or minimum values of the sequence are reached

[Order]; -- specify to generate serial numbers in sequence

3.1 create Sequence

 --  Authorization 
  Grant     Create  Sequence  To  Scott
  --  Create sequence seq_id  
  Create  Sequence seq_id
Start  With     300  
Increment  By    1  
Maxvalue  99999999  
Cache  10  
Nocycle; 

3.2 use sequence

A nextval column must be referenced before the currval column of the application sequence to initialize the value of the sequence.

SelectSeq_id.nextvalFromDual;
--Result 300
SelectSeq_id.currvalFromDual;
--Result 300
 

3.3 change sequence

Syntax: Alter sequence sequence_name

[Increment by N2]

[{Maxvalue N3 | nomaxvalue}]

[{Minvalue N4 | nominvalue}]

[{Cache N5 | nocache}]

[{Cycle | nocycle}]

3.4 Delete Sequence

Syntax: drop sequence sequence_name

4. View

A view is a logical table defined by a select subquery statement. It is a "virtual table" that only defines and has no data ".

4.1 create a view

Syntax: Create [or replace] [force] view view_name -- Force creation (whether the base table exists or not)

[(Alias1, alias2. ..)] -- specifies the expression or column alias selected by the View query.

As select_statement -- SELECT statement when creating a view

[With check option [constraint] -- when using a view, check whether the involved data passes the where condition of the select subquery.

[With read only]; -- the created view can only be used for queries, but cannot be used to change data. It cannot exist at the same time as the order by clause.

 --  Authorization  
  Grant    Create     View     To  Scott;
  --  Create view v_scoot_1  
  Create     View  V_scott_1  As  
  Select  Empno, ename, job From  EMP  Where  Deptno  =  20  ;
  --  Create view v_scott_2  
  Create     View  V_scott_2 (employee ID, name, position)  As  
  Select Empno, ename, job  From  EMP  Where  Deptno  =  20  ; 

4.2 perform DML operations on the View

All DML operations performed on the view are performed on the data of the base table.

4.2.1 User-updated connection View:

(1) does not contain centralized operators (such as Union, Union all, intersect, and minus)

(2) does not contain the distinct keyword

(3) does not include the Group by, order by, connect by, or start with clause

(4) does not contain subqueries

(5) does not contain grouping Functions

(6) columns to be updated are not defined by column expressions.

(7) All the not null columns in the table belong to this view.

4.2.2 key value saving table

If the primary key (primary key and unique key) of a base table in the connected view exists in its view, the base table is called a key-value storage table.

4.3 query view information

4.3.1 updatable columns in the query view

You can use the user_updatable_columns data dictionary to query all the tables in the current user solution and all the modifiable columns in the view.

 
--Query all columns that can be updated in the v_dept_emp _ view: select a. column_name, A. updatable, A. insertable, A. deletable
FromUser_updatable_columns
WhereA. table_name=Upper('V_dept_emp_1');
 

4.3.2 query view definition Information

You can use the user_views data dictionary to query the definition information of the View Graph in the current solution.

 
--Query the definition information of v_dept_emp_1 view.
SelectC. view_name, C.Text FromUser_views C
Where
C. view_name=Upper('V_dept_emp_1');
 

4.4 delete a view

Syntax: Drop view view_name

4. Index

4.1 index Overview

An index is an optional table-related solution object.

4.2 create an index

Syntax: Create index index_name on table_name (column list) [tablespace tablespace_name]

Index_name: name of the created index

Table_name: name of the table to which the index is created

Column_list: Specifies the list of column names for which an index is created on a table. You can create an index based on multiple columns.

Tablespace_name: Specify the tablespace for the index

--Authorization
Grant Create Index ToScott;
--Create a Sal-based index for the EMP table in the Scott Solution
Create IndexIdx_empOnEMP (SAL)
 

4.3 Index classification

(1). Single Column index and Composite Index

An index created based on one column is a single column index, and a composite index based on two or more columns

(2) unique and non-unique Indexes

Unique index-based Columns cannot have duplicate values. Non-unique index-based columns can have duplicate values, which can be null.

(3). Standard (B-tree) Index

B-tree indexes organize and store index data by B-Tree Structure

(4). Bitmap Index

For columns with a small base (the number of non-repeated values that a column may have), a bitmap index should be created for these columns to accelerate the query efficiency.

(5). Function Index

An index created for a function or expression containing a column is called a "Function Index"

4.4 create various indexes

(1) unique index

 
--Create a unique index idx_emo_ename in the ename column of the EMP table.
Create Unique IndexIdx_emp_enameOnEMP (ename );
 

(2) Composite Index

 
--Create a composite index for the ename and job columns in the EMP table
Create IndexIdx_emp_ename_jobOnEMP (ename, job );
 

(3) bitmap Index

 
--Create a bitmap index on the job column of the EMP table
CreateBitmapIndexIdx_bm_emp_jobOnEMP (job );
 

(4). Function Index

Create IndexIndx_fun_emp_hiredateOnEMP (extract (Year FromHiredate ));
 

4.5 merge Indexes

 
--Merge index indx_fun_emp_hiredate
Alter IndexIndx_fun_emp_hiredateCoalesce;
 

4.6 re-Indexing

--Re-indexing indx_fun_emp_hiredate
Alter IndexIndx_fun_emp_hiredate rebuild;
 

4.7 monitor index usage

Record the index usage in the V $ object_usage data dictionary dynamic performance View

Step: (1) make the index under monitoring

 
--Make the index idx_emp_ename_job under monitoring
Alter IndexIdx_emp_ename_job monitoring usage;
 

(2) Execute SQL statements to view the usage of monitored indexes

4.8 View index information

You can use the user_indexes data dictionary view to view all the index information in a table.

Index_name: Index name

Index_type: Index type (function-based Normal: B-tree index, bitmap: bitmap index, normal: Normal B-Tree Index)

Uniquenes: whether the column is a unique index

4.9 indexing principles

(1) generally, you do not need to create an index for a table with a small amount of data.

(2) If the data volume is large and each query volume is less than 15% of all data, you can create an index for the table.

(3) An index should be created for columns with no repeated column values.

(4) B-tree indexes should be created for classes with a large value range.

(5) An index should be created for columns that contain multiple null values but need to query all records with non-null values.

(6) An index cannot be created for a column of the Data Type of a large object, such as clob or blob.

(7) for multiple tables that frequently require connection queries, creating indexes on the columns used for connection can significantly increase the query speed.

(8) orcale automatically creates an index for a column with the primary key and unique constraints.

(9) Although a table can have any number of indexes, the more indexes there are in the table, the more overhead it requires to maintain indexes.