Oracle Other database objects

　　Other database objects:
Sequence (SEQUENCE)
Indexing (Index)
Views (view)

1. Sequence (SEQUENCE)
Corresponding Database dictionary: user_sequences
　　　　Role: A database special object used to produce a unique value

To create a sequence syntax:
Create sequence sequence name
Start with n means starting from a few, the default value is 1
Ncrement by n How many increments per count, default is 1
MaxValue n Sequence Peak value n
MinValue n sequence lowest peak n
Cache n provides n pre-allocated sequences, stored in memory
Cycle | Nocycle whether the loop
Oredr | Noorder ordered or unordered sequence

For example:
Create a sequence for employees
Create sequence tbl_emp_id start with 4;

　　How do I use sequences?
Nextval: Take the next value of the sequence (Tbl_emp_id.nextval)
Currval: The first value of the sequence to take (Tbl_emp_id.currval)

　　When inserting data, use:
INSERT into tbl_emp values (Tbl_emp_id.nextval,....)

　　To delete a sequence:
Drop sequence sequence name;

Indexing (Index)
The corresponding data dictionary user_indexes

It is a more important database object, which can effectively improve the query efficiency of database (Database performance optimization)

There are two ways to create an index:
1 Automatic creation
When you add a PRIMARY KEY constraint or a uniqueness constraint to a column in a table, the system automatically creates a unique index for the column, which is the name of the constraint

2 Manually created
Grammar:
Create index index name on table name (column name ...) ）；

Create INDEX index name on EMP (name)

Select salary from emp where name = ' Mark ';

Select salary from emp wehre rowid = (select rowID from emp where name= ' mark ');

Attention:
1. The index has its own separate storage space and namespace
2. Creating an index will also sacrifice some database performance

How the Index works:
1. By default, the index is a data structure using btree (binary tree)

2. Pseudo-column (ROWID), which holds the true "physical address" of the data row record.

--Query a row of records based on a physical address
Get the rowid of the record first
Select rowID from s_emp where first_name= "Carmen";

Find the corresponding record based on the row record rowID
select* from S_emp where rowid= (select rowID from s_emp where first_name= "Carmen");

3. Principle of Index Establishment:
A key-value tree is synthesized from the column values that create the index with the ROWID, which is the index, and then it is stored in the specified data structure (binary tree, bitmap), and is a separate index space

4. How the index query works:
When the column of the Where condition in our query statement is indexed, the query is divided into 2 steps:
A. First check the index, the value in the sentence column directly find rowID
B. rowID directly to the corresponding line record end query based on the first step

5. Indexing strategy:
A. Primary key and Uniqueness column appropriate
B. The columns that do not change frequently are appropriate
C. Meet the above two conditions, often do query the right column
D. Columns with too many duplicate values are inappropriate
A column with too many e.null values is inappropriate

6. Deleting an index

Drop index name;
-----------------------------------------------------

Views (view)
Corresponding Data dictionary: User_view
He is a database object, it is a "window" of the table, the object used to hold the query statement, the view is attached to the table, and the table is shared with the storage space.

Definition: The essence is a valid query statement

Role:
1. With permission, according to the business to do hierarchical management
2. Reduce complexity and increase the security of your data

Syntax for creating a view:
CREATE VIEW name as clause;
with Read only; View Read only

For example:
--Create a view of the Id,salary two column in the S_emp table in read-only mode
CREATE View view_name as select Id,salary from s_emp with Read only;
Note: The operation view must have certain permissions and cannot perform DML operations on read-only views

Delete a view
Drop view name;

View Categories:
Relationship view, inline view, object view, materialized view
-----------------------------
Add: Query What the current user can do
SELECT * from session privs;

-- query all actions that a permission can perform
select* from Dba_sys_privs where grantee= ' DBA ';(requires DBA)

-- querying the system roles assigned to the current user
Select *from sessions_roles order by role;

--Two ways to grant permissions :
1.grant createany view to User name
2.grant connect,resource,dba to User name

Oracle Other database objects