SQL> @create_employees.sql
SQL>
SQL> set echo off
SQL> drop table employees
2 /
drop table employees
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
SQL> drop table product_returns
2 /
drop table product_returns
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
SQL> -- create a table with a virtual column
SQL> create table employees
2 ( employee_id number(6) not null
3 , first_name varchar2(20)
4 , last_name varchar2(25)
5 , email varchar2(25)
6 , phone_number varchar2(20)
7 , hiredate date
8 , job_id varchar2(10)
9 , salary number(8,2)
10 , commission_pct number(2,2)
11 , manager_id number(6)
12 , department_id number(4)
13 , well_off as (case sign(1000 - (salary + nvl(commission_pct,0)))
14 when 1 then 'poor'
15 else case sign(3000 - (salary + nvl(commission_pct,0)))
16 when 1 then 'not doing too bad'
17 when 0 then 'on target'
18 else 'filthy rich'
19 end
20 end
21 ) virtual
22 )
23 /
테이블이 생성되었습니다.
SQL> @insert_employees.sql
SQL>
SQL> set echo off
SQL> -- insert data into the table
SQL> insert into employees
2 (employee_id, first_name, last_name, email, phone_number, hiredate, job_id, salary, commission_pct,
3 manager_id, department_id)
4 select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary,
5 commission_pct, manager_id, department_id from hr.employees
6 /
107 개의 행이 만들어졌습니다.
SQL> commit
2 /
커밋이 완료되었습니다.
SQL> @Select_employees.sql
SQL>
SQL> set echo off
SQL> -- select from the virtual column
SQL> select first_name ||' '|| last_name name, well_off, salary + nvl(commission_pct,0) total_salary
2 from employees
3 /
NAME WELL_OFF TOTAL_SALARY
--------------------------------------------- ----------------- ------------
Donald OConnell not doing too bad 2600
Douglas Grant not doing too bad 2600
Jennifer Whalen filthy rich 4400
Michael Hartstein filthy rich 13000
Pat Fay filthy rich 6000
Susan Mavris filthy rich 6500
Hermann Baer filthy rich 10000
Shelley Higgins filthy rich 12008
William Gietz filthy rich 8300
Steven King filthy rich 24000
Neena Kochhar filthy rich 17000
Lex De Haan filthy rich 17000
Alexander Hunold filthy rich 9000
Bruce Ernst filthy rich 6000
David Austin filthy rich 4800
Valli Pataballa filthy rich 4800
Diana Lorentz filthy rich 4200
Nancy Greenberg filthy rich 12008
Daniel Faviet filthy rich 9000
John Chen filthy rich 8200
Ismael Sciarra filthy rich 7700
Jose Manuel Urman filthy rich 7800
Luis Popp filthy rich 6900
Den Raphaely filthy rich 11000
Alexander Khoo filthy rich 3100
Shelli Baida not doing too bad 2900
Sigal Tobias not doing too bad 2800
Guy Himuro not doing too bad 2600
Karen Colmenares not doing too bad 2500
Matthew Weiss filthy rich 8000
Adam Fripp filthy rich 8200
Payam Kaufling filthy rich 7900
Shanta Vollman filthy rich 6500
Kevin Mourgos filthy rich 5800
Julia Nayer filthy rich 3200
Irene Mikkilineni not doing too bad 2700
James Landry not doing too bad 2400
Steven Markle not doing too bad 2200
Laura Bissot filthy rich 3300
Mozhe Atkinson not doing too bad 2800
James Marlow not doing too bad 2500
TJ Olson not doing too bad 2100
Jason Mallin filthy rich 3300
Michael Rogers not doing too bad 2900
Ki Gee not doing too bad 2400
Hazel Philtanker not doing too bad 2200
Renske Ladwig filthy rich 3600
Stephen Stiles filthy rich 3200
John Seo not doing too bad 2700
Joshua Patel not doing too bad 2500
Trenna Rajs filthy rich 3500
Curtis Davies filthy rich 3100
Randall Matos not doing too bad 2600
Peter Vargas not doing too bad 2500
John Russell filthy rich 14000.4
Karen Partners filthy rich 13500.3
Alberto Errazuriz filthy rich 12000.3
Gerald Cambrault filthy rich 11000.3
Eleni Zlotkey filthy rich 10500.2
Peter Tucker filthy rich 10000.3
David Bernstein filthy rich 9500.25
Peter Hall filthy rich 9000.25
Christopher Olsen filthy rich 8000.2
Nanette Cambrault filthy rich 7500.2
Oliver Tuvault filthy rich 7000.15
Janette King filthy rich 10000.35
Patrick Sully filthy rich 9500.35
Allan McEwen filthy rich 9000.35
Lindsey Smith filthy rich 8000.3
Louise Doran filthy rich 7500.3
Sarath Sewall filthy rich 7000.25
Clara Vishney filthy rich 10500.25
Danielle Greene filthy rich 9500.15
Mattea Marvins filthy rich 7200.1
David Lee filthy rich 6800.1
Sundar Ande filthy rich 6400.1
Amit Banda filthy rich 6200.1
Lisa Ozer filthy rich 11500.25
Harrison Bloom filthy rich 10000.2
Tayler Fox filthy rich 9600.2
William Smith filthy rich 7400.15
Elizabeth Bates filthy rich 7300.15
Sundita Kumar filthy rich 6100.1
Ellen Abel filthy rich 11000.3
Alyssa Hutton filthy rich 8800.25
Jonathon Taylor filthy rich 8600.2
Jack Livingston filthy rich 8400.2
Kimberely Grant filthy rich 7000.15
Charles Johnson filthy rich 6200.1
Winston Taylor filthy rich 3200
Jean Fleaur filthy rich 3100
Martha Sullivan not doing too bad 2500
Girard Geoni not doing too bad 2800
Nandita Sarchand filthy rich 4200
Alexis Bull filthy rich 4100
Julia Dellinger filthy rich 3400
Anthony Cabrio on target 3000
Kelly Chung filthy rich 3800
Jennifer Dilly filthy rich 3600
Timothy Gates not doing too bad 2900
Randall Perkins not doing too bad 2500
Sarah Bell filthy rich 4000
Britney Everett filthy rich 3900
Samuel McCain filthy rich 3200
Vance Jones not doing too bad 2800
Alana Walsh filthy rich 3100
Kevin Feeney on target 3000
107 개의 행이 선택되었습니다.
SQL> @create_prod_ret.sql
SQL> set echo off
SQL>
SQL> -- now, create a table product returns with a virtual column, and partition by this column
SQL> create table product_returns
2 ( order_id number not null
3 , product_id number not null
4 , customer_id number not null
5 , quantity number not null
6 , unit_price number not null
7 , total_value as (quantity * unit_price) virtual
8 )
9 partition by range(total_value)
10 ( partition dont_even_bother values less than (100)
11 , partition perhaps_the_accountant_cares values less than (1000)
12 , partition now_it_becomes_worrying values less than (10000)
13 , partition oops_somebody_be_fired values less than (100000)
14 , partition this_means_bankruptcy values less than (maxvalue)
15 )
16 /
테이블이 생성되었습니다.
SQL> @insert_prod_ret.sql
SQL>
SQL> set echo off
SQL> -- insert some data into the table
SQL> set feedback off
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 36,109, 10, 120) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (2, 46,129, 20, 10) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (3, 73,159, 15, 1) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (4,136,1109, 16, 2) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (5, 27,309, 20, 420) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (6, 96,129, 10, 1020) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (7, 39,104, 1, 12000) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (8,436,6109, 8, 200) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (9,789,709, 2, 2400) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (11,276,809,9, 10000) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (12, 34,199, 10, 20000) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (15,24,100,5, 10) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (17,76,1109, 1, 20) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (19, 55,209, 1, 300000) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (21, 76,139, 1, 10) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (31, 90, 104, 2, 4020) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (51,284,5109, 9, 60) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,912,169, 1, 10980) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (31,536,179, 2, 60) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (13,752,108, 1, 400) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (12,986,1109, 3, 700) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 39,129, 4, 6) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 32,103, 1, 30) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (12, 34,104, 1, 98710) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,173,6109, 1, 9650) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (17,853,7109, 1, 60) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (19,981,809, 5, 80) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 84,103, 16, 1000) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,21,2109, 1000, 90) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (13,782,2359, 102, 1850) ;
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,941,5309, 10, 40) ;
SQL> commit ;
SQL>
SQL> /
SQL> @insert_prod_ret.sql
SQL>
SQL> set echo off
SQL> -- insert some data into the table
SQL> set feedback off
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 36,109, 10, 120) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (2, 46,129, 20, 10) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (3, 73,159, 15, 1) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (4,136,1109, 16, 2) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (5, 27,309, 20, 420) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (6, 96,129, 10, 1020) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (7, 39,104, 1, 12000) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (8,436,6109, 8, 200) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (9,789,709, 2, 2400) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (11,276,809,9, 10000) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (12, 34,199, 10, 20000) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (15,24,100,5, 10) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (17,76,1109, 1, 20) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (19, 55,209, 1, 300000) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (21, 76,139, 1, 10) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (31, 90, 104, 2, 4020) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (51,284,5109, 9, 60) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,912,169, 1, 10980) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (31,536,179, 2, 60) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (13,752,108, 1, 400) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (12,986,1109, 3, 700) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 39,129, 4, 6) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 32,103, 1, 30) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (12, 34,104, 1, 98710) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,173,6109, 1, 9650) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (17,853,7109, 1, 60) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (19,981,809, 5, 80) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1, 84,103, 16, 1000) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,21,2109, 1000, 90) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (13,782,2359, 102, 1850) ;
SQL>
SQL> insert into product_returns (order_id, product_id, customer_id, quantity, unit_price) values (1,941,5309, 10, 40) ;
SQL>
SQL> commit ;
SQL> @Select_prod_return.sql
SQL>
SQL> set echo off
SQL> -- select from the different partitions
SQL> select order_id, quantity, unit_price, total_value
2 from product_returns partition (dont_even_bother)
3 order by total_value
4 /
ORDER_ID QUANTITY UNIT_PRICE TOTAL_VALUE
---------- ---------- ---------- -----------
21 1 10 10
21 1 10 10
21 1 10 10
21 1 10 10
21 1 10 10
3 15 1 15
3 15 1 15
3 15 1 15
3 15 1 15
3 15 1 15
17 1 20 20
17 1 20 20
17 1 20 20
17 1 20 20
17 1 20 20
1 4 6 24
1 4 6 24
1 4 6 24
1 4 6 24
1 4 6 24
1 1 30 30
1 1 30 30
1 1 30 30
1 1 30 30
1 1 30 30
4 16 2 32
4 16 2 32
4 16 2 32
4 16 2 32
4 16 2 32
15 5 10 50
15 5 10 50
15 5 10 50
15 5 10 50
15 5 10 50
17 1 60 60
17 1 60 60
17 1 60 60
17 1 60 60
17 1 60 60
40 개의 행이 선택되었습니다.
SQL> select order_id, quantity, unit_price, total_value
2 from product_returns partition (perhaps_the_accountant_cares)
3 order by total_value
4 /
ORDER_ID QUANTITY UNIT_PRICE TOTAL_VALUE
---------- ---------- ---------- -----------
31 2 60 120
31 2 60 120
31 2 60 120
31 2 60 120
31 2 60 120
2 20 10 200
2 20 10 200
2 20 10 200
2 20 10 200
2 20 10 200
1 10 40 400
19 5 80 400
13 1 400 400
1 10 40 400
19 5 80 400
13 1 400 400
1 10 40 400
19 5 80 400
13 1 400 400
1 10 40 400
1 10 40 400
19 5 80 400
19 5 80 400
13 1 400 400
13 1 400 400
51 9 60 540
51 9 60 540
51 9 60 540
51 9 60 540
51 9 60 540
30 개의 행이 선택되었습니다.
SQL> select order_id, quantity, unit_price, total_value
2 from product_returns partition (now_it_becomes_worrying)
3 order by total_value
4 /
ORDER_ID QUANTITY UNIT_PRICE TOTAL_VALUE
---------- ---------- ---------- -----------
1 10 120 1200
1 10 120 1200
1 10 120 1200
1 10 120 1200
1 10 120 1200
8 8 200 1600
8 8 200 1600
8 8 200 1600
8 8 200 1600
8 8 200 1600
12 3 700 2100
12 3 700 2100
12 3 700 2100
12 3 700 2100
12 3 700 2100
9 2 2400 4800
9 2 2400 4800
9 2 2400 4800
9 2 2400 4800
9 2 2400 4800
31 2 4020 8040
31 2 4020 8040
31 2 4020 8040
31 2 4020 8040
31 2 4020 8040
5 20 420 8400
5 20 420 8400
5 20 420 8400
5 20 420 8400
5 20 420 8400
1 1 9650 9650
1 1 9650 9650
1 1 9650 9650
1 1 9650 9650
1 1 9650 9650
35 개의 행이 선택되었습니다.
SQL> select order_id, quantity, unit_price, total_value
2 from product_returns partition (oops_somebody_be_fired)
3 order by total_value
4 /
ORDER_ID QUANTITY UNIT_PRICE TOTAL_VALUE
---------- ---------- ---------- -----------
6 10 1020 10200
6 10 1020 10200
6 10 1020 10200
6 10 1020 10200
6 10 1020 10200
1 1 10980 10980
1 1 10980 10980
1 1 10980 10980
1 1 10980 10980
1 1 10980 10980
7 1 12000 12000
7 1 12000 12000
7 1 12000 12000
7 1 12000 12000
7 1 12000 12000
1 16 1000 16000
1 16 1000 16000
1 16 1000 16000
1 16 1000 16000
1 16 1000 16000
1 1000 90 90000
1 1000 90 90000
11 9 10000 90000
1 1000 90 90000
11 9 10000 90000
1 1000 90 90000
11 9 10000 90000
1 1000 90 90000
11 9 10000 90000
11 9 10000 90000
12 1 98710 98710
12 1 98710 98710
12 1 98710 98710
12 1 98710 98710
12 1 98710 98710
35 개의 행이 선택되었습니다.
SQL> select order_id, quantity, unit_price, total_value
2 from product_returns partition (this_means_bankruptcy)
3 order by total_value
4 /
ORDER_ID QUANTITY UNIT_PRICE TOTAL_VALUE
---------- ---------- ---------- -----------
13 102 1850 188700
13 102 1850 188700
13 102 1850 188700
13 102 1850 188700
13 102 1850 188700
12 10 20000 200000
12 10 20000 200000
12 10 20000 200000
12 10 20000 200000
12 10 20000 200000
19 1 300000 300000
19 1 300000 300000
19 1 300000 300000
19 1 300000 300000
19 1 300000 300000
15 개의 행이 선택되었습니다.
SQL> explain plan for select * from product_returns where quantity * unit_price = 60000;
해석되었습니다.
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 2847847071
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 156 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 156 | 3 (0)| 00:00:01 | 4 | 4 |
|* 2 | TABLE ACCESS FULL | PRODUCT_RETURNS | 2 | 156 | 3 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PRODUCT_RETURNS"."TOTAL_VALUE"=60000)
Note
-----
- dynamic sampling used for this statement (level=2)
18 개의 행이 선택되었습니다.