DAY16-dong Soft training. Oracle. Index

2018-07-12 14:44:27

Iv. Index
1. Create an index
Create manually:
Create index index name on table name (column name, [column name, ...] ）
CREATE TABLE Employee (
Pno Number (7),
PName VARCHAR2 (20)
);

CREATE index Inx_scott_pno on employee (pno);
INSERT into employee select Empno,ename from EMP;

SELECT *
From employee
where pno=7788;

---inserting data recursively
INSERT INTO employee SELECT * from employee;

Update employee Set pno = rownum;
Function Index:
CREATE index Inx_scott_pname on employee (upper (ename));

CREATE index Inx_scott_pname_pno on employee (pno,pname);

Automatically create indexes: indexes are created automatically when a primary key or unique key is created

2. Delete index:
Drop index name;

Drop INDEX Inx_scott

----Exercise 2
1. Create the test table using the subquery method.
CREATE TABLE Test as SELECT * from EMP;
SELECT * from Test;

2. Quickly copy the data from the test table and copy it to around 100w
---incremental insertion
INSERT INTO Test select * from EMP;
SELECT * from Test;

3. Update the Empno field in the test table rownum
Update test set Empno=rownum;
SELECT * from Test;

4. Query the record value of Empno 800000 in test to record the query execution time.
SELECT *
From Test
where empno=800000;

5. Create an index on the Empno field of the test table
CREATE INDEX inx_scott_empno on test (EMPNO);

6. Re-implement question 4th, compare query time

DAY16-dong Soft training. Oracle. Index