2020-2-05 13:19×÷Õߣº À´Ô´£ºCSDN
EXPLAIN SELECT e.first_name,e.last_name,e.salary,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000; Ö´ÐиÃÓï¾ä½«»á·µ»ØÒ»¸ö±í¸ñÐÎʽµÄÖ´Ðмƻ®£¬°üº¬ÁË 12 ÁÐÐÅÏ¢£º id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra | --|-----------|-----|----------|------|-----------------|-------|-------|--------------------|----|--------|-----------| 1|SIMPLE |e | |ALL |emp_department_ix| | | | 107| 33.33|Using where| 1|SIMPLE |d | |eq_ref|PRIMARY |PRIMARY|4 |hrdb.e.department_id| 1| 100| | |
EXPLAIN ANALYZE SELECT e.first_name,e.last_name,e.salary,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000; -> Nested loop inner join (cost=23.43 rows=36) (actual time=0.325..1.287 rows=3 loops=1) -> Filter: ((e.salary > 15000.00) and (e.department_id is not null)) (cost=10.95 rows=36) (actual time=0.281..1.194 rows=3 loops=1) -> Table scan on e (cost=10.95 rows=107) (actual time=0.266..0.716 rows=107 loops=1) -> Single-row index lookup on d using PRIMARY (department_id=e.department_id) (cost=0.25 rows=1) (actual time=0.013..0.015 rows=1 loops=3) |
EXPLAIN PLAN FOR SELECT e.first_name,e.last_name,e.salary,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000; |
SELECT * FROM TABLE(DBMS_XPLAN.display); PLAN_TABLE_OUTPUT | --------------------------------------------------------------------------------------------| Plan hash value: 1343509718 | | --------------------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || --------------------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 44 | 1672 | 6 (17)| 00:00:01 || | 1 | MERGE JOIN | | 44 | 1672 | 6 (17)| 00:00:01 || | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 || | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 || |* 4 | SORT JOIN | | 44 | 968 | 4 (25)| 00:00:01 || |* 5 | TABLE ACCESS FULL | EMPLOYEES | 44 | 968 | 3 (0)| 00:00:01 || --------------------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") | filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") | 5 - filter("E"."SALARY">15000) | |
SELECT e.first_name,e.last_name,e.salary,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000; first_name|last_name|salary |department_name| ----------|---------|--------|---------------| Steven |King |24000.00|Executive | Neena |Kochhar |17000.00|Executive | Lex |De Haan |17000.00|Executive | Rows|Executes|StmtText |StmtId|NodeId|Parent|PhysicalOp |LogicalOp |Argument |DefinedValues |EstimateRows|EstimateIO |EstimateCPU|AvgRowSize|TotalSubtreeCost|OutputList |Warnings|Type |Parallel|EstimateExecutions| ----|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------|------|------|--------------------|--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------|------------|------------|-----------|----------|----------------|----------------------------------------------------------------------|--------|--------|--------|------------------| 3| 1|SELECT e.first_name,e.last_name,e.salary,d.department_name? FROM employees e? JOIN departments d ON (e.department_id = d.department_id)? WHERE e.salary > 15000 | 1| 1| 0| | | | | 2.9719627| | | | 0.007803641| | |SELECT | 0| | 3| 1| |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[department_id])) | 1| 2| 1|Nested Loops |Inner Join |OUTER REFERENCES:([e].[department_id]) | | 2.9719627| 0| 0| 57| 0.007803641|[e].[first_name], [e].[last_name], [e].[salary], [d].[department_name]| |PLAN_ROW| 0| 1| 3| 1| |--Clustered Index Scan(OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00))) | 1| 3| 2|Clustered Index Scan|Clustered Index Scan|OBJECT:([hrdb].[dbo].[employees].[emp_emp_id_pk] AS [e]), WHERE:([hrdb].[dbo].[employees].[salary] as [e].[salary]>(15000.00)) |[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id]| 3|0.0038657407| 2.747E-4| 44| 0.004140441|[e].[first_name], [e].[last_name], [e].[salary], [e].[department_id] | |PLAN_ROW| 0| 1| 3| 3| |--Clustered Index Seek(OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD)| 1| 4| 2|Clustered Index Seek|Clustered Index Seek|OBJECT:([hrdb].[dbo].[departments].[dept_id_pk] AS [d]), SEEK:([d].[department_id]=[hrdb].[dbo].[employees].[department_id] as [e].[department_id]) ORDERED FORWARD|[d].[department_name] | 1| 0.003125| 1.581E-4| 26| 0.0035993|[d].[department_name] | |PLAN_ROW| 0| 3| |
EXPLAIN SELECT e.first_name,e.last_name,e.salary,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000; QUERY PLAN | ----------------------------------------------------------------------| Hash Join (cost=3.38..4.84 rows=3 width=29) | Hash Cond: (d.department_id = e.department_id) | -> Seq Scan on departments d (cost=0.00..1.27 rows=27 width=15) | -> Hash (cost=3.34..3.34 rows=3 width=22) | -> Seq Scan on employees e (cost=0.00..3.34 rows=3 width=22)| Filter: (salary > '15000'::numeric) | |
EXPLAIN ANALYZE SELECT e.first_name,e.last_name,e.salary,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.salary > 15000; QUERY PLAN | ----------------------------------------------------------------------------------------------------------------| Hash Join (cost=3.38..4.84 rows=3 width=29) (actual time=0.347..0.382 rows=3 loops=1) | Hash Cond: (d.department_id = e.department_id) | -> Seq Scan on departments d (cost=0.00..1.27 rows=27 width=15) (actual time=0.020..0.037 rows=27 loops=1) | -> Hash (cost=3.34..3.34 rows=3 width=22) (actual time=0.291..0.292 rows=3 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 9kB | -> Seq Scan on employees e (cost=0.00..3.34 rows=3 width=22) (actual time=0.034..0.280 rows=3 loops=1)| Filter: (salary > '15000'::numeric) | Rows Removed by Filter: 104 | Planning Time: 1.053 ms | Execution Time: 0.553 ms | |
sqlite> EXPLAIN QUERY PLAN ...> SELECT e.first_name,e.last_name,e.salary,d.department_name ...> FROM employees e ...> JOIN departments d ON (e.department_id = d.department_id) ...> WHERE e.salary > 15000; QUERY PLAN |--SCAN TABLE employees AS e `--SEARCH TABLE departments AS d USING INTEGER PRIMARY KEY (rowid=?) |
sqlite> EXPLAIN ...> SELECT e.first_name,e.last_name,e.salary,d.department_name ...> FROM employees e ...> JOIN departments d ON (e.department_id = d.department_id) ...> WHERE e.salary > 15000; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 15 0 00 Start at 15 1 OpenRead 0 5 0 11 00 root=5 iDb=0; employees 2 OpenRead 1 2 0 2 00 root=2 iDb=0; departments 3 Rewind 0 14 0 00 4 Column 0 7 1 00 r[1]=employees.salary 5 Le 2 13 1 (BINARY) 53 if r[1]<=r[2] goto 13 6 Column 0 10 3 00 r[3]=employees.department_id 7 SeekRowid 1 13 3 00 intkey=r[3] 8 Column 0 1 4 00 r[4]=employees.first_name 9 Column 0 2 5 00 r[5]=employees.last_name 10 Column 0 7 6 00 r[6]=employees.salary 11 Column 1 1 7 00 r[7]=departments.department_name 12 ResultRow 4 4 0 00 output=r[4..7] 13 Next 0 4 0 01 14 Halt 0 0 0 00 15 Transaction 0 0 8 0 01 usesStmtJournal=0 16 Integer 15000 2 0 00 r[2]=15000 17 Goto 0 1 0 00 |
2021-3-31 09:18
2020-2-04 13:18
2020-2-03 13:09
2020-1-23 09:34
2020-1-23 09:32
2020-1-23 09:29