Oracle Learning Notes (iii) _oracle

First, create and manage tables
1. CREATE TABLE syntax
CREATE TABLE table name (column datatype [default Expr][,column ...])
The description of default constraint is slightly

2, use the subquery to create the table
CREATE TABLE Table name
As
SELECT * FROM U table

second, modify the 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 Column
ALTER TABLE table_name DROP column (keywords) column 1
To delete a column centrally
ALTER TABLE TABLE_NAME set unused column 1
ALTER TABLE table_name drop unused columns;

third, delete the table
The drop table table name, which is automatically submitted after the table is deleted, and cannot be rollback back.
Duplicate table name: Rename table names to new table name
Efficient emptying of a table, with truncate deleted without logging is not to use rollback back.
TRUNCATE TABLE name

Deleted with Delete, you can regret, roll back.

Make a note of a table
Comment on table tb_u_1 is ' user table for user information ... '

Four, 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% ');
Add a failure if there is no k in the Column1 data when adding data

Add a PRIMARY KEY constraint
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 constraint
Oracle/sql:alter table tb_name drop constraint p1
ALTER TABLE name 2 DROP constraint fk_1 cascade;
SQL: Query primary key exists 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= '

v. Creating views 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, then using force to create the view succeeds, otherwise the report does not have an error.
Create force view V_name as SELECT * from Table_1

Using views to modify data
CREATE VIEW view_001
As
SELECT * FROM table_001 where id<10
Modifying data that is not in the view scope will also succeed.
Update view_001 set column1= ' 123 ' where id=10;
Modifying data that is not in the view scope will fail (only id<10 can be modified), and report: View with CHECK option violates WHERE clause
CREATE VIEW view_001
As
SELECT * FROM table_001 where id<10
with CHECK option;
Update view_001 set column1= ' 123 ' where id=10;

Creating a read-only view 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)

vi. sequence (sequence), index, synonym

Create sequence (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 a sequence using a Sequence
CREATE TABLE tb_1 (a int);
Insert into tb_1 (seq_test1.currval);

Modify sequence Modifying a Sequence
Alter sequence SEQ_TEST1
Increment by 20
MaxValue 999999
NoCache
Nocycle;

Creating indexes Creating an Index
Create index on one or more columns creating an is on a or more columns
Create Index Index_test1
On table (Column[,column] ...);

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

Index creation principle (excerpt from CSDN)

I. B-tree index:
1. The principle of optional index fields:
L The most frequently used field in the WHERE clause
L JOIN fields in join statements
L Select highly selective fields (if few fields have the same value, there are many unique values, then selectivity is good)
• Oracle automatically indexes on unique and primary key fields
L an index on a poorly-chosen field is useful only if the value distribution of the field is very skewed (in which case, a value of two fields appears a lot less than other word values)
L Do not build B-tree indexes on fields with very few unique values, in which case you might consider building a bitmap index on these fields. In an online transaction processing environment, with very high concurrency and indexes that are often modified, bitmap indexing should not be built
L do not index on fields that are frequently modified. When there is a Update,delete,insett operation, Oracle updates the index in addition to the data in the table, and it is like updating data, or producing a restore and redo entry
L do not index on fields that are useful to functions, Oracle in this case, the optimizer will not use the index unless you create a function index
L You can consider building indexes on foreign key fields that allow you to update,delete operations on the primary table without having to share the child table's locks, which is ideal for situations where there are many concurrent insert,update and delete operations on the parent and child tables
L when the index is established, please compare the query performance of the index and the loss of Update,delete,insert operation performance, and then finally decide whether it is necessary to establish the index after comparing the gain and loss.
2. Choose to set up a composite index
Advantages of Composite indexes:
• Improve selectivity: Composite indexes are more selective than individual field indexes
• Reduce I/O: If the field that you want to query is exactly contained in the Compound index field, Oracle only has to access the index without accessing the table
What happens when the optimizer uses a composite index?
(a) When an SQL statement's WHERE clause is useful to the lead field of a composite index, the Oracle optimizer considers using a composite index to access it.
(b) You might consider using these fields to establish a composite index when a few fields are frequently used as filter predicates in the WHERE clause of the SQL statement, and when these fields are selected better than the selectivity of each individual field.
(c) If you have several query statements that query the same number of field values, consider establishing a composite index on these fields.
the principle of compound index field sorting:
l Ensure that the field used in the WHERE clause is the lead field for the composite index
L If a field is used most frequently in the WHERE clause, consider putting the field first (in the CREATE INDEX statement) when establishing a composite index
L If all the fields are used the same frequency in the WHERE clause, the most selective fields are first, and the least selective fields are at the bottom of the line
L If all the fields are used the same frequency in the WHERE clause, and if the data is physically sorted by a field, consider placing the field first in the composite index
two. Bitmap indexing
What happens to the lower graph index to improve the performance of the query?
The l WHERE clause contains multiple fields with low cardinality in predicates
L A single predicate selects a large number of rows on these low cardinality fields
L already have a bitmap index created in some or all of these low cardinality fields
L The table being queried contains many rows
L can create multiple bitmap indexes on a single table, so bitmap indexes can improve the performance of complex queries that contain lengthy where clauses, and bitmap indexes provide better performance in the JOIN query statements for aggregate queries and star models
Comparison of bitmap index and B-tree index
L Bitmap index saves more storage space
L bitmap indexes are better for the data Warehouse environment, but not for the online transaction processing environment. In a data warehouse environment, data maintenance is typically done through bulk inserts and batch update, so the maintenance of the index is delayed by the direct-cross DML operation. For example: When you insert 1000 rows of data in bulk, These inserted rows are placed in the sorted cache (sort buffer), and then the batch updates the 1000 index entries, so each bitmap segment is updated only once in each DML operation, even if more than one row is updated in that bitmap segment
L The compressed bitmap of a health value consists of one or more bitmap segments, each of which is about half the size of a block, and the minimum granularity of the lock is a bitmap segment, in an online transaction processing environment, if multiple transactions perform simultaneous updates (that is, concurrent updates), Using a bitmap index can affect update,insert,delete performance
L A B-tree index entry contains only one rowid, so when an index entry is locked, a row is locked. But for bitmap indexes, an index entry potentially contains a rowid (that is, a range of rowid, multiple rowID), When a bitmap index entry is locked, the part of the ROWID that is included in the entry is locked, which affects concurrency. The more the number of rowID in a bitmap segment, the worse the concurrency. However, for the bulk insert,update and delete, Bitmap indexing performance is better than B-tree index
three, index and null
A null value is considered a unique value in the index unless the non-null value of the two or more rows of an index is equal. In that case, the row is considered equal, so the unique index does not allow the row to contain null values to be considered equal. However, when all rows are null values, This rule does not apply. Oracle does not index rows for tables where all health values are null, unless it is a bitmap index or when a cluster key field value is null

Creating a synonym Create a synongms
Create [public] synonym Sy_name for object
Create an alias for table_001
Create synonym tb1 for table_001
Remove synonym drop a synonym
Drop synonym Tb1
Oracle synonym creation and its role (excerpt from http://www.jb51.net/database/201109/106257.html)

The Oracle synonym (synonyms) is literally the meaning of the alias, and similar to the attempted function, is a mapping relationship. This article describes how to create a synonym statement, delete a synonym, and view a synonym statement.
Oracle's Synonyms Summary:
The literal understanding is the meaning of the alias, and is similar to the attempted function. is a mapping relationship.
1. Create a synonym statement:
Create public synonym table_name for user.table_name;
One of the first user_table and the second user_table can be different.
In addition, if you want to create a synonym for a table on a remote database, you need to create a database link to extend access, and then create a database synonym using the following statement: The CREATE synonym table_name for table_ Name@db_link;
Of course, you may need to authorize the current user (User2) in the user users: 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: Save a lot of database space, the operation of different users of the same table not much difference; the scope of the extended database can be used to achieve seamless interaction between different database users; synonyms can be created on a different database server and connected over the network.
The Oracle database provides the functionality of synonym management. An oracle synonym is an alias to a database schema object that is often used to simplify object access and improve the security of object access.

AD:

Management of users in Oracle is managed in a way that uses permissions, that is, if we want to use the database, we have to have permission, but if someone gives us the permission, we can manipulate the database, but we have to type the name of the table owner before the name of the authorized table. , so this is more troublesome, in the face of this situation, we should do? Create a synonym for Oracle! This allows us to use the table directly using synonyms.
1. The concept of synonyms
The Oracle database provides the functionality of synonym management. A synonym is an alias to a database schema object that is often used to simplify object access and improve the security of object access. When using synonyms, the Oracle database translates it to the name of the corresponding schema object. Like a view, synonyms do not occupy the actual storage space, only the definition of a synonym is saved in the data dictionary. Database administrators can define synonyms for most database objects in an Oracle database, such as tables, views, synonyms, sequences, stored procedures, packages, and so on.
Classification of 2.Oracle synonyms
There are two types of Oracle synonyms, namely, public Oracle synonyms and private Oracle synonyms.
1 Common Oracle Synonyms: owned by a special user group public. As the name implies, all users in the database can use common synonyms. Common synonyms are often used to label some of the more common database objects, which are often referred to by everyone.
2 private Oracle Synonym: It is corresponding to a common synonym, he is created by the user all. Of course, the creator of this synonym can control whether other users have permission to use their own private synonyms by authorizing them.
3.Oracle synonym creation and deletion
Syntax for creating publicly-owned Oracle synonyms: create [public] synonym synonym name for [username.] objectname;
Drop [public] synonym synonym name
The function of the 4.Oracle synonym
1 The cooperative development of multi-user can screen the name of the object and its holder. If there are no synonyms, when you manipulate other users ' tables, Must pass the user name. In the form of an object name, you can hide the user name after using an oracle synonym, but note that the public synonym simply defines a common alias for the database object, and whether other users can access the database object through the alias, and see if the user has been authorized to do so.
2 simplifies SQL statements for users. One of the above is a simple representation of simplifying SQL, and if you build a table with a long name, you can create an Oracle synonym for the table to simplify SQL development.
3 provides location transparency for remote objects in a distributed database.
The role of 5.Oracle synonyms in database chain
A database chain is a named object that describes the path of 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 '; Access to the object is through the object name @ database chain name. The role of synonyms in the database chain is to provide location transparency.