Summary of Oracle details, oracle details

Summary of Oracle details, oracle details

Database creation and management
I. database creation problems:
Data is organized in the form of a two-dimensional table. A table consists of columns and rows. A Row corresponds to an entity instance, which is also called a record and a tuples. A column corresponds to an object attribute, also called a field. The value of each row in a column is also called a data unit, data item, attribute value, field value, and so on. The most basic schema object in the table database, and all data operations are performed around the table.
Create table statement

Ii. Indexing
Index creation can improve the efficiency of data retrieval. It can provide a quick access path for Data Query and reduce disk I/O. An index is created based on a table, but does not depend on the table itself. The index is automatically maintained and used by the system. After the index is created, you do not need to participate.

Create index statement to create an index. Unique indicates creating a unique index. Bitmap indicates creating a bitmap index. Asc | desc is used to specify the order of index values. Reverse indicates that a reverse key index is created. Parameter_list indicates that a reverse key index is created.

3. Create a view
A view is a virtual table that displays data in a table after filtering. A view consists of a predefined select statement.
Data in a view does not belong to the view itself, but is a basic table. You can perform insert, update, and delete operations on a view like a table.
The view cannot be modified. After a table is modified or deleted, the view should be deleted and rebuilt.
There is no limit on the number of views, but the names cannot be the same as those of views and tables, and they are unique.
A view can be nested. A view can be nested with another view.
Views cannot be indexed, there cannot be associated triggers and default values, and SQL server cannot use order by After views.
Create view

Iv. Sequence
A sequence is used by oracle to produce a set of equal-interval values. The sequence is incremental and continuous. The oracle primary key does not have the auto-increment type. Therefore, the sequence value is generally used as the primary key of a table to implement auto-increment of the primary key. Sequence numbers are not automatically generated when a record is inserted. You must call the sequence method to generate the sequence (nextval method is generally called ). You can also write an insert trigger for the table to automatically generate the table.
Unique value automatically provided
Shared object
It is mainly used to provide the primary key value.
Loading Sequence values into memory improves access efficiency

Create Sequence
1. You must have the permission to create a sequence: create sequence or create any sequence.
2. Sequence creation syntax

Create sequence name

V. partitions
Definition: Oracle partitioning is a technology used to process ultra-large tables and indexes. Partitioning is a "divide and conquer" technology. Through the continuous management and division of tables and indexes, each large and independent object is managed, it can provide a scalable space for a large amount of data.

Advantages:
Enhanced availability: If a table partition fails, data in other partitions is still available;
Easy Maintenance: if each partition of the table fails, you only need to repair the data;
Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance;
Query performance improvement: You can query partition objects to search for shards you are concerned about, improving the search speed;

Vi. Partition tables and partition Indexes