Oracle Query Optimization Overrides

-----------Books: Oracle query optimization overrides
-----------1th "c## #oracle" is the user name of the login database, and the 2nd "Oraclechange" is the name of the database to log in as the password "Oraclechange" for the login database.

/*
Create Tablespace Oraclechange
DataFile ' F:\devlopment\databases\oracle\oracleChange\oracleChange.def ' size 100M--Generate data file and define file size
Autoextend on next 100M maxsize Unlimited logging--Set auto-expand
Extent Management Local Autoallocate
Segment Space management auto;

Create user c## #oracle identified by oraclechange default Tablespace oraclechange quota 500m on users;
----The first personnel_manage here is the user name, the second mwq is the password, and the third dbsql is the tablespace name. And then execute.
Grant all privileges to c## #oracle;
---Execute this statement to Personnel_manage user authorization, at this time personnel_manage user can log on.

---Create an EMP employee 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) default 0,
Deptno number (2));

--ADD comments to the columns
Comment on column emp.empno is ' encoded ';
Comment on column emp.ename is ' name ';
Comment on column emp.job is ' work ';
Comment on column emp.mgr is ' supervisor ';
Comment on column emp.hiredate is ' hire date ';
Comment on column emp.sal is ' wages ';
Comment on column Emp.comm is ' Commission ';
Comment on column emp.deptno is ' department code ';
----------------------------------------
----Inserting data
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7369, ' Smith ', ' clerk ', 7902, to_date (' 17-12-1980 ', ' dd-mm-yyyy '), +, NULL, 20);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7499, ' Allen ', ' salesman ', 7698, to_date (' 20-02-1981 ', ' dd-mm-yyyy '), 1600, 300, 30);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7521, ' Ward ', ' salesman ', 7698, to_date (' 22-02-1981 ', ' dd-mm-yyyy '), 1250, 500, 30);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7556, ' Jones ', ' manager ', 7698, to_date (' 02-04-1981 ', ' dd-mm-yyyy '), 2975, NULL, 20);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7654, ' maritn ', ' salesman ', 7698, to_date (' 28-09-1981 ', ' dd-mm-yyyy '), 1250, 1400, 30);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7698, ' Blake ', ' manager ', 7839, to_date (' 01-01-1981 ', ' dd-mm-yyyy '), 2850, NULL, 30);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7782, ' Clark ', ' manager ', 7839, to_date (' 09-06-1981 ', ' dd-mm-yyyy '), 2450, NULL, 10);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7788, ' Scott ', ' analyst ', 7566, to_date (' 19-04-1987 ', ' dd-mm-yyyy '), +, NULL, 20);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7839, ' king ', ' president ', NULL, to_date (' 17-11-9181 ', ' dd-mm-yyyy '), 10);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7844, ' Turner ', ' salesman ', 7698, to_date (' 08-09-9181 ', ' dd-mm-yyyy '), $, null, 30);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7876, ' Adams ', ' Clerk ', 7788, to_date (' 23-05-1987 ', ' dd-mm-yyyy '), 1100, NULL, 20);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7900, ' James ', ' Clerk ', 7698, to_date (' 03-12-1981 ', ' dd-mm-yyyy '), 950, NULL, 30);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7902, ' Ford ', ' analyst ', 7566, to_date (' 03-12-1981 ', ' dd-mm-yyyy '), +, NULL, 20);
Insert into EMP (empno, ename, Job, Mgr, HireDate, Sal, Comm, Deptno)
VALUES (7934, ' milier ', ' clerk ', 7782, to_date (' 23-01-1982 ', ' dd-mm-yyyy '), 1300, NULL, 10);

*/


-----Retrieve some rows from a table (you only need to add filters when querying the data)
SELECT * from emp where job = ' salesman ';

----Find a null value

SELECT * FROM EMP where comm is NULL,---NULL does not support subtraction size compare equality comparison otherwise it can only be empty
SELECT * FROM EMP where comm are NOT NULL---NO null value query

---convert null values to actual values
Select NVL (emp.comm,0), emp.* from EMP where comm is null;---single-column query
Select COALESCE (Comm,comm) as C, emp.* from the EMP where comm is null;

----Find rows that meet multiple criteria
---Query the Employee Table Department for 10 of all employees, all employees who receive a commission, and the Department of 20 of employees with a salary of not more than $2000;
SELECT *
From EMP
WHERE (Deptno = 10
or comm is not NULL
or (sal <= and deptno = 20);

---Retrieve some of the columns from the table < in real-world development; It is often only necessary to return the data for some of the required columns >
Select Empno, ename,hiredate,sal from emp where deptno = 10;

---a meaningful name for the column
Select Ename as name, Deptno as department number from EMP order by 2;
----referencing the alias column in the WHERE clause
SELECT * FROM (select Sal as payroll, Comm as Commission from EMP) x where wage <1000;

----Stitching Columns
Select Ename | | ' The job is ' | | Job as msg from emp where deptno = 10;
---dynamically generate statements that delete table data
---Select ' Truncate table ' | | Owner | | '. ' | | table_name | | ‘;‘ As emptying table from All_tables;

----Using conditional logic in a SELECT statement
Select grade, COUNT (*) as number
From (SELECT (case
When Sal <=1000 then ' 0000-1000 '
When Sal <=2000 then ' 1000-2000 '
When Sal <=3000 then ' 2000-3000 '
When Sal <=4000 then ' 3000-4000 '
When Sal <=5000 then ' 4000-5000 '
Else ' good high '
End) as grade, ename,sal from EMP)
Group BY grade
Order by 1;

----Limit the number of rows returned (rownum each data returned in turn to make an identity;)
SELECT * from emp where rownum <=2;---Remove the first 2 rows of data
SELECT * FROM (select RowNum as SN, emp.* from EMP where rownum <=100) where sn=2;---Take out the second row of data


----randomly returns n records from the table (Dbms_random to randomly sort the data, and then takes three rows)
Select Empno,ename from (select Empno, ename from emp Order by Dbms_random.value ()) where RowNum <=3;
Select Empno,ename from (select Empno, ename from emp Order by Dbms_random.value ()) where RowNum <=3;
Select Empno,ename from (select Empno, ename from emp Order by Dbms_random.value ()) where RowNum <=3;
Select Empno,ename from (select Empno, ename from emp Order by Dbms_random.value ()) where RowNum <=3;
---------is equivalent to the following statement---The following statement execution order: 1:select 2:rownum 3:order by;
Select Empno, Ename,dbms_random.value ran from the EMP where rownum <=3 order by ran;

-----fuzzy query like '% ' means any number of characters ' _ denotes any character ' M representing any length of character '
---Create a view
Create or replace view EmpView1 as
Select ' Abcedf ' as VName from dual
UNION ALL
Select ' _BCEFG ' as VName from dual
UNION ALL
Select ' _bcedf ' as VName from dual
UNION ALL
Select ' _\bcedf ' as VName from dual
UNION ALL
Select ' Xyceg ' as vname from dual;
---requirement 1: Find out that VName contains the string ' CED '
SELECT * from EmpView1 where vname like '%ced% ';
---requirement 2: Find out that VName Z contains the string "_BCE"
SELECT * from EmpView1 where vname like ' \_bce% ' escape ' \ '; ---Note: escape identifies ' \ ' as an escape character, whereas ' \ ' escapes ' _ ' to ' character ', rather than the original

-----Return query results in the specified order
---the actual extraction of data or production reports, usually based on a certain order of view
---such as: View all employee information for a unit
Select Empno,ename,hiredate from EMP where deptno=10 ORDER by hiredate ASC;
Select Empno,ename,hiredate from EMP where deptno=10 ORDER by 3 ASC; ---the number in this notation can only appear in order by
------
Select Empno, ename,sal from EMP where deptno=10 ORDER by 3 ASC;---
Select Empno,ename, sal from EMP where comm are NOT NULL for order by 3 ASC;
----NOTE: If the column names used after order by need to be aware of the consistency before and after, otherwise the development of a little trouble

---Sort by multiple fields, Ordey by Desc/asc/number
---Ascending by department number and sorted by wage descending
Select Empno, Deptno, ename,job from emp order by 2 asc,3 desc;
-----Sort by substring
Select Last_Name as Name,
Phone_number as number,
SUBSTR (Phone_number,-4) as tail number
From Hr.employees
where RowNum <= 5
Order BY 4;

----Translate
---translate (expr,from_string,to_string)--from_string to_string is replaced by character one by one
Select translate (' AB Hello! BCADEFG ', ' abcdefg ', ' 1234567890 ') as new_str from dual;
---Returns a null value if To_string is empty
Select translate (' AB Hello! BCADEFG ', ' abcdefg ', ') as new_str from dual;
---If the to_string corresponds to a location that does not have characters, delete the characters listed in from_string
Select translate (' AB Hello! BCADEFG ', ' ABCDEFG ', ' 1 ') as new_str from dual;

----Sort the letters in a string by a combination of numbers and letters
Create or Replace view Numberview
As
Select Empno | | "| | ename as data from EMP;

----View View
SELECT * from Numberview;
---requirements: Sort by letter
Select Nv.data, Translate (nv.data, ' -1234567890 ', '-') as ename from Numberview NV ORDER by 2;
Select Nv.data from Numberview NV ORDER by translate (Nv.data, '-1234567890 ', '-');


---processing a sort null value
Select ENAME,SAL,COMM,NVL (comm,-1) Order_col from EMP order by 4 ASC;
SELECT * from emp ORDER by NVL (COMM,-1) desc;
---Use the keyword nulls first; Nulls last
SELECT * FROM emp ORDER by Comm Nulls first;
SELECT * FROM emp ORDER by Comm nulls last;

Oracle Query Optimization Overrides