Optimize the number of table scans

Optimization of the number of table scans has recently encountered some SQL optimization problems during interviews and study. The following is a summary of the following: information about employees whose salaries are higher than the average salary: For this question, the first query statement we wrote was: select * from e where sal> (select avg (sal) from e, the execution plan is as follows: [SQL] [SQL] create table e as select * from emp; [SQL] SQL> select * from e where sal> (select avg (sal) from e ); execution Plan -------------------------------------------------------- Plan hash value: 1174284194 -------------------------------------- ------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | consumed | 0 | select statement | 1 | 87 | 6 (0) | 00:00:01 | * 1 | table access full | E | 1 | 87 | 3 (0) | 00:00:01 | 2 | sort aggregate | 1 | 13 | 3 | table access full | E | 14 | 182 | 3 (0) | 00:00:01 | --------------- Extract [SQL] Predicate Information (identified by operation id): --------------------------------------------------- 1-filter ("SAL"> (SELECT AVG ("SAL") FROM "E" "E ")) note ------dynamic sampling used for this statement (level = 2) Statistics -------------------------------------------------------- 0 recursive CILS 0 db block gets 7 consis Tent gets 0 physical reads 0 redo size 1263 bytes sent via SQL * Net to client 524 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed [SQL] Two full table scans and seven logical reads. Now we use the analysis function to optimize this query select * from (select avg (sal) over (partition by 1) as avg_sal, e. * from e) w where w. sal> avg_sal; [SQL] SQL> select * from (select avg (sal) over (partition 1) as avg_sal, e. * from e) w where w. sal> avg_sal; Execution Plan -------------------------------------------------------- Plan hash value: 1316326686 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ---------------------------------------------------------------------------- | 0 | select statement | 14 | 14 00 | 3 (0) | 00:00:01 | * 1 | VIEW | 14 | 1400 | 3 (0) | 00:00:01 | 2 | window buffer | 14 | 1218 | 3 (0) | 00:00:01 | 3 | table access full | E | 14 | 1218 | 3 (0) | 00:00:01 | identified by operation id: ----------------------------------------------------------------- 1-filter ("W ". "SAL"> "AVG_SAL") Note ------Dynamic sampling used for this statement (level = 2) statistics defaults 0 recursive cballs 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1380 bytes sent via SQL * Net to client 524 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 6 rows processed can be seen that only one table scan is performed, and the logical read is reduced to 3. 2. from the above example, we can extend to the general statement for the condition that the average salary of the Department is greater than: select * from e, (select deptno, avg (sal) avg_sal from e group by deptno) w where e. deptno = w. deptno and e. sal> w. avg_sal; use the analysis function statement: select * from (select avg (sal) over (partition by deptno) as avg_sal, e. * from e) w where w. sal> w. avg_sal; [SQL] SQL> select * from e, (select deptno, avg (sal) avg_sal from e group by deptno) w where e. deptno = w. deptno and e. sal> w. avg_sal Plan Hash value: 3522624553 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 3 | 339 | 8 (25) | 00:00:01 | * 1 | hash join | 3 | 339 | 8 (25) | 00:00:01 | 2 | table access full | E | 14 | 1218 | 3 (0) | 00: 0 00:00:01 | 3 | VIEW | 14 | 364 | 4 (25) | 4 | hash group by | 14 | 364 | 4 (25) | 00:00:01 | 5 | table access full | E | 14 | 364 | 3 (0) | 00:00:01 | explain SQL> select * from (select avg (sal) over (partition by deptno) as avg_sal, e. * from e) w where w. sal> w. avg_sal; Plan hash value: 798854145 --------------------- ------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 14 | 1400 | 4 (25) | 00:00:01 | * 1 | VIEW | 14 | 1400 | 4 (25) | 00:00:01 | 2 | window sort | 14 | 1218 | 4 (25) | 00:00:01 | 3 | table access full | E | 14 | 1218 | 3 (0) | 00:00:01 | Issue 3. query the merchant information for only one product 'pc' [SQL] create table test (name nvarchar2 (10), type nvarchar2 (20 )); insert into test values ('A', 'pc'); insert into test values ('B', 'pc'); insert into test values ('B ', 'printer'); insert into test values ('C', 'printer'); select * from test; name type ---------- ------------------ A PC B PC B PRINTER C PRINTER my previous understanding is that as long as the merchants who sell non-'pc' products are excluded, they will get the merchants who only sell 'pc, therefore, the following query statement is used: select name from test a where not exists (select 1 from test B where. name = B. name and B. type <> 'pc'); Execution Plan: [SQL] scheduler | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | average --------------------------------------------------------------------------- | 0 | select statement | 4 | 184 | 7 (15) | 00:00:01 | * 1 | hash join anti | 4 | 184 | 7 (15) | 00:00:01 | 2 | table access full | TEST | 4 | 48 | 3 (0) | 00:00:01 | * 3 | table access full | TEST | 2 | 68 | 3 (0) | 00:00:01 | ----------------------------------------------------------------------------- optimization statement: select name from test group by name having sum (decode (type, 'pc', 0, 1) = 0; [SQL] Plan Hash value: 1349321023 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 4 | 136 | 4 (25) | 00:00:01 | * 1 | FILTER | 2 | hash group by | 4 | 136 | 4 (25) | 00:00:01 | 3 | table access full | TE ST | 4 | 136 | 3 (0) | 00:00:01 | ---------------------------------------------------------------------- 4. Maximum salary information of each department 1. Self-connection mode: select * from emp a where sal = (select max (sal) max_sal from emp w where w. deptno =. deptno) order by. deptno; 2. inline view: select. * from emp a, (select max (sal) max_sal, deptno from emp group by deptno) w where w. deptno =. deptno and. sal = w. max_sal order by. deptno; 3. Analysis letter Number: select * from (select max (sal) over (partition by deptno) max_sal,. * from emp a) w where w. max_sal = w. sal order by w. deptno; 3. The method for analyzing functions is the most efficient. This example can be used to query the Last login information of a user. The above optimization purposes are to reduce subqueries and reduce table scans to optimize queries.