Mega Code Archive

AUTOTRACE table joining and aggregate function

SQL>CREATE TABLE project ( 2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 ); Table created. SQL> SQL> SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, 'A',12345); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, 'ERP',23456); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, 'SQL',34567); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, 'CRM',45678); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, 'VPN',56789); 1 row created. SQL> SQL> SQL>CREATE TABLE server_usage ( 2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 ); Table created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1001,101,to_date('4-Apr-2004','dd-mon-yyyy'),1123,222); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1002,102,to_date('4-Apr-2005','dd-mon-yyyy'),1124,223); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1003,103,to_date('4-Apr-2006','dd-mon-yyyy'),1125,224); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1004,104,to_date('4-Apr-2007','dd-mon-yyyy'),1126,225); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1005,105,to_date('4-Apr-2008','dd-mon-yyyy'),1127,226); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1001,106,to_date('4-Apr-2009','dd-mon-yyyy'),1128,227); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1002,107,to_date('4-Apr-2010','dd-mon-yyyy'),1129,228); 1 row created. SQL> SQL>CREATE TABLE emp ( 2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 ); Table created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, 'Mary', to_date('15-Nov-1961','dd-mon-yyyy'),null,169); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, 'Tom', to_date('16-Sep-1964','dd-mon-yyyy'),to_date('5-May-2004','dd-mon-yyyy'),135); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, 'Peter', to_date('29-Dec-1987','dd-mon-yyyy'),to_date('1-Apr-2004','dd-mon-yyyy'),99); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, 'Mike', to_date('15-Jun-2004','dd-mon-yyyy'),null,121); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, 'Less', to_date('2-Jan-2004','dd-mon-yyyy'),null,45); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, 'Park', to_date('1-Mar-1994','dd-mon-yyyy'),to_date('15-Nov-2004','dd-mon-yyyy'),220); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, 'Ink', to_date('4-Apr-2004','dd-mon-yyyy'),to_date('30-Sep-2004','dd-mon-yyyy'),84); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, 'Tike', to_date('23-Aug-1976','dd-mon-yyyy'),null,100); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, 'Inn', to_date('15-Nov-1961','dd-mon-yyyy'),to_date('4-Apr-2004','dd-mon-yyyy'),70); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, 'Kate', to_date('3-Mar-2004','dd-mon-yyyy'),to_date('31-Oct-2004','dd-mon-yyyy'),300); 1 row created. SQL> SQL> SQL>SET ECHO ON SQL>SET AUTOTRACE ON EXPLAIN SQL> SQL>SELECT ename, SUM(hours_logged) 2 FROM emp, server_usage 3 WHERE emp.emp_id = server_usage.emp_id 4 GROUP BY ename; User System Privileges Page 1 SUM(HOURS_LOGGED) -------------------- ----------------- Less 1129 Mary 1123 Mike 1127 Peter 1126 Tom 1124 5 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2561811960 ------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | SERVER_USAGE | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | |* 5 | INDEX UNIQUE SCAN | EMP_PK | ------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID") Note ----- - rule based optimizer used (consider using cbo) SQL> SQL>set autotrace off SQL> SQL>drop table emp; Table dropped. SQL>drop table project; Table dropped. SQL>drop table server_usage; Table dropped.