CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) )
Table created.
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT)
Table created.
begin
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
end;
Statement processed.
select * from dept join emp using(deptno)
DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 17-DEC-80 | 800 | - | 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600 | 300 | 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250 | 500 | 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 | - | 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250 | 1400 | 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 | - | 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450 | - | 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 | 3000 | - | 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | - | 17-NOV-81 | 5000 | - | 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 | 1500 | 0 | 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 | 1100 | - | 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 03-DEC-81 | 950 | - | 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 03-DEC-81 | 3000 | - | 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 23-JAN-82 | 1300 | - |
---|
explain plan for select * from dept join emp using(deptno)
Statement processed.
select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT | Plan hash value: 2219294842 | --------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | --------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 14 | 1638 | 6 (0)| 00:00:01 | | |* 1 | HASH JOIN | | 14 | 1638 | 6 (0)| 00:00:01 | | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | | 3 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | | --------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO") | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|