Complex Oracle multi-Table query

I. Paging query:

1. Minute by rowid
Select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from (select rowid rid, cid from t_xiaoxi order by cid desc) where rownum <10000) where rn> 9980) order by cid desc;
Execution time: 0.03 seconds

2. Score by Analysis Function
Select * from (select t. *, row_number () over (order by cid desc) rk from t_xiaoxi t) where rk <10000 and rk> 9980;
Execution time: 1.01 seconds

3. Divide by rownum
Select * from (select t. *, rownum rn from (select * from t_xiaoxi order by cid desc) t where rownum <10000) where rn> 9980;

Execution time: 0.1 seconds

Minute by rownum
A. rownum Paging
SELECT * FROM emp;
B. Display rownum [allocated by Oracle]
SELECT e. *, ROWNUM rn FROM (SELECT * FROM emp) e;
Rn is equivalent to the ID of the row allocated by Oracle.
C. Select 6 to 10 records
First, 1-10 records are found.
SELECT e. *, ROWNUM rn FROM (SELECT * FROM emp) e where rownum <= 10;
If rownum> = 6 is not followed,
Then find 6-10 records
SELECT * FROM (SELECT e. *, ROWNUM rn FROM (SELECT * FROM emp) e where rownum <= 10) WHERE rn> = 6;
* Specify the query column. You only need to modify the subquery at the innermost layer.

SELECT * FROM (SELECT e. *, ROWNUM rn FROM (SELECT ename, sal FROM emp) e where rownum <= 10) WHERE rn> = 6;

* For sorting queries, you only need to modify the subqueries at the innermost layer.
Query 6-10 pieces of data after sorting the salary
SELECT * FROM (SELECT e. *, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e where rownum <= 10) WHERE rn> = 6;

2. Create a new table using the query results
Create table mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

3. Merge queries

In practice, sometimes, to merge the results of multiple select statements, you can use the union, union all, intersect, and minus operators.
It is mostly used in data Bureaus with a large amount of data and runs fast.
1). union
This operator is used to obtain the union of two result sets. When this operator is used, repeated rows in the result set are automatically removed.
SELECT ename, sal, job from e where sal> 2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'manager ';
2). union all
This operator is similar to union, but it does not cancel duplicate rows and does not sort.
SELECT ename, sal, job from e where sal> 2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'manager ';
This operator is used to obtain the union of two result sets. When this operator is used, repeated rows in the result set are automatically removed.
3). intersect
Use this operator to obtain the intersection of two result sets.
SELECT ename, sal, job from e where sal> 2500
INTERSECT
SELECT ename, sal, job FROM emp WHERE job = 'manager ';
4). minus
Use the transform operator to obtain the difference set of two result sets. It only shows that the first set exists, but not the data in the second set.
SELECT ename, sal, job from e where sal> 2500
MINUS
SELECT ename, sal, job FROM emp WHERE job = 'manager ';
(MINUS means subtraction)

4. Left and Right connections
1. Inner join (natural join): Only the rows matching the two tables can appear in the result set.
2. external connections: including
(1) left Outer Join (the table on the left is not restricted) select a. studentno, a. studentname, B. classname
From students a, classes B where a. classid = B. classid (+ );
(2) Right Outer Join (the table on the right is not restricted) select a. studentno, a. studentname, B. classname
From students a, classes B where a. classid (+) = B. classid
(3) Full outer join (no restrictions are imposed on both the left and right tables)

In short,
The left join shows all on the left and the right are the same as those on the left.
The right connection shows all and the left are the same as the right
Only matching conditions are displayed for internal connections!
Supplement:
Notes for using (+:
1. the (+) operator can only appear in the where clause and cannot be used with the outer join syntax.
2. When an external join is executed using the (+) operator, if the where clause contains multiple conditions, the (+) operator must be included in all conditions.
3. the (+) operator cannot be used with the or and in operators.
4. the (+) operator can only be used to implement left Outer Join and right outer join, but not to implement full outer join.

Oracle Parallel Query

Oracle user information query operation statement

Performance problems of querying the maximum and minimum values of a column in a single Oracle table

The recycle bin causes slow usage of Oracle query table space