Shulou(Shulou.com)06/01 Report--

Purpose: Create simple and complex views View retrieval Create, maintain and use sequences Create and maintain indexes Create private and public synonyms

Database objects: ObjectDescriptionTableBasic unit of storage; composed of rowsViewLogically represents subsets of data from one or more tableSequenceGeneratenumerical valuesIndexImproves the performance of some queriesSynonymGives alternative names to objects View Views present logical subsets or combinations of data. Views are logical tables based on tables or other views. A view does not contain its own data, but acts like a window through which you can view or change the data in the table. The table on which the view is based is called the base table. Views are dictionaries that store data in SELECT declarations.

View benefits: Views restrict access to data because they only show selected columns. Simplifying queries, views make simple queries to retrieve complex connections. Provide data independence. A view can retrieve data from multiple tables. Views provide users with access to data based on specific criteria.

Simple and complex views Simple view: Export data from only one table Contains no functions or arrays View can perform DML operations Complex view: Export data from multiple tables Contains functions or arrays View does not allow DML operations

Create View Syntax: CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery--A subquery can contain a complex select query. [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

OR REPLACE: Existing will be overwritten. FORCE: Create regardless of whether the base table exists. NOFORCE: Create using base table exists (default) view_name: view name alias: alias Specifies name for expression selected by view query (number of aliases must match number of expressions selected by view.) subquery: A subquery is a complete SELECT statement (you can use aliases in SELECT lists). WITH CHECK OPTION: Specifies that only those rows accessible to the view can be inserted or updated WITH READ ONLY: Ensures that DML operations cannot be performed on this view

DML operations on views: simple views can perform DML operations cannot perform DML operations on views with the following conditions-group functions-GROUP BY clause-DISTINCT keyword-ROWNUM keyword-columns containing expressions, such as (salary * 12)-base table columns NOT NULL are not in the view list. Remove view: DROP VIEW view_name;

Sequence A sequence is a database object that generates values. Sequences can be created and then used to generate numerical values. Sequence characteristics: -Can automatically generate unique values-It is a shared object-Can be used for primary key values-Replace application code-Its values are cached in memory to improve access efficiency

A sequence is a database object created by a user that can be shared by multiple users to generate integers. You can define a sequence to generate unique values, or reuse the same number. A typical use of a sequence is to create a primary key value where each row must be unique. This sequence is generated and incremented (or decremented) by internal Oracle routines. This can be a time-saving object because it reduces the amount of application code required to write sequence generation routines. Serial numbers are stored and generated independently. Thus, the same sequence can be used for multiple tables.

Syntax for creating sequences: CREATE Sequence sequence_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n| NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

INCREMENT BY n： Specifies the spacing between sequence numbers, where n is an integer (If this value is omitted, the sequence is incremented by 1) START WITH n: Specifies the first sequence number to generate (If this value is omitted, the sequence starts with 1) MAXVALUE n: Specifies the maximum value that the sequence can generate NOMAXVALUE: Specifies the maximum value of 10^27 for increasing sequences,-1 for decreasing sequences (default option) MINVALUE n: Specifies the minimum sequence value NOMINVALUE: Specifies the minimum value of 1 for increasing sequences, -(10^26) for increasing sequences (default option) CYCLE| NOCYCLE: Specifies whether the sequence continues to generate values when it reaches a maximum or minimum (NOCYCLE is the default) CACHE| NOCACHE: Specifies Oracle Server Prescaler and the value to keep in memory (Oracle Server caches 20 values by default)

CREATE SEQUENCE sequence_t INCREMENT BY 1 START WITH 10 MAXVALUE 999 NOCACHE NOCYCLE; NEXTVAL and CURRVAL pseudo-columns are required after sequence generation to reference sequence values NEXTVAL: pseudo-columns are used to extract consecutive sequence numbers from a specified sequence. Nextval must be qualified with sequence names. When reference sequence.nextval is, a new sequence number is generated and the current sequence is placed in currval. CURRVAL: Pseudo columns are used to reference the serial number that the current user has just generated. Nextval must be used to generate a sequence number in the current user session before currval can be referenced. 16:53:05 SQL> select sequence_t.nextval from dual; NEXTVAL ---------- 10 16:53:17 SQL> select sequence_t.currval from dual; CURRVAL ---------- 10

Cache sequence values: Cache sequences in memory for quick access to these sequence values. The buffer is automatically filled when the sequence is referenced for the first time. Retrieves the next sequence value from the cached sequence, and after using the last sequence value, the next request for the sequence pulls the cache of another sequence into the memory cache. Loss of sequence: -Rolling back in a transaction results in value loss when values of a sequence are continuously generated. - Crash of the system, resulting in loss of sequences in cache. - Multiple tables share the same sequence, resulting in sequence loss.

Modify sequence: ALTER SEQUENCE sequence_name INCREMENT BY 20 MAXVALUE 99999 NOCACHE NOCYCLE;

DROP SEQUENCE sequence_name;

An index is a database object used to improve the performance of certain queries. The database is also created automatically when you create a primary key or uniqueness constraint.

Index: -It is a schema object-It uses pointers to associate each row to improve access speed-It reduces disk I/O and provides fast access to local data-It relies on tables-The database server automatically maintains and uses it.

An Oracle server index is a schema object that can be used to speed up row retrieval through the use of pointers. Indexes can be created explicitly or automatically. If there is no index on the column, a full table scan occurs. Indexes provide direct and fast access to rows in tables. The goal is to reduce disk I / O by using index paths to quickly locate data. This index is automatically maintained and used by Oracle servers. After the index is created, no direct user activity is required. Indexes are logically and physically independent of index tables. This means that they can be created or deleted at any time without affecting the base table or other indexes. Note: When you delete a table, the corresponding index is also deleted. Index type: Unique index: PRIMARY KEY and UNIQUE constraints automatically create a unique index. Non-unique index: user-defined index creation. Note: When creating a unique index, it is recommended to create a uniqueness constraint because it implicitly creates a unique index.

Create index syntax: CREATE INDEX schema.index_name ON schema.table_name (column[,column]...);

Conditions for creating an index: - Large span of data on column-Large number of null values on data on column-One or more columns are often used together in WHERE clause or join condition-The table is large, most queries are expected to retrieve less than 2% to 4% of rows Indexing is not recommended: -Indexed column is not used for SQL statement queries-Retrieves data greater than 2% or 4% of total data-Large number of update operations on column-Column queries are used for expressions

Note: More indexes are not better, and more indexes on a table do not produce faster queries. DML operations on tables with indexed columns mean that the index must also be updated. Index-dependent tables are deleted, and indexes are deleted.

Drop index: DROP INDEX schema.index_name ; Rebuild index: ALTER INDEX schema.index_name REBUILD [PARALLEL n];

14:40:24 SQL> create index hr.hr_emp_01 on hr.emp_01(salary); 14:40:51 SQL> alter index hr.hr_emp_01 rebuild parallel 4; 14:41:06 SQL> drop index hr.hr_emp_01;

Synonym Synonym is a database object that can call tables (other objects) with other names. Synonyms can be created to give an alternative name to a table (or other object). Create Syntax: CREATE [PUBLIC] SYNONYM synonym_name FOR schema.object_name; Synonym Category: PUBLIC: Public synonym, accessible to other users. PRIVATE: Private synonym that can only be owned or accessed by the creating user (default). Remove synonyms: DROP [PUBLIC] SYNONYM synonym_name; Note: public synonyms Users must have drop public synonym permission to delete. 14:57:16 SQL> drop public synonym dbmonitor_book2 15:00:20 2 ; drop public synonym dbmonitor_book2 * ERROR at line 1: ORA-01031: Insufficient permissions 15:00:25 SQL> drop synonym dbmonitor_book2 15:00:31 2 ; synonym deleted.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.