Bo's Oracle Station

查看: 2521|回复: 0

课程第80/81/82次(2017-12-08星期五,2017-12-10星期日上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-12-8 21:37:16 | 显示全部楼层 |阅读模式
  1. SELECT cust_street_address
  2. FROM customers
  3.   3  WHERE cust_postal_code = 68054;

  4. 193 rows selected.


  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 2008213504

  8. -------------------------------------------------------------------------------
  9. | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  10. -------------------------------------------------------------------------------
  11. |   0 | SELECT STATEMENT  |              |    89 |  2581 |   405        (1)| 00:00:05 |
  12. |*  1 |  TABLE ACCESS FULL| CUSTOMERS |    89 |  2581 |   405        (1)| 00:00:05 |
  13. -------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  16.    1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=68054)


  17. Statistics
  18. ----------------------------------------------------------
  19.           2  recursive calls
  20.           0  db block gets
  21.        1474  consistent gets
  22.           0  physical reads
  23.           0  redo size
  24.        7393  bytes sent via SQL*Net to client
  25.         651  bytes received via SQL*Net from client
  26.          14  SQL*Net roundtrips to/from client
  27.           0  sorts (memory)
  28.           0  sorts (disk)
  29.         193  rows processed

  30. SQL> SELECT cust_street_address
  31.   2   FROM customers
  32.   3   WHERE cust_postal_code = to_char(68054) ;

  33. 193 rows selected.


  34. Execution Plan
  35. ----------------------------------------------------------
  36. Plan hash value: 1219101926

  37. ----------------------------------------------------------------------------------------------------
  38. | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time           |
  39. ----------------------------------------------------------------------------------------------------
  40. |   0 | SELECT STATEMENT            |                           |        89 |  2581 |        85   (0)| 00:00:02 |
  41. |   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS            |        89 |  2581 |        85   (0)| 00:00:02 |
  42. |*  2 |   INDEX RANGE SCAN            | CUST_POSTAL_CODE_IDX |        89 |           |         1   (0)| 00:00:01 |
  43. ----------------------------------------------------------------------------------------------------

  44. Predicate Information (identified by operation id):
  45. ---------------------------------------------------

  46.    2 - access("CUST_POSTAL_CODE"='68054')


  47. Statistics
  48. ----------------------------------------------------------
  49.           1  recursive calls
  50.           0  db block gets
  51.         191  consistent gets
  52.           1  physical reads
  53.           0  redo size
  54.        7393  bytes sent via SQL*Net to client
  55.         651  bytes received via SQL*Net from client
  56.          14  SQL*Net roundtrips to/from client
  57.           0  sorts (memory)
  58.           0  sorts (disk)
  59.         193  rows processed

  60. 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. select   s.prod_id, a. maxt, s.amount_sold
  10.   from   sales  s , (select    prod_id ,   cust_id , max(time_id)  maxt
  11.                              from sales
  12.                            group by  prod_id , cust_id ) a
  13.   where s.prod_id=a.prod_id and
  14.    s.cust_id =a.cust_id
  15.    and s.prod_id=13
  16.    and s.cust_id=1762;
复制代码
  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 3826430447

  4. ----------------------------------------------------------------------------------------------------------
  5. | Id  | Operation                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time         |
  6. ----------------------------------------------------------------------------------------------------------
  7. |   0 | SELECT STATEMENT            |                                 |  1220 | 43920 |  1151   (0)| 00:00:14 |
  8. |   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS                  |  1220 | 43920 |  1151   (0)| 00:00:14 |
  9. |*  2 |   INDEX SKIP SCAN            | CUST_COUNTRY_STATE_CITY_IX |  1220 |         |    16   (0)| 00:00:01 |
  10. ----------------------------------------------------------------------------------------------------------

  11. Predicate Information (identified by operation id):
  12. ---------------------------------------------------
复制代码
  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. SELECT sum(amount_sold)
  6. FROM sales s
  7. WHERE prod_id BETWEEN 130 AND 150
  8. AND cust_id BETWEEN 10000 AND 10100;

  9. 最好的(带星型):

  10. SELECT sum(s.amount_sold)
  11. FROM sales s , products p
  12. WHERE s.prod_id=p.prod_id and
  13. p.prod_id BETWEEN 130 AND 150
  14. AND s.cust_id BETWEEN 10000 AND 10100;
复制代码
  1. select  * from sales;

  2. SELECT count(*)
  3. FROM sales s, customers c
  4. WHERE s.cust_id = c.cust_id
  5. AND prod_id = 13
  6. AND c.cust_credit_limit > 10000


  7. select  cust_id
  8. from sales s
  9.   where exists (  select 'X'  from customers c
  10.     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. CREATE OR REPLACE VIEW emp_dept
  6. AS
  7. SELECT d.department_id, d.department_name, d.location_id,
  8. e.employee_id, e.last_name, e.first_name, e.salary,
  9. e.job_id FROM departments d ,employees e
  10. WHERE e.department_id (+) = d.department_id;


  11. SELECT v.last_name, v.first_name, l.state_province
  12. FROM locations l, emp_dept v
  13. WHERE l.state_province = 'California'
  14. AND
  15. v.location_id = l.location_id (+);




  16. SELECT v.last_name, v.first_name, l.state_province
  17. FROM locations l, emp_dept v
  18. WHERE l.state_province = 'California'
  19. AND
  20. v.location_id = l.location_id (+);
复制代码
  1. select  * from user_constraints c
  2. where c.TABLE_NAME='PRODUCTS';

  3. alter table products  modify constraint PRODUCTS_PK
  4. validate ;


  5. create bitmap index isales on sales (p.prod_subcategory
  6. )
  7. from  products p , sales s
  8. 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;
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 12:49 , Processed in 0.045112 second(s), 24 queries .

快速回复 返回顶部 返回列表