Sequence
Created in sequences
Role
Generate an auto-grow (or decrease) integer value
Use when adding data frequently, controllability is good
notation: sequence name. Nextval Gets the next sequence value
The sequence name. Currval Gets the current sequence value, not commonly used.
View
Virtual tables defined on the basis of physical tables
Create in views
Role:
The complex query logic is saved in the form of view, which facilitates the reuse of query results.
Generally used to query data, not to do data updates
Commonly used for compatibility or migration of systems
Views cannot be indexed and are suitable for query statements with small amounts of data
Create a View
Wording: SELECT * from view name
Materialized view
Physical storage of data in a normal view
You can create indexes that are suitable for query statements with large data volumes
However, the data is not synchronized with the original table and needs to be refreshed.
Created in materialized views
There will be one more table in the tables after creation.
Refresh Statement
In the command window, create
exec Dbms_mview.refresh (' Physical View name ')
Index
One way to improve query efficiency
Auto Create: Automatically created based on primary key or uniqueness constraint
Manually created: The data values in the column are distributed in a wide range
Columns often appear in a WHERE clause or join condition
Tables are frequently accessed and data volumes are large
Tables are not updated frequently
Features: The order of indexed columns is consistent with the order of the columns of the query criteria, increasing the index hit ratio. Indexes can affect the efficiency of data updates, not multiple builds.
Create a method
Oracle sequence, view, index