Oracle Study Notes-(4)

Oracle Study Notes --- (4)

Thu

Create a user and authorize the user in the System user:
Create user usera identified
By usera defalut tablespace test;
Grant connect, resource
Usera;

1. Synonyms

Private and public

PRIVATE: created by a common user. Only the user who creates the synonym can use it.
Prerequisites: normal users have access rights to Scott's table EMP.


Connect Scott/tiger;
Grant all on EMP to usera;
-- All includes select, update, insert, and delete.

Connect usera/usera;
Create synonym EMP for Scott. EMP;

Select * from EMP;
Public: the public is generally created by the DBA and must have the create public Synonym
System permissions, required for normal users to create
Connect system/manager;
Grant create
Public synonym to usera;
Conect usera/usera;

Create public synonym EMP
Scott. EMP;
Other users can also use the public synonym EMP.


View the synonym user_synonyms created by the user.
Select synonym_name from
User_synonyms;

Ii. Sequence

It is usually used with a table to generate a unique primary key value. It is used when data is inserted, but does not belong to any table and exists independently of the table.

Create
Sequence deptseq
Increment by 10
Start
With 50

Insert
Into dept values (deptseq. nextval, 'sale _ dept', 'haizhustreet ');

Use pseudo columns nextval and currval
Nextval: the initial value of the returned sequence is used for the first time after the sequence is created.
With specified value, subsequent incremental value
Currval: returns the current value of the sequence.
Data Dictionary:
User_sequences

3. View

You can use group by, order by clause, and functions when creating a view. You must specify the column alias when using a function.
1,
Simple view: view created by querying a single table

You can add, delete, modify, and delete views, except for some restrictions, such as creating read-only views or having
Option option view, or the view does not contain
Columns that are not allowed to be empty in the base table (data cannot be inserted into the view ).

1) You can use with check option to restrict modifications to a single-Table view. You cannot update rows that cannot be viewed through this view.
Create
Or replace view emp_view as select * from EMP where job = 'salesman' with check
Option constraint empv;

SQL> select * From emp_view;

Empno ename job Mgr hiredate Sal
Comm deptno
-------------------------------------------------
------------------------------
7499 Allen salesman 7698
20-2 months-81 1600 300 30
7521 ward
Salesman 7698 22-2 months-81 1250 500 30
7654
Martin salesman 7698 28-9 month-81 1250 1400
30
7844 Turner salesman 7698-81
1500 0 30

Update emp_view set job = 'cler' where empno = 7499; will not be updated

If the view is not created with check option, the row of data cannot be found in the view after the view is modified.

2) use with read only to create a read-only view
Create or replace
View emp_view as select * from EMP where job = 'salesman' with read
Only;
3) use force to create a view.

If the view definition references a non-existent table or a table with a column that does not exist, or the owner of the created view does not have the permission to access the table, you can create a view,

However, an error occurred while creating the view. After creating a table later or obtaining access permissions, Oracle automatically re-compiles the view to make the view valid. You can manually compile the view.


Assume that the dept table does not contain the A1 column, but you can create a view, but an error is prompted. After the dept table adds the column A1, the view is automatically compiled to be valid.
Create or
Replace Force view emp_view as select A1, dname from Dept;
Yes
Use alter view emp_view compile; manually compile


2. complex view
:
1) use multi-table join to create a view,
2) When creating a view, including functions or group

Complex views cannot be directly added, deleted, or modified. However, you can use instead
Triggers can be modified (subsequent courses)
Create or replace view emp_dept_view as select
Empno, ename, dname, EMP. deptno
From Dept, EMP where
Dept. deptno = EMP. deptno;

3,
Key reserved table
Key reserved table: the table that appears in a complex view.
If conditions are met: 1)
All primary key columns are displayed in the view, and their values are unique and non-empty in the view.
For example, create or replace View
Emp_dept_view
Select empno, ename, dname, Dept. deptno

From Dept, EMP where Dept. deptno = EMP. deptno;

Empn is the primary key of the EMP table and its value is unique and non-empty in emp_dept_view. Therefore, the EMP table is a key-preserving table.

The value of deptno is not unique in the view, so DEPT is not a key reserved table.
Yes
Modify the table data retained by the middle key in the view, except for the primary key value.
Update emp_dept_view set ename = 'job' where
Empno = 7566; // Yes
Update emp_dept_view set dname = 'abc' where
Deptno = 30; // No dname is the column in the dept table, and DEPT is not the key reserved table.


4. Partition View
Create or replace view v_emp as select * from
Emp1 partition (P1) Union all select * From emp1 partition (P2 );

Data Dictionary: user_views
Usage: in SQL * Plus
Col
View_name for A10 -- customize the displayed column width
Col text
A50
Set linesize 200 -- set the number of characters displayed in each line

Select view_name, text from user_views;
-- View the view created by the user

Iv. Index

Purpose of using indexes:
1) force unique: In primary
The key column or the unique column automatically creates a unique index.
2) Improved query speed: When an index is used for a query, the query speed will be dramatically improved.

The index is used only when where or order by is used in the query.
1. Unique Index
Create
Unique index ind2 on dept (deptno );
In the column defining the index, the values of any two rows in the table are different.
2,
Composite Index
Index created on multiple columns of a table
Create index comind1 on
EMP (deptno, empno)
3. Reverse Key Index
This index reverses every byte in the index column.

Distribute the insert operation on the entire index to avoid reducing the index performance when inserting data. However, it slows down queries.
Create index revind1 on
Salgrade (grade) reverse;

You can change the reverse key index to a standard index.
Alter
Index revind1 rebuild noreverse;

4. Bitmap index (which can be created on one or more connected tables)
If the number of column repetitions exceeds 100, you can create a bitmap index on the column. When the number of different values is smaller than the number of rows
Table 1000000
Rows. A column has 10000 different values. You can create a bitmap index on the column.
In Bitmap indexes, bitmap of each key value is used instead of rowid. Each bit in the bitmap corresponds to a possible rowid.

Ing Functions
Each position --------- rowid (determines a row in the table)
When the WHERE clause contains and or
Or conditions, you can directly perform Boolean operations on the in-place graph.
Create bitmap index bitemp on
EMP (deptno );

5. index the Organizational table
The table is accessed based on the primary key instead of the rowid.
The data in the table is stored like an index, and the table does not have a rowid. Therefore, you cannot
Index organization table
Select the value of the rowid pseudo Column
For queries that require exact matching and range-based searches, it provides a faster way to access data.
Table data is stored in the associated index, and rowid is not stored in the index.
Instead, it stores the actual data of the table.

Create Table indorg (

A1 number (2) primary key,

A2 varchar2 (10)
)
Organization
Index;
You cannot create indexes on other columns of the indorg table. When the data changes little, you can use indexes to organize tables.


6. function-based indexes

If
If a function is used on a column, the index is not used even if an index is created on the column. Therefore, you can create function indexes.
Create index exind1 on
EMP (length (ename); -- creates an index based on the Function
Create index exind2 on
EMP (SAL + nvl (Comm, 0); -- creates an index based on an expression

7. Key compression index
To save the space of the index block, the same column value is stored once as the prefix, And the suffix is different rowid

Create index exind1 on EMP (length (ename) compress 1;
-- The number of key columns processed as the prefix is 1.
Create index exind2 on EMP (deptno) compress
1;

8. Index partitioning


After you create a partition table, you can also create an index on the table. This index can be divided according to the same value range used for table partitions.

Create Table
Emp1 (
Empno number (4 ),
Ename varchar2 (10 ),
Job
Varchar2 (9 ),
Mgr number (4 ),
Hiredate date,

Sal number (7,2 ),
Comm number (7, 2 ),

Deptno number (2 ))
Partition by range (empno)
(

Partition P1 values less than (7566 ),
Partition p2
Values less than (7900 ),
Partition P3 values less
Than (maxvalue ))


1) use local
Keyword. You can create an independent index for each partition,
This index command creates three independent indexes, each of which corresponds to one partition.

Data Dictionary:
Select index_name, partition_name, tablespace_name from
User_ind_partitions

I) Local index without a prefix
Create index emp_ind1 on
Emp1 (ename) Local

Ii) Local prefix index (the first column of the index is the same as the first column of the partition)
Create
Index emp_ind1 on emp1 (empno) local;
Or
Specify different index tablespaces
Create index emp_ind1 on emp1 (empno)
Local
(Partition P1 tablespace
Test10000idx,
Partition P2 tablespace
Test2_idx,
Partition P3 tablespace
Test3_idx)
If the partition is modified, the index is automatically rebuilt.
SQL> Col segment_name
For A20
SQL> select
Segment_name, partition_name, segment_type, tablespace_name from user_segments
Where segment_name = 'emp_ind1 ';

Segment_name partition_name segment_type
Tablespace_name
--------------------------------------------------
------------------------------------------------
Emp_ind1
P1 index partition system

Emp_ind1 P2 Index
Partition system
Emp_ind1 p3
Index partition system

2) Use global
Keyword to create a global index
I) global index without prefix
Create Index
Emp_ind2 on emp1 (ename) global;

Ii)
Global prefix Index
Create a global prefix index (empno is the prefix and ename cannot be used as the index column)

Create index emp_ind2 on emp1 (empno, ename)

Global
Partition
By range (empno)
(

Partition P1 values less than (6000 ),

Partition P2 values less than (7500 ),

Partition P3 values less
(Maxvalue)
)
Any operation on the partition will invalidate the global index. You need to re-create the index.

Note: you cannot manually delete the index partition. When the referenced data is deleted from the table partition, the index partition is automatically deleted.

5. Clusters

A cluster is a group of tables consisting of two or more tables with public columns.
A cluster is used to store tables. These tables are closely related to each other and are usually connected to the same area of the disk.
E. g.: EMP
The table is stored in a disk area (section)
And the dept table is stored in another region. Their rows can be connected to the same region.
The cluster keyword is usually one or several columns (such as deptno in dept and EMP) that join the table in the query. to cluster these tables, you must
Is the table owner.

1. An empty cluster is created.
Create
Cluster emp_dept (deptno_key number (2 ));
An empty cluster is created (for example, a space is also set like a table)

2. Create a table in the Cluster
Create Table Dept
(

Deptno number (2) primary key,
Dname char (14 ),

Local char (13)
)
Cluster emp_dept (deptno );

-- Deptno is the name of the column to be stored in the cluster keyword deptno_key in the table.

Create Table
EMP
(
Empno number (4 ),
Ename varchar2 (10 ),
Job
Varchar2 (9 ),
Mgr number (4 ),
Hiredate
Date,
Sal number (7,2 ),
Comm number (7, 2 ),
Deptno number (2)
References
Dept
)
Cluster emp_dept (deptno );

3. Create a cluster index (create a cluster index before inserting data to the cluster table)
Create Index
Emp_dept_ndx on cluster emp_dept;

4. insert data

Insert into EMP select * from Scott. EMP;
Insert
Dept select * from Scott. Dept;

The data of the two tables is actually stored in the same location. The cluster seems to be a big table that contains the data of the two tables.