Oracle Learning Note Nine database objects

The Oracle database object is also called the schema object, and the database object is a collection of logical structures, and the most basic database object is a table. Other database objects include: Synonyms synonyms are an alias for an existing object. It has the following functions: 1. Simplifying SQL statements 2. Hides the name and owner of the object (hides the original object content) 3. Provides a common access synonym for an object there are two types of synonyms:

Public synonyms can be accessed by all database users.

A private synonym can only be accessed within its schema and cannot have the same name as an object in the current schema.

Create or replace an existing synonym

Create or Replace  for Scott.emp;

Delete synonyms

Drop synonym emp; Drop  public synonym Emp_syn

Using synonyms

Create  Public  for SCOTT.EMP2; Select *  from newer;

Sequence sequences are objects used to generate a unique, sequential sequence number, which can be ascending or descending, and can be used to represent the number of data. We can use the Create SEQUENCE statement to make a sequence.

Createsequence sequence name Start with 1             --refers to the start size of the sequence, which defaults to 1Increment by 1           --increment per time, default is 1Maxvalue -            --maximum number of sequencesMinvalue1               --minimum number of sequencesNocycle--integers are no longer generated when the sequence is maximum (default)CacheTen;--number of integers kept in memory, default

Note: If the sequence is in use, it should be set to NoCache if the primary key is given to prevent discontinuities.

All sequence information is detected from the user_sequences.

Select *  from User_sequences;

The sequence's value 1 is accessed through the pseudo-column of the sequence. Nextval returns the next value of 2 for the sequence. Currval returns the current value of the sequence

Note: If the sequence is in use, it should be set to NoCache if the primary key is given to prevent discontinuities.

INSERT  into Toys (Toyid, Toyname, Toyprice)       VALUES  - ); INSERT  into Toys (Toyid, Toyname, Toyprice)       VALUES  the ); -- Toys_seq. Nextval  The next value of the specified sequence

SELECT  from dual;  -- retrieving the current value of a sequence

Modifying a sequence with the ALTER SEQUENCE statement cannot change the start with parameter of the sequence

ALTER  the CYCLE;

To delete a sequence using the drop sequence statement

DROP SEQUENCE Toys_seq;

View view displays data from one or more tables in a customized manner, and the view can be considered a "virtual table" or "Stored query", and the table on which the view is created is called the base table. The advantages of the view are: 1. Provides another level of table Security 2. The complexity of Hidden Data 3. SQL commands for simplified users 4. Changes in the structure of the isolated base table 5. Provide the data example from another angle by renaming the column:

CREATE VIEW  as SELECT  from Stud_details;

From this view, the results of the query:

Create view syntax for creating views:

 CREATE [OR REPLACE] [ Force] VIEWview_name[(alias[, alias]...)]  asselect_statement[With CHECK OPTION][CONSTRAINT C_name]  [With READ only];--prohibit update delete operations on a table--W ITH Check OPTION CONSTRAINT _name: Indicates that an additional check constraint, insert and update must be required:--Insert requires that the inserted data must conform to the where condition set when the definition is defined, and the UPDATE can only be changed to a data column other than the Where Condition column, or the value of the modified Condition column conforms to the Where condition--

Create a view using the WITH CHECK option options

Create or Replace View  as Select *  from WHERE = ' P '  with Check option Constaint CHK_PV;　　　　

Create a view using the ORDER by clause

Create or Replace View  as Select *  from Order  by Venname;　　

Join view: Results of two tables

Create View  as    Select Studno, Studname, Submrks, SubName      from stud_details, Sub_details      where Stud_details. Subno=sub_details. Subno;

Create an outer join view

CREATE VIEW  as SELECT Vm.vencode, Venname, OrderNo, Odate, Ostatus  from    vendor_master vm, order_master omWHERE  = Om.vencode (+);

Equivalent to

SELECT Vm.vencode, Venname, OrderNo, Odate, Ostatus  from  Left OUTER JOIN order_master om  on = Om.vencode;

DML statements on Views can also use DML statements that modify data, such as INSERT, UPDATE, and delete The DML statements on the view have the following limitations: 1. Only one underlying base table 2 can be modified. If you modify a constraint that violates the base table, you cannot update view 3. If the view contains a join operator, DISTINCT keyword, collection operator, aggregate function, or GROUP by clause, You will not be able to update view 4. If the view contains pseudo-columns or expressions, the view cannot be updatedIf you add data to the view, the data can be added to the base table, but the data that you insert does not meet the requirements of the view's where, and the view does not appear. If you want to insert data that conforms to the definition of the view, youneed to add parameters to the where . If you want to remove data from the base table from the view, the deleted data must conform to the query criteria of the view to remove the data from the view. Key preserving Table key Reservation table is a basic concept to understand the limitations of connection view modification. The table's primary key columns are all displayed in the view, and their values are unique and non-empty in the view. That is, the key value of the table is also the key value in a connection view, then it is called the table is the key reservation table.

Because Studno is both the primary key in Stud_details and the primary key in the join result, Stud_details is a key-preserving table

A single-line function, grouping functions, and expressions can be used in a function view in a view

CREATE VIEW  as     SELECT LOWER (ITEMDESC) Item_desc      from

Delete view Delete view using Drop VIEW statement

DROP VIEW

Indexed indexes are an optional structure related to tables, and using indexes can improve the performance of SQL statement execution, reduce disk I/O, index is logically and physically independent of table data, and Oracle can automatically maintain indexes.

The principle of indexing: indexing works closely with a concept called ROWID, when Oracle creates a data table, the default is to create an implicit field for each data table called ROWID. When you insert a record into a datasheet, the system automatically assigns a unique ROWID number to each record, which allows you to quickly navigate to the record by using the ROWID number.

The action index creates an index using the CREATE INDEX statement: Create a standard index:Create Index index name on table name ( column name );

CREATE INDEX  on itemfile (ItemCode)     Tablespace Index_tbs;

Rebuilding indexes

ALTER INDEX

Delete Index

DROP INDEX

There are various types of indexed index types, and there are special types of indexes in addition to the standard indexes: Unique index unique index ensures that there are no duplicate values in the column that defines the index, Oracle automatically creates a unique index on the table's primary key column create a unique index using the Create UNIQUE index statement

CREATE UNIQUE INDEX Item_index       on Itemfile (ItemCode);

A composite index is an index that is created on more than one column of a table, and the order of the columns in the index is arbitrary, and if all or most of the columns of the combined index are referenced in the WHERE clause of the SQL statement, the retrieval speed can be increased.

CREATE INDEX Comp_index       on Itemfile (P_category, itemrate);

Reverse key index Reverse key index reverses each byte of an indexed column key value, usually built on a column with a continuous growth value, which allows the data to be distributed evenly across the index when creating an index using the reverse keyword

CREATE INDEX Rev_index        on REVERSE;

ALTER INDEX rev_index rebuid noreverse;

Bitmap index bitmap indexes are suitable for creating on low cardinality columns, bitmap indexes do not store rowid directly, but instead store byte-to-rowid mappings, and using bitmap indexing can reduce response time and save space.

CREATE INDEX Bit_index       on Order_master (OrderNo);

Index Organization table Index The data of the organization table is stored in its associated index, and the index stores the actual data of the row, not the ROWID, which accesses the data based on the primary key. The CREATE TABLE command is used together with the ORGANIZATION index clause for creating an indexed organization table.

CREATE TABLE Ind_org_tab (       number (4PRIMARYKEY,       VARCHAR2 ()     )      INDEX;

Comparison of normal table and Index organization table

Primary key uniquely identifies row

TD class= "OA2" width= "408" >

Plain table	Indexed Organization table
ROWID uniquely identifies row
Implicit ROWID column	No implicit ROWID column
ROWID-based access	Access based on primary key
Sequential scan returns all rows	Full index scan returns all rows, sorted in primary key order
Support partition	does not support partition

A function-based index is based on an index created from a function or expression on one or more columns, and an aggregate function cannot appear in an expression, cannot be created on a column of the LOB type, and must be created with QUERY REWRITE permission.when a query uses a function, the index is no longer used, so a function index is established.

CREATE INDEX Lowercase_idx        on Toys (LOWER(toyname));

SELECT  from Toys      WHERE LOWER (Toyname) = ' Doll ';

Partitions in the index we can store the indexes in different partitions. There are three types of partition-related indexes: 1. Local partition index-an index created on a partitioned table that creates a separate index on each table partition, and the partition range of the index is consistent with the table 2. Global Partition Index-indexes created on partitioned tables or non-partitioned tables, indexes specify the range of partitions separately, Regardless of the partition range or partition of the table 3. Global non-partitioned index-the index of a global generic index that is created on a partitioned table, indexes that are not indexed by partitions are indexed by the data dictionary view: 1. User_indexes-User-created index information 2. User_ind_partitions-user-created partition index information 3. User_ind_columns-Information about index-related table columns

SELECT index_name, TABLE_NAME, COLUMN_NAME       from user_ind_columns      ORDER  by Index_name, column_position;

Oracle Learning Note Nine database objects