Shulou(Shulou.com)06/01 Report--

Oracle database-the most basic objects are tables and views, as well as constraints, indexes, sequences, functions, stored procedures, and even creating synonyms. The operation to the database can be basically summed up as the operation to the data object. Therefore, on the basis of the basic operation described in the previous blog post, this blog post will introduce the basic operation of its object.

Table and view Oracle table is the basic structure of data storage. Subsequently, partitioned tables and object tables are introduced, and then temporary tables are introduced to make the table more powerful. A view is a logical expression of data in one or more tables. Users can think of the view as a storage query (stored query) or a virtual table (virtual table). The query is only stored in the oracle data dictionary, and the actual data is not stored anywhere else, so there is no need to consume other space to build the view. Views can also hide complex queries. 1. Table operation

As explained in detail in the previous blog post, create tablespaces-create users (c##jerry)-create tables (info). The table environment is as follows:

/ / create a table

SQL > create table info

2 (

3 id number (4) constraint PK_id primary key, # constraint: constraint

4 name varchar2 (10)

5 score number (5par 2)

6 born date

7 address varchar2 (50)

8)

/ / insert data

SQL > insert into info values (1 nanjing', 2018-10-9)

SQL > insert into info values (2 recordings lisibility77 recordnullpencil null)

SQL > insert into info values (3 recordings lwangwuqiang pr 77pr nullpol null)

SQL > commit

2. Create a view SQL > create view view_info as select * from info; # create a view select view view_info as select * from info; # View View drop view view_info; # Delete a view

3. Materialized view

A materialized view is a database object that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data table. Materialized views store data based on remote tables, which can also be called snapshots.

/ / Grant SQL to c##jerry users > conn sys/abc123 as sysdba # switch to administrator grant create materialized view to caches jerrys; # create materialized view grant query rewrite to cantilever jerrys; # query rewrite grant create any table to cantilever jerrys; # create any table grant select any table to c##jerry # View any table

/ / create materialized view log

SQL > create materialized view log on info

/ / create materialized view statement SQL > create materialized view mtrlview_info # establish materialized view name build immediate # immediately generate data refresh fast # refresh (do not enable this function = snapshot) on commit # enable submission function enable query rewrite # enable query rewriting as select statement / / Delete materialized view

SQL > drop materialized view mtrlview_info

II. Index

An index is a data structure that can improve query performance and is divided into the following categories:

/ / B-tree index

SQL > create index score_index on info (score)

/ / unique index (unique, non-empty for primary key)

SQL > create unique index uni_index_info on info (id)

/ / reverse index

SQL > create index re_index_info on info (score) reverse

/ / Bitmap index

SQL > create bitmap index bit_index_info on info (address)

/ / other indexes (such as functional indexes)

SQL > create index upp_index_info on info (upper (name)); # uppercase function index

/ / View the index

SQL > select index_name,index_type,table_name,tablespace_name from user_indexes

/ / rebuild the index

SQL > alter index index name rebuild

SQL > alter index index name rebuild tablespace tablespace

/ / merge index fragments

SQL > alter index index name coalesce

/ / Delete the index

SQL > drop index index name

3. Sequence

The Oracle sequence is a continuous digital generator. Sequences are often used for artificial keywords or to sort data rows otherwise the data rows are unordered.

/ / create sequence SQL > create sequence toy_seqstart with 10 # initial value increment by 1 # incremental maxvalue 2000 # maximum nocycle # non-cyclic (no restart over 2000) cache 30 # Cache 30 sequence digits / / create table toySQL > create table toy 2 (3 id number (4) constraint PK_id primary key, 4 name varchar2 (10), 5 score number (5 born date 2), 6 born date, 7)

/ / insert data and verify the serial number

SQL > insert into toy values (toy_seq.nextval,'zhangsan',88); # nextval: pointer (fixed). To call the

SQL > insert into toy values (toy_seq.nextval,'zhangsan',77)

/ / View the current value of the sequence

SQL > select toy_seq.currval from dual

/ / Delete the sequence

Drop sequence toy_seq

Synonym is an alias for another data object. Classification: public synonyms (public) are for all users; private synonyms (private) are only for object owners or authorized accounts. In a local database, synonyms can represent data objects such as tables, views, sequences, programs, functions, or packages, as well as objects in another database through links. / / create private synonyms (for info table)

SQL > create synonym pr_info for info

SQL > select * from pr_info; # check through synonyms

/ / create public synonyms (for info table)

SQL > create public synonym pub_info for info

SQL > select * from pub_info; # View

5. Partition table

In order to solve the problem of massive data storage

/ / create four tablespaces (tmp01, tmp02, tmp03, tmp04)

SQL > create tablespace tmp01

Datafile'/ orc/app/oracle/oradata/tmp01.dbf'

Size 100M

SQL > create tablespace tmp02

Datafile'/ orc/app/oracle/oradata/tmp02.dbf'

Size 100M

SQL > create tablespace tmp03

Datafile'/ orc/app/oracle/oradata/tmp03.dbf'

Size 100M

SQL > create tablespace tmp04

Datafile'/ orc/app/oracle/oradata/tmp04.dbf'

Size 100M

/ / create a partition table (sales)

Create table sales

(

Sales_id number

Product_id vachar2 (5)

Sales_date date

)

Partition by range (sales_date)

(

Partition p1 values less than (to_date ('2018-04-03)) tablespace tmp01

Partition p2 values less than (to_date ('2018-05-03)) tablespace tmp02

Partition p3 values less than (to_date ('2018-06-03)) tablespace tmp03

Partition p4 values less than (maxvalue) tablespace tmp04

)

/ / insert data to see if distributed storage is implemented.

Insert into sales values ('2018-05-23 March May, May 23)

Select * from sales partition (p3)

# the results show that the input data date is 2018-05-23 and should be stored in the p3 partition, while other partitions do not have this data!

Thank you for your reading and hope to make progress together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.