课程第80/81/82次（2017-12-08星期五，2017-12-10星期日上下午）

botang

1. SELECT cust_street_address
   
2. FROM customers
   
3.   3  WHERE cust_postal_code = 68054;
   
4. 
   
5. 193 rows selected.
   
6. 
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. Plan hash value: 2008213504
    
11. 
    
12. -------------------------------------------------------------------------------
    
13. | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    
14. -------------------------------------------------------------------------------
    
15. |   0 | SELECT STATEMENT  |              |    89 |  2581 |   405        (1)| 00:00:05 |
    
16. |*  1 |  TABLE ACCESS FULL| CUSTOMERS |    89 |  2581 |   405        (1)| 00:00:05 |
    
17. -------------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22.    1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=68054)
    
23. 
    
24. 
    
25. Statistics
    
26. ----------------------------------------------------------
    
27.           2  recursive calls
    
28.           0  db block gets
    
29.        1474  consistent gets
    
30.           0  physical reads
    
31.           0  redo size
    
32.        7393  bytes sent via SQL*Net to client
    
33.         651  bytes received via SQL*Net from client
    
34.          14  SQL*Net roundtrips to/from client
    
35.           0  sorts (memory)
    
36.           0  sorts (disk)
    
37.         193  rows processed
    
38. 
    
39. SQL> SELECT cust_street_address
    
40.   2   FROM customers
    
41.   3   WHERE cust_postal_code = to_char(68054) ;
    
42. 
    
43. 193 rows selected.
    
44. 
    
45. 
    
46. Execution Plan
    
47. ----------------------------------------------------------
    
48. Plan hash value: 1219101926
    
49. 
    
50. ----------------------------------------------------------------------------------------------------
    
51. | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time           |
    
52. ----------------------------------------------------------------------------------------------------
    
53. |   0 | SELECT STATEMENT            |                           |        89 |  2581 |        85   (0)| 00:00:02 |
    
54. |   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS            |        89 |  2581 |        85   (0)| 00:00:02 |
    
55. |*  2 |   INDEX RANGE SCAN            | CUST_POSTAL_CODE_IDX |        89 |           |         1   (0)| 00:00:01 |
    
56. ----------------------------------------------------------------------------------------------------
    
57. 
    
58. Predicate Information (identified by operation id):
    
59. ---------------------------------------------------
    
60. 
    
61.    2 - access("CUST_POSTAL_CODE"='68054')
    
62. 
    
63. 
    
64. Statistics
    
65. ----------------------------------------------------------
    
66.           1  recursive calls
    
67.           0  db block gets
    
68.         191  consistent gets
    
69.           1  physical reads
    
70.           0  redo size
    
71.        7393  bytes sent via SQL*Net to client
    
72.         651  bytes received via SQL*Net from client
    
73.          14  SQL*Net roundtrips to/from client
    
74.           0  sorts (memory)
    
75.           0  sorts (disk)
    
76.         193  rows processed
    
77. 
    
78. SQL>
    

复制代码

1. select  *
   
2. from sales s1
   
3. where time_id = (select max(time_id)
   
4. from sales s2
   
5. where s1.prod_id = s2.prod_id
   
6. and s1.cust_id = s2.cust_id
   
7. and prod_id = 13
   
8. and cust_id = 1762);
   
9. 
   
10. 
    
11. select   s.prod_id, a. maxt, s.amount_sold
    
12.   from   sales  s , (select    prod_id ,   cust_id , max(time_id)  maxt
    
13.                              from sales
    
14.                            group by  prod_id , cust_id ) a
    
15.   where s.prod_id=a.prod_id and
    
16.    s.cust_id =a.cust_id
    
17.    and s.prod_id=13
    
18.    and s.cust_id=1762;

复制代码

1. Execution Plan
   
2. ----------------------------------------------------------
   
3. Plan hash value: 3826430447
   
4. 
   
5. ----------------------------------------------------------------------------------------------------------
   
6. | Id  | Operation                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time         |
   
7. ----------------------------------------------------------------------------------------------------------
   
8. |   0 | SELECT STATEMENT            |                                 |  1220 | 43920 |  1151   (0)| 00:00:14 |
   
9. |   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS                  |  1220 | 43920 |  1151   (0)| 00:00:14 |
   
10. |*  2 |   INDEX SKIP SCAN            | CUST_COUNTRY_STATE_CITY_IX |  1220 |         |    16   (0)| 00:00:01 |
    
11. ----------------------------------------------------------------------------------------------------------
    
12. 
    
13. Predicate Information (identified by operation id):
    
14. ---------------------------------------------------
    

复制代码

1. SELECT /*+ index(s sales_pk) */ sum(amount_sold)
   
2. FROM sales s
   
3. WHERE prod_id BETWEEN 130 AND 150
   
4. AND cust_id BETWEEN 10000 AND 10100;
   
5. 
   
6. 
   
7. 
   
8. SELECT sum(amount_sold)
   
9. FROM sales s
   
10. WHERE prod_id BETWEEN 130 AND 150
    
11. AND cust_id BETWEEN 10000 AND 10100;
    
12. 
    
13. 最好的（带星型）：
    
14. 
    
15. SELECT sum(s.amount_sold)
    
16. FROM sales s , products p
    
17. WHERE s.prod_id=p.prod_id and
    
18. p.prod_id BETWEEN 130 AND 150
    
19. AND s.cust_id BETWEEN 10000 AND 10100;

复制代码

1. select  * from sales;
   
2. 
   
3. SELECT count(*)
   
4. FROM sales s, customers c
   
5. WHERE s.cust_id = c.cust_id
   
6. AND prod_id = 13
   
7. AND c.cust_credit_limit > 10000
   
8. 
   
9. 
   
10. select  cust_id
    
11. from sales s
    
12.   where exists (  select 'X'  from customers c
    
13.     where  s.cust_id=c.cust_id  and s.prod_id=13 and c.cust_credit_limit > 10000     );

复制代码

1. SELECT d.department_id, d.department_name, d.location_id,
   
2. e.employee_id, e.last_name, e.first_name, e.salary,
   
3. e.job_id FROM departments d ,employees e
   
4. WHERE e.department_id (+)  = d.department_id ;
   
5. 
   
6. 
   
7. CREATE OR REPLACE VIEW emp_dept
   
8. AS
   
9. SELECT d.department_id, d.department_name, d.location_id,
   
10. e.employee_id, e.last_name, e.first_name, e.salary,
    
11. e.job_id FROM departments d ,employees e
    
12. WHERE e.department_id (+) = d.department_id;
    
13. 
    
14. 
    
15. SELECT v.last_name, v.first_name, l.state_province
    
16. FROM locations l, emp_dept v
    
17. WHERE l.state_province = 'California'
    
18. AND
    
19. v.location_id = l.location_id (+);
    
20. 
    
21. 
    
22. 
    
23. 
    
24. SELECT v.last_name, v.first_name, l.state_province
    
25. FROM locations l, emp_dept v
    
26. WHERE l.state_province = 'California'
    
27. AND
    
28. v.location_id = l.location_id (+);
    

复制代码

1. select  * from user_constraints c
   
2. where c.TABLE_NAME='PRODUCTS';
   
3. 
   
4. alter table products  modify constraint PRODUCTS_PK
   
5. validate ;
   
6. 
   
7. 
   
8. create bitmap index isales on sales (p.prod_subcategory
   
9. )
   
10. from  products p , sales s
    
11. where p.prod_id=s.prod_id  local ;
    

复制代码

内存表是物化视图还要快的数据结构：

1. SET SERVEROUTPUT ON
   
2. DECLARE
   
3. TYPE emp_table_type is table of
   
4. employees%ROWTYPE INDEX BY BINARY_INTEGER;
   
5. my_emp_table emp_table_type;
   
6. v_count
   
7. NUMBER(3):= 104;
   
8. BEGIN
   
9. FOR i IN 100..v_count
   
10. LOOP
    
11. SELECT * INTO my_emp_table(i) FROM employees
    
12. WHERE employee_id = i;
    
13. END LOOP;
    
14. FOR i IN my_emp_table.FIRST..my_emp_table.LAST
    
15. LOOP
    
16. DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
    
17. END LOOP;
    
18. END;
    

复制代码

从原始结构了解cursor：

1. declare
   
2. type type_departments  is table of departments%rowtype index by binary_integer;
   
3. v_departments type_departments ;
   
4. type type_1 is ref cursor;
   
5. v_1 type_1;
   
6. v_2 varchar2(200) :='select   department_id, department_name, manager_id,location_id
   
7.                  from departments';
   
8. i integer :=1;
   
9. begin
   
10.   
    
11.   dbms_output.put_line(lpad('DEPARTMENT_ID',13,' ')||' '
    
12.                        ||rpad('DEPARTMENT_NAME',30,' ')||' '
    
13.                        ||lpad('MANAGER_ID',10,' ')||' '||
    
14.                        lpad('LOCATION_ID',11,' '));
    
15.   dbms_output.put_line(lpad('-',13,'-')||' '
    
16.                        ||rpad('-',30,'-')||' '
    
17.                        ||lpad('-',10,'-')||' '||
    
18.                        lpad('-',11,'-'));
    
19.   open v_1 for v_2;
    
20.   loop
    
21.     fetch v_1 into v_departments(i);
    
22.     exit when v_1%notfound;
    
23.     dbms_output.put_line(lpad(to_char(v_departments(i).DEPARTMENT_ID),13,' ')||' '
    
24.                        ||rpad(v_departments(i).DEPARTMENT_NAME,30,' ')||' '
    
25.                        ||lpad(nvl(to_char(v_departments(i).MANAGER_ID),' '),10,' ')||' '||
    
26.                        lpad(to_char(v_departments(i).LOCATION_ID),11,' '));
    
27.       i := i +1;
    
28.    end loop;
    
29.    close v_1;
    
30. end;

复制代码