Oracle Study Notes (3)

1. create and manage tables
1. Create Table syntax
Create table Name (column datatype [default expr] [, column...])
Default Constraints

2. Create a table using subquery
Create table Name
As
Select * from u table

Ii. modify a table
1. Add a new column
Oracle: alter table table_name
Add (column datatype [default expr] [, column datatype]...);

2. Modify existing columns (modify type, size, default value)
Oracle: alter table table_name
Modify (column datatype [defaullt expr] [, column datatype]...)

3. Delete Columns
Alter table table_name drop column (keyword) column 1
Delete columns in a centralized manner
Alter table table_name set unused column 1
Alter table table_name drop unused columns;

Iii. delete a table
Drop table name. The table is automatically submitted after deletion and cannot be returned by rollback.
Duplicate table: rename TABLE name to new table name
Efficiently clear a table and delete it with truncate without logging, that is, roll back cannot be used.
Truncate table name

After deletion with delete, You can regret it and roll it back.

Annotate a table
Comment on table tb_u_1 is 'user table is used to store user information ....'

4. Add Constraints
Create table table_name
(Id int not null,
Lname varchar (20 ),
Fname varchar (20 ),
Constraint un_key_1 unique (lname, fname)
)

Alter table name add constraint ch_1 check (column1 like 'K % ');
When adding data, if the column1 data does not contain k, the addition fails.

Add primary key constraints
Oracle/SQL: aleter table name 1 add constraint pk_1 primary key (column1 );
Add foreign key
Oracle/SQL: aleter table name 2 add constraint fk_1 foreign key (column1) references table name 1 (column1 );


Delete primary key constraints
Oracle/SQL: alter table tb_name drop constraint p1
Alter table name 2 drop constraint fk_1 cascade;
SQL: query whether the primary key has p1
If exists (select * from sysobjects where name = 'p1 ')

Select * from user_constraint

Select constraint_name, column_name from user_cons_columns where table_name =''

5. Create View

Create [or replace] [force | noforce] view view_name
[]
As subquery
[With check option [constraint 11]
[With read only [constraint 22]

Create a view or redefine a view
Create or replace view view_name as subquery

If the table table_1 does not exist, the view is successfully created using force. Otherwise, the report does not have an error.
Create force view v_name as select * from table_1

Use views to modify data
Create view view_001
As
Select * from table_001 where id <10
Data that is not in the view range is successfully modified.
Update view_001 set column1 = '000000' where id = 10;
If you modify data that is not in the view range, it will fail (you can only modify the id <10) and report: "view with check option violates the where clause"
Create view view_001
As
Select * from table_001 where id <10
With check option;
Update view_001 set column1 = '000000' where id = 10;

Create a read-only view
Create view view_001
As
Select * from table_001 where id <10
With read only;

Delete View Drop View
Drop view view_Name

Temporary view inline views
Select * from (select * from table_Name)

6. sequence, index, and synonym

Create a sequence)
Create sequence seq_name
[Increment by n]
[Start with n]
[{Maxvalue | nomaxvalue}]
[{Minvalue | nominvalue}]
[{Cycle | nocycle}]
[{Cache | nocache}]
Create sequence seq_test1
Increment by 1
Start with 1
Query Sequence Select a Sequence
Select seq_test1.currval from daul
Select seq_test1.nextvall from daul
Select * from user_sequences (User sequence view)

Use Sequence Using a Sequence
Create table tb_1 (a int );
Insert into tb_1 (seq_test1.currval );

Modifying a Sequence
Alter sequence seq_test1
Increment by 20
Max value 999999
Nocache
Nocycle;

Create an Index Creating an Index
Create an index on one or more columns
Create index index_test1
On table (column [, column]...);

Indexing improves data retrieval speed, but reduces the performance of UPDATE, DELETE, and INSERT operations !!!

Index creation principles (from csdn)

I. B-Tree Index:
1. Principles for selecting index fields:
L The most frequently used fields in the WHERE clause
L join fields in the join statement
L select highly selective fields (if a few fields have the same value, there are many unique values, the selectivity is good)
L ORACLE automatically creates an index on UNIQUE and primary key fields
L indexing on poorly selective fields is only beneficial when the value distribution of this field is very skewed (in this case,, the two field values are much less than the other values)
L do not create B-TREE indexes on fields with very few unique values, in which case you can consider creating Bitmap indexes on these fields. in the online transaction processing environment, the concurrency is very high, and indexes are often modified. Therefore, bitmap indexes should not be created.
L do not index frequently modified fields. when there are UPDATE, DELETE, and INSETT operations, in addition to updating the table data, ORACLE also needs to UPDATE the index, just like updating data, or generating restoration and redo entries.
L do not create indexes on fields that use functions. In this case, the optimizer does not use indexes unless you create function indexes.
L you can consider creating indexes on foreign key fields. These indexes allow you to share the sub-Table lock when performing the UPDATE and DELETE operations on the master table, this is very suitable for the case where the parent and child tables have many concurrent INSERT, UPDATE, and DELETE operations.
L after an index is created, compare the query performance improvement and the loss of UPDATE, DELETE, and INSERT operations after the index is created. After comparison, determine whether to create this index.
2. Select create Composite Index
Advantages of composite indexes:
L improved selectivity: composite indexes are more selective than single field indexes.
L reduce I/O: if all the fields to be queried are included in the compound index field, ORACLE only needs to access the index without accessing the table.
Under what circumstances will the optimizer use composite indexes?
(A) When the WHERE clause of an SQL statement uses the leading field of composite indexes, the ORACLE optimizer will consider using composite indexes for access.
(B) when several fields are in the WHERE clause of SQL statements, they are often used together with the AND operator as filter predicates, when these fields are combined, they are more selective than each other. You may consider using these fields to create a composite index.
(C) When several query statements query the same field values, you can create a composite index on these fields.
Principles for sorting compound index fields:
L make sure that the fields used in the WHERE clause are the leading fields of the composite index.
L if a field is most frequently used in the WHERE clause, you should consider putting this field first when creating a composite INDEX (in the create index Statement)
L if all the fields use the same frequency in the WHERE clause, the most selective fields are placed at the beginning, and the least selective fields are placed at the end.
L if all fields use the same frequency in the WHERE clause, if the data is physically sorted by a field, consider placing this field first in the composite index.
Ii. Bitmap Index
Under what circumstances can bitmap indexes improve query performance?
L The WHERE clause contains multiple predicates for fields with a low or low base.
L a single predicate selects a large number of rows on these low-base Fields
L Bitmap indexes have been created on some or all of these low-base fields.
L The queried table contains many rows
L multiple Bitmap indexes can be created on a single table. Therefore, bitmap indexes can improve the performance of complex queries that contain lengthy WHERE clauses. In the join query statements of aggregate queries and star models, bitmap indexes can also provide excellent performance.
Comparison between bitmap index and B-TREE Index
L Bitmap indexes save storage space
L Bitmap indexes are suitable for data warehouse environments, but not for online transaction processing environments. in the data warehouse environment, data maintenance is usually completed through batch INSERT and batch UPDATE, so the maintenance of indexes is delayed and the direct interaction DML operation ends. for example, when you insert 1000 rows of data in batches, these inserted rows are placed in the sort buffer, and then the 1000 index entries are updated in batches, each bitmap segment only needs to be updated once in each DML operation, even if multiple rows in that bitmap segment are updated
L The compressed bitmap of a healthy value is composed of one or more bitmap segments. Each bitmap segment is about as large as half a BLOCK, and the minimum granularity of the lock is a bitmap segment, in the online transaction processing environment, if multiple transactions execute simultaneous updates (that is, concurrent updates), bitmap indexes will affect the UPDATE, INSERT, and DELETE performance.
L A B-TREE index contains only one ROWID, so when an index entry is locked, that is, a row is locked. however, for Bitmap indexes, an index entry may potentially contain a ROWID (that is, a ROWID within a certain range, with multiple rowids). When a bitmap index entry is locked, the ROWID contained in this entry is locked, which affects the concurrency. the larger the number of rowids in a single-digit image segment, the poorer the concurrency. even so, for bulk insert, UPDATE, and DELETE, bitmap indexes still offer better performance than B-TREE indexes.
Iii. Index and NULL
NULL values are seen as unique values in an index unless the NON-NULL values of two or more rows of an index are equal. in that case, rows are considered equal. Therefore, a unique index does not allow rows to contain null values for fear of being considered equal. however, this rule does not apply when all rows are null. ORACLE does not index the rows of all tables whose health values are NULL unless it is a bitmap index or when the cluster key field value is NULL

Create a Synongms Synonym
Create [public] synonym sy_name for object
Create a table named table_001
Create synonym tb1 for table_001
Delete synonym Drop a synonym
Drop synonym tb1
Oracle synonym creation and its role (from http://www.jb51.net/database/201109/106257.html)

The Oracle synonym (synonyms) is literally an alias. Similar to the attempted function, it is a ing relationship. This topic describes how to create a synonym statement, delete a synonym, and view a synonym statement.
Summary of oracle Synonyms:
The meaning of an alias is literally the same as that of an attempt. Is a ing relationship.
1. Create a synonym statement:
Create public synonym table_name for user. table_name;
The first user_table and the second user_table can be different.
In addition, to create a synonym for a table on a remote Database, you must first create a Database Link (Database connection) to expand the access, and then create a Database synonym using the following statement: create synonym table_name for table_name @ DB_Link;
Of course, you may need to authorize the current user (user2) in the user: grant select/delete/update on user2
2. Delete Synonyms:
Drop public synonym table_name;
3. view all synonyms:
Select * from dba_synonyms
Synonyms have the following benefits: they save a lot of database space and have little difference in operating the same table for different users; they extend the scope of use of databases, allows seamless interaction between different database users. synonyms can be created on different database servers and connected over the network.
Oracle Database provides the synonym management function. Oracle synonyms are aliases of database solution objects. They are often used to simplify object access and improve object access security.

AD:

In Oracle, users are managed by permissions. That is to say, if we want to use a database, we must have permissions, but if someone else grants us the permissions, we can also perform operations on the database, but we must enter the name of the table owner before the name of the authorized table, so this is troublesome, what should we do? Create an Oracle synonym! In this way, you can directly Use synonyms to use tables.
1. Definition of synonyms
Oracle Database provides the synonym management function. Synonyms are aliases of database solution objects. They are often used to simplify object access and improve object access security. When a synonym is used, the Oracle database translates it into the name of the corresponding solution object. Similar to a view, synonyms do not occupy the actual storage space. Only the definition of synonyms is saved in the data dictionary. Most database objects in Oracle databases, such as tables, views, synonyms, sequences, stored procedures, packages, etc. Database administrators can define synonyms for them based on actual conditions.
2. Oracle synonym Classification
There are two types of Oracle Synonyms: Public Oracle synonyms and private Oracle synonyms.
1) Public Oracle synonym: it is owned by a special user group Public. As the name suggests, all users in the database can use public synonyms. Common synonyms are often used to mark common database objects, which must be referenced by everyone.
2) Private Oracle synonym: it corresponds to a public synonym, which is owned by the user who created it. Of course, the creator of this synonym can control whether other Users have the right to use their own private synonyms through authorization.
3. Create and delete Oracle Synonyms
Syntax for creating public Oracle Synonyms: Create [public] synonym name for [username.] objectName;
Drop [public] synonym name
4. Role of Oracle Synonyms
1) in multi-user collaborative development, the object name and its owner can be blocked. If no synonym exists, the user name must be used to operate tables of other users. the object name format uses Oracle synonyms to hide the user name. Of course, note that the public synonym only defines a public alias for the database object, whether other users can access this database object through this alias depends on whether the user has been authorized.
2) Simplify SQL statements for users. The preceding statement is a simplified SQL statement. If the name of a self-created table is long, you can create an Oracle synonym for the table to simplify SQL development.
3) provides location transparency for remote objects in distributed databases.
5. Role of Oracle synonyms in the database chain
A database chain is a named object that describes the path from a database to another database, through which communication between different databases can be achieved.
Create database link database chain name connect to user name identified by password using 'oracle connection string'; the access object must use the object name @ database chain name. The role of synonyms in the database chain is to provide location transparency.