Bo's Oracle Station

查看: 2848|回复: 0

课程第36次(2018-08-31星期五)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-31 20:07:06 | 显示全部楼层 |阅读模式
Skillset 3
Section 5
5.2                  Ensure that all the statements execute in parallel.                    If a statement cannot execute in parallel, it should not execute at all.
  1. create or replace trigger trg1
  2. after logon  on database
  3. declare
  4.   pragma AUTONOMOUS_TRANSACTION;
  5.   begin
  6.     execute immediate 'alter session force parallel dml  parallel 8';
  7. end;
复制代码
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> startup force
  4. ORACLE instance started.

  5. Total System Global Area  535662592 bytes
  6. Fixed Size                    1314580 bytes
  7. Variable Size                  264241388 bytes
  8. Database Buffers          264241152 bytes
  9. Redo Buffers                    5865472 bytes
  10. Database mounted.
  11. Database opened.
  12. SQL> conn hr/hr
  13. Connected.
  14. SQL> explain plan for update employees set salary=salary+1;

  15. Explained.

  16. SQL>  @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql

  17. PLAN_TABLE_OUTPUT
  18. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  19. Plan hash value: 545592796

  20. ----------------------------------------------------------------------------------------------------------------
  21. | Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time          |    TQ  |IN-OUT| PQ Distrib |
  22. ----------------------------------------------------------------------------------------------------------------
  23. |   0 | UPDATE STATEMENT      |           |   107 |   428 |        2   (0)| 00:00:01 |           |          |               |
  24. |   1 |  UPDATE               | EMPLOYEES |          |          |               |          |           |          |               |
  25. |   2 |   PX COORDINATOR      |           |          |          |               |          |           |          |               |
  26. |   3 |    PX SEND QC (RANDOM)| :TQ10000  |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
  27. |   4 |     PX BLOCK ITERATOR |           |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWC |               |
  28. |   5 |      TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWP |               |

  29. PLAN_TABLE_OUTPUT
  30. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  31. ----------------------------------------------------------------------------------------------------------------

  32. 12 rows selected.

  33. SQL> alter table employees noparallel ;

  34. Table altered.

  35. SQL> explain plan for update employees set salary=salary+1;

  36. Explained.

  37. SQL>  @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql

  38. PLAN_TABLE_OUTPUT
  39. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  40. Plan hash value: 964452392

  41. --------------------------------------------------------------------------------
  42. | Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  43. --------------------------------------------------------------------------------
  44. |   0 | UPDATE STATEMENT   |               |   107 |   428 |     3         (0)| 00:00:01 |
  45. |   1 |  UPDATE            | EMPLOYEES |       |       |            |               |
  46. |   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |     3         (0)| 00:00:01 |
  47. --------------------------------------------------------------------------------

  48. 9 rows selected.

  49. SQL> conn / as sysdba
  50. Connected.
  51. SQL> create or replace trigger trg1
  52. after logon  on database
  53. declare
  54.   pragma AUTONOMOUS_TRANSACTION;
  55.   begin
  56.     execute immediate 'alter session force parallel dml  parallel 8';
  57. end;  2    3    4    5    6    7  
  58.   8  /

  59. Trigger created.

  60. SQL> conn hr/hr
  61. Connected.
  62. SQL>  explain plan for update employees set salary=salary+1;

  63. Explained.

  64. SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql

  65. PLAN_TABLE_OUTPUT
  66. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  67. Plan hash value: 545592796

  68. ----------------------------------------------------------------------------------------------------------------
  69. | Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time          |    TQ  |IN-OUT| PQ Distrib |
  70. ----------------------------------------------------------------------------------------------------------------
  71. |   0 | UPDATE STATEMENT      |           |   107 |   428 |        2   (0)| 00:00:01 |           |          |               |
  72. |   1 |  UPDATE               | EMPLOYEES |          |          |               |          |           |          |               |
  73. |   2 |   PX COORDINATOR      |           |          |          |               |          |           |          |               |
  74. |   3 |    PX SEND QC (RANDOM)| :TQ10000  |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
  75. |   4 |     PX BLOCK ITERATOR |           |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWC |               |
  76. |   5 |      TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWP |               |

  77. PLAN_TABLE_OUTPUT
  78. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  79. ----------------------------------------------------------------------------------------------------------------

  80. 12 rows selected.

  81. SQL>
复制代码
  1. SQL> alter system set db_securefile=PERMITTED;

  2. System altered.

  3. SQL> create table oe.CUSTOMER_PROFILES(CUST_ID  NUMBER,
  4.   2   FIRST_NAME  VARCHAR2(20),
  5.   3    LAST_NAME  VARCHAR2(30),
  6.   4  PROFILE_INFO  BLOB)
  7.   5  lob (profile_info) store as securefile
  8.   6  (
  9.   7   disable storage in row
  10.   8   deduplicate
  11.   9   compress high
  12. 10   tablespace sf_data)
  13. 11  tablespace sf_data;

  14. Table created.

  15. SQL>
复制代码
  1. select table_name, partition_name, high_value
  2. from user_tab_partitions
  3. where table_name in ('NEW_ORDERS','NEW_ORDER_ITEMS')
  4. order by partition_position, table_name;
复制代码
先用以下语句把NEW_ORDERS表建在SH下面:
  1.    select      dbms_metadata.get_ddl('TABLE','ORDERS','OE')  from dual;
复制代码
  1. grant select  on hr.employees to sh;
  2.    
  3.    grant references on hr.employees to sh;
复制代码


  1. CREATE TABLE SH.NEW_ORDERS
  2.    (        "ORDER_ID" NUMBER(12,0),
  3.         "ORDER_DATE" date CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
  4.         "ORDER_MODE" VARCHAR2(8),
  5.         "CUSTOMER_ID" NUMBER(6,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
  6.         "ORDER_STATUS" NUMBER(2,0),
  7.         "ORDER_TOTAL" NUMBER(8,2),
  8.         "SALES_REP_ID" NUMBER(6,0),
  9.         "PROMOTION_ID" NUMBER(6,0),
  10.          CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) ENABLE,
  11.          CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,
  12.          CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  13.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  14.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  15.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  16.   TABLESPACE "EXAMPLE"  ENABLE,
  17.          CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
  18.           REFERENCES "SH"."CUSTOMERS" ("CUST_ID") ON DELETE SET NULL ENABLE,
  19.          CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID")
  20.           REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL ENABLE
  21.    )
  22.    partition by range (ORDER_DATE)
  23.   ( partition  part1  values less than  (to_date('01-JAN-1999','dd-MON-yyyy')) tablespace  part_tbs1,
  24.     partition  part2  values less than (maxvalue)  tablespace part_tbs2) ;
复制代码
  1. create table sh.NEW_ORDER_ITEMS (
  2. ORDER_ID        NUMBER(12) NOT NULL,
  3. PRODUCT_ID      NUMBER NOT NULL,
  4. QUANTITY        NUMBER NOT NULL,
  5. SALES_AMOUNT    NUMBER NOT NULL,
  6. constraint fk_NEW_ORDER_ITEMS  foreign key(ORDER_ID)   references sh.NEW_ORDERs)
  7. partition by  reference  (fk_NEW_ORDER_ITEMS ) ;
复制代码

验证:
  1. select table_name, partition_name, high_value
  2.     from dba_tab_partitions tp
  3.     where  tp.table_owner  ='SH' and  table_name in ('NEW_ORDERS','NEW_ORDER_ITEMS')
  4.     order by partition_position, table_name;
复制代码
   TABLE_NAMEPARTITION_NAMEHIGH_VALUE
1NEW_ORDERSPART1<Long>
2NEW_ORDER_ITEMSPART1<Long>
3NEW_ORDERSPART2<Long>
4NEW_ORDER_ITEMSPART2<Long>

  1.   insert into sh.new_orders  select  * from oe.orders;
复制代码
  1. insert into sh.new_order_items  select  ORDER_ID, PRODUCT_ID,QUANTITY,unit_price*QUANTITY
  2.     from oe.order_items;
复制代码





回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 15:25 , Processed in 0.038335 second(s), 24 queries .

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