Oracle study notes 9 database objects, oracle Study Notes

Oracle study notes 9 database objects, oracle Study Notes
Oracle database objects are also called schema objects. database objects are a set of logical structures, and the most basic database objects are tables. Other database objects include: synonym is an alias of an existing object. It has the following functions: 1. Simplified SQL statement 2. Hiding the Object Name and owner (hiding the original object content) 3. There are two types of public access synonyms for objects:

Public synonyms can be accessed by all database users.

Private synonyms can only be accessed in the current mode, and cannot have the same name as the objects in the current mode.

Create or replace an existing Synonym

create or replace synonym emp_syn for scott.emp;

 

Delete Synonym

drop synonym emp;drop public synonym emp_syn

 

Synonym

Create public synonym Newer for scott.emp2;Select * from Newer ;

A sequence is an object used to generate a unique or continuous sequence number. A sequence can be in ascending or descending order and can be used to represent the number of data. We can use the create sequence statement to CREATE a SEQUENCE.

Create sequence name Start with 1-indicates the sequence Start size. The default value is 1 Increment by 1-each Increment, the default value is 1 Maxvalue 2000 -- the maximum number of sequences Minvalue 1 -- the minimum number of sequences Nocycle -- when the maximum number of sequences is reached, no INTEGER (default) is generated. Cache 10; -- retain the number of integers in the memory. The default value is 20.

Note: When using a sequence, if the sequence is given a primary key, it should be set to NOCACHE to prevent discontinuous conditions.

 

All sequence information is found from user_sequences.

select * from user_sequences;

 

Access the value of the sequence through the pseudo column of the sequence 1. NEXTVAL returns the next value of the sequence 2. CURRVAL returns the current value of the sequence

Note: When using a sequence, if the sequence is given a primary key, it should be set to NOCACHE to prevent discontinuous conditions.

Insert into toys (toyid, toyname, toyprice) VALUES (VALUES, 'twenty', 25); insert into toys (toyid, toyname, toyprice) VALUES (toys_seq.NEXTVAL, 'Magic lock ', 75); -- toys_seq.NEXTVAL specifies the next value of the sequence

 

SELECT toys_seq.CURRVAL FROM dual; -- retrieves the current value of the sequence

 

Use the alter sequence statement to modify the SEQUENCE. The start with parameter of the SEQUENCE cannot be modified.

ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

 

Use the drop sequence statement to delete a SEQUENCE

DROP SEQUENCE toys_seq;

 

The view displays data from one or more tables in a customized manner. The view can be regarded as a "virtual table" or "Storage query ", the table on which the view is created is called a base table ". View advantages: 1. provides another level of table Security 2. complexity of hidden data 3. simplified SQL command 4. change the structure of the isolated base table 5. you can rename a column to provide a data example from another perspective:

CREATE VIEW stud_view AS SELECT studno, studname, subno FROM Stud_details;

The query results are as follows:

Syntax for creating a view:

CREATE [or replace] [FORCE] VIEW view_name [(alias [, alias]...)] AS select_statement [with check option] [CONSTRAINT C_NAME] [with read only]; -- disable update and delete operations on tables -- w ith check option constraint _ NAME: adds a check constraint, the INSERT and UPDATE operations must follow the following requirements: -- during the INSERT operation, the inserted data must comply with the where conditions set during the definition, during UPDATE, only data columns other than the where condition column can be modified, or the value of the modified condition column can meet the where condition --

 

Use the with check option to create a view

create or replace view pause_view as　　select * from order_master WHERE ostatus = 'p'　　with check option constaint chk_pv;

 

Use the order by clause to create a view

 

create or replace view ord_ven as　　select * from vendor_master order by venname;

 

 

Join view: Results of two tables

create view Stud_sub_view as    select Studno, Studname, Submrks, Subname    from Stud_details, Sub_Details     where Stud_details.Subno=Sub_details.Subno;

 

Create external join View

CREATE VIEW ven_ord_outj_view ASSELECT vm.vencode, venname, orderno, odate, ostatusFROM   vendor_master vm, order_master omWHERE  vm.vencode = om.vencode(+);

Equivalent

SELECT vm.vencode, venname, orderno, odate, ostatusFROM vendor_master vm LEFT OUTER JOIN order_master omON vm.vencode = om.vencode;

 

DML statements on the view can also be used to modify data in the view. For example, the DML statements on the INSERT, UPDATE, and DELETE views have the following restrictions: 1. only one underlying base table can be modified. if the modification violates the constraints of the base table, the view cannot be updated. if a view contains join operators, DISTINCT keywords, set operators, Aggregate functions, or group by clauses, View 4 cannot be updated. if a view contains a pseudo column or expression, the view cannot be updated. If data is added to the view, the data can be added to the base table, however, if the inserted data does not meet the where and other conditions of the view, the view is not displayed. If the data to be inserted must comply with the view definition, you need to add parameters to the where statement. If you want to delete the data of the base table from the view, the deleted data must meet the query conditions of the view to delete the data through the view. Key-reserved table key-reserved table is a basic concept for understanding the restriction on modifying the connection view. All primary key columns of the table are displayed in the view, and their values are unique and not empty in the view. That is to say, the table's key value is also a key value in a connection view, so this table is called a key-preserving table.

Because Studno is both the primary key in Stud_details and the primary key in the join result, Stud_details is the key-retained table.

You can use single-row functions, grouping functions, and expressions in the function view.

CREATE VIEW item_view AS     SELECT itemcode, LOWER(itemdesc) item_desc    FROM itemfile; 

 

Delete a view using the drop view statement

DROP VIEW toys_view; 

 

Index indexes are an optional table-related structure. Using indexes can improve SQL statement execution performance and reduce disk I/O. indexes are logically and physically independent of table data, oracle can automatically maintain indexes.

Indexing principle: the indexing principle is closely related to a concept called ROWID. When creating a data table, Oracle creates an implicit field named ROWID for each data table by default. When a record is inserted into a data table, the system automatically assigns a unique ROWID for each record. With this ROWID, you can quickly locate the record.

Use the create index statement to Create an index: CREATE a standard INDEX: Create index INDEX name on table name (column name );

CREATE INDEX item_index ON itemfile (itemcode)     TABLESPACE index_tbs;

 

Re-Indexing

ALTER INDEX item_index REBUILD; 

 

Delete Index

DROP INDEX item_index; 

 

There are various types of index types. Apart from standard indexes, there are also some special types of indexes: Unique indexes ensure that there are no duplicate values in the columns that define the index, oracle automatically creates a unique index on the primary key column of the table. Use the create unique index statement to CREATE a unique index.

CREATE UNIQUE INDEX item_index     ON itemfile (itemcode);

Composite index is an index created on multiple columns of a table. The column order in the index is arbitrary, if the WHERE clause of an SQL statement references all or most columns of the composite index, the retrieval speed can be improved.

CREATE INDEX comp_index     ON itemfile(p_category, itemrate);

Reverse key index reverse key index reverses each byte of the index column key value, usually created on a column whose value is continuously increasing, the REVERSE keyword can be used to evenly distribute data throughout the index creation.

CREATE INDEX rev_index      ON itemfile (itemcode) REVERSE;

 

ALTER INDEX rev_index REBUID NOREVERSE;

Bitmap index Bitmap indexes are suitable for low-base columns. Instead of storing ROWID directly, bitmap indexes store the ing between byte and ROWID. Bitmap indexes can reduce response time and reduce space usage.

CREATE BITMAP INDEX bit_index     ON order_master (orderno);

 

Index organization table data is stored in the associated index, the index stores the actual data of the row, rather than the ROWID, which accesses data based on the primary key. The create table command is used together with the organization index clause to CREATE an index organization table.

CREATE TABLE ind_org_tab (       vencode NUMBER(4) PRIMARY KEY,       venname VARCHAR2(20)     )      ORGANIZATION INDEX;

 

Comparison between a common table and an indexed Organizational table

Normal table	Index organization table
ROWID uniquely identifies a row	The primary key uniquely identifies a row.
Implicit ROWID Column	No implicit ROWID Column
ROWID-Based Access	Primary Key-Based Access
Sequential Scan returns all rows	Full index scan returns all rows in the primary key order
Partitions are supported.	Partitions are not supported.

Function-based indexes are indexes created based on functions or expressions on one or more columns. Aggregate functions cannot appear in expressions and cannot be created on LOB columns, you must have the query rewrite permission when creating a QUERY. When a function is used for a QUERY, the index is no longer used. Therefore, you must create a function index.

CREATE INDEX lowercase_idx      ON toys (LOWER(toyname));

 

SELECT toyid FROM toys     WHERE LOWER(toyname)='doll';

 

Partitions in the index can be stored in different partitions. Partition-related indexes have three types: 1. partial partition index-the index created on the partition table creates an independent index on each table partition. The partition range of the index is the same as that of the table. global partition index-an index created on a partitioned or non-partitioned table. The index specifies the partition range separately, regardless of the partition range or whether the table is partitioned. global non-partition index-the global common index created on the partition table. The index information that is not obtained by the partition is related to the index. The data dictionary views include: 1. USER_INDEXES-user-created index information 2. USER_IND_PARTITIONS-partition index information created by the user 3. USER_IND_COLUMNS-index-related table column information

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME     FROM USER_IND_COLUMNS     ORDER BY INDEX_NAME, COLUMN_POSITION;