1. Transaction: A logical unit consisting of a set of commands that ensures that all operations either succeed or all fail.
End of transaction: 1) Explicit commit (Commit) 2) explicit rollback (rollback) 3) DDL statement Stealth commit 4) normal End program commit 5) Abnormal end program rollback
Transaction Characteristics: Acid Properties
1) atomicity 2) Consistency 3) Isolation 4) persistence
Command: Commit commit rollback rollback set autocommit on Auto commit (rollback invalid)
2, Index: Fast access to data, a single column can only build one index
Ways to access data: Full table scan, via ROWID (line number), index
Index Category: 1) B-Tree index: (balance balanced index) each node queries the same speed, the default is the B-tree index, and the unique index and composite index are also B-tree indexes.
2) unique index (unique): Index column does not have duplicate values
3) Reverse key index (reverse): Reverses the data value of the indexed column. The adaptation is built on columns where column values are continuously growing. Cannot do scope query.
4) bitmap Index (bitmap): Fit on columns with very few column values. Small footprint, reduced response time, and a table that is not adaptable to frequent changes
5) Other indexes: Combined index, function index
Command: 1) CREATE INDEX: [Unique|bitmap] index name on table name (column name) [Reverse]
2) Rebuild index: ALTER INDEX index name Rebulid
3) Merge index: Alter index name COALESCE
3, View: Virtual table does not occupy space, the data is stored in the base table.
Syntax: Create [or replace replace] [force enforcement] View view name as SQL statement [with CHECK option constraint][with Read Only]
Force creates a view with errors: whether the base table exists, whether the column is valid, and whether the table has permissions the ORDER BY clause can be included in the SQL statement
With CHECK option to insert or update only rows that can be accessed
Changes to a view can only be done on a single base table and cannot violate the constraint does not contain an aggregate function
Role: Ensure data security, hide data complexity, simplify commands, isolate changes to base table definitions, and flexibly rename columns
Materialized view: A special view with physical storage space. There are two concepts: query rewriting and materialized view synchronization, synchronous refresh with on commit (base table change is synchronized to view) and on Demand (manual) two ways, refresh mode is complete (full), force (default, Priority fast), fast (increment), Never (not refreshed) four kinds
Create materialized views (requires appropriate permissions): 1) Create a materialized log: Creation materialized view Log on table name with ROWID;
2) Creating materialized Views: Create materialized View name build immediate refresh fast on commit enable query rewrite as SQL statement
4. Sequence: A column value used to generate consecutive integer values that can be ascending or descending.
Syntax: Create sequence sequence name [start with initial digit][increment by growth value][maxvalue maximum][minvalue min.]
You can query the next sequence value and Currval query the current sequence value with Nextval, and the value of the pseudo-column is stored in the virtual table dual
Query sequence: Select Sequence_name,increment_by from User_sequences
5, Synonyms: Object alias, do not occupy space
Purpose: Simplifies SQL statements, hides object names and owners, provides location transparency for remote objects, and provides public access to database objects.
Category: Private synonyms, public synonyms
1) Private synonym: Must have create [any] synonym permission only by current mode user access
Creae synonym synonym name for object name
2) public synonym: can be accessed by all users, must have the synonym permissions
Create public synonym synonym name for object name
6. Partition table: Partitioning a table into sections is stored in separate tablespaces into partitions.
A little: Improved query performance for tables, easier management of tables, easy backup and recovery, improved data security
Partitioning methods: scope (range), list, hash (hash), composite, interval, virtual column partition
Syntax: CREATE TABLE table name (Field 1, Field 2, ...) Patiton by range (partition field) (partition P1 values less then (value), partition P2 ...)
Query partition table: SELECT
From table name partition (p1)
Command:
Alter user Scott account unlock unlock the Scott user
Alter user Scott identified by password to set the password for Scott users
Grant Connect,resource to Scott for Scott user authorization
Select Username,account_status from dba_users check the user's lock status
Select Object_name,object_type from User_objects all objects and categories of users
Select Rowid,rownum from Dept Dept Table's Pseudo-column rowid and RowNum, Nextval and currval in the sequence are pseudo-columns and can be checked for non-modifiable
Select Table_name,index_name,column_name from user_ind_columns indexed field
Create unique index Dept_index on dept (dname) creates unique indexes for dname in dept tables
CREATE INDEX Emp_reserve on EMP (empno) reverse; To create a reverse index of empno in an EMP table
ALTER TABLE EMP DROP CONSTRAINT pk_emp cascade DROP index; Delete primary key index
CREATE VIEW v3 as SelectFrom dept where loc= ' BOSTON ' with CHECK option Constratint Ycq; Create a V3 view in the Dept table and add constraints to prevent the LOC column from being modified
Grant create materialized view,query rewrite,create any table,select anything table to Scott; Grant Scott permission to create materialized views and any tables
Select sequence name. Nextval, sequence name. Currval,user,sysdate from dual Query the next and current serial number and current user and system time
Grant create any synonym to Scott; Grant Scott users permission to create arbitrary synonyms
Create synonym E for EMP; creating a private synonym for the EMP table E
Create public synonym a for EMP; creating a common synonym for the EMP table A
CREATE TABLE student (ID int,name char) partition by range (ID) (partition P1 value less than (' ten '), partition P2 Valu E less than (' C '), partition P3 value less than (' + '), partition P4 value less than (MaxValue)); Create student table with ID less than 10 for first partition table ...
Oracle transactions and common database objects
