课程第36次（2018-08-31星期五）
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.create or replace trigger trg1
after logonon database
declare
pragma AUTONOMOUS_TRANSACTION;
begin
execute immediate 'alter session force parallel dmlparallel 8';
end;
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area535662592 bytes
Fixed Size 1314580 bytes
Variable Size 264241388 bytes
Database Buffers 264241152 bytes
Redo Buffers 5865472 bytes
Database mounted.
Database opened.
SQL> conn hr/hr
Connected.
SQL> explain plan for update employees set salary=salary+1;
Explained.
SQL>@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 545592796
----------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | TQ|IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 107 | 428 | 2 (0)| 00:00:01 | | | |
| 1 |UPDATE | EMPLOYEES | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000| 107 | 428 | 2 (0)| 00:00:01 |Q1,00 | P->S | QC (RAND)|
| 4 | PX BLOCK ITERATOR | | 107 | 428 | 2 (0)| 00:00:01 |Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 428 | 2 (0)| 00:00:01 |Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
12 rows selected.
SQL> alter table employees noparallel ;
Table altered.
SQL> explain plan for update employees set salary=salary+1;
Explained.
SQL>@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 964452392
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 107 | 428 | 3 (0)| 00:00:01 |
| 1 |UPDATE | EMPLOYEES | | | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 428 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
9 rows selected.
SQL> conn / as sysdba
Connected.
SQL> create or replace trigger trg1
after logonon database
declare
pragma AUTONOMOUS_TRANSACTION;
begin
execute immediate 'alter session force parallel dmlparallel 8';
end;2 3 4 5 6 7
8/
Trigger created.
SQL> conn hr/hr
Connected.
SQL>explain plan for update employees set salary=salary+1;
Explained.
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 545592796
----------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | TQ|IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 107 | 428 | 2 (0)| 00:00:01 | | | |
| 1 |UPDATE | EMPLOYEES | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000| 107 | 428 | 2 (0)| 00:00:01 |Q1,00 | P->S | QC (RAND)|
| 4 | PX BLOCK ITERATOR | | 107 | 428 | 2 (0)| 00:00:01 |Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 428 | 2 (0)| 00:00:01 |Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
12 rows selected.
SQL>
SQL> alter system set db_securefile=PERMITTED;
System altered.
SQL> create table oe.CUSTOMER_PROFILES(CUST_IDNUMBER,
2 FIRST_NAMEVARCHAR2(20),
3 LAST_NAMEVARCHAR2(30),
4PROFILE_INFOBLOB)
5lob (profile_info) store as securefile
6(
7 disable storage in row
8 deduplicate
9 compress high
10 tablespace sf_data)
11tablespace sf_data;
Table created.
SQL>
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('NEW_ORDERS','NEW_ORDER_ITEMS')
order by partition_position, table_name;
先用以下语句把NEW_ORDERS表建在SH下面：
select dbms_metadata.get_ddl('TABLE','ORDERS','OE')from dual;
grant selecton hr.employees to sh;
grant references on hr.employees to sh;
CREATE TABLE SH.NEW_ORDERS
( "ORDER_ID" NUMBER(12,0),
"ORDER_DATE" date CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(6,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID" NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) ENABLE,
CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,
CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EXAMPLE"ENABLE,
CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "SH"."CUSTOMERS" ("CUST_ID") ON DELETE SET NULL ENABLE,
CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL ENABLE
)
partition by range (ORDER_DATE)
( partitionpart1values less than(to_date('01-JAN-1999','dd-MON-yyyy')) tablespacepart_tbs1,
partitionpart2values less than (maxvalue)tablespace part_tbs2) ;
create table sh.NEW_ORDER_ITEMS (
ORDER_ID NUMBER(12) NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
QUANTITY NUMBER NOT NULL,
SALES_AMOUNT NUMBER NOT NULL,
constraint fk_NEW_ORDER_ITEMSforeign key(ORDER_ID) references sh.NEW_ORDERs)
partition byreference(fk_NEW_ORDER_ITEMS ) ;
验证：
select table_name, partition_name, high_value
from dba_tab_partitions tp
wheretp.table_owner='SH' andtable_name in ('NEW_ORDERS','NEW_ORDER_ITEMS')
order by partition_position, table_name;
TABLE_NAMEPARTITION_NAMEHIGH_VALUE
1NEW_ORDERSPART1<Long>
2NEW_ORDER_ITEMSPART1<Long>
3NEW_ORDERSPART2<Long>
4NEW_ORDER_ITEMSPART2<Long>
insert into sh.new_ordersselect* from oe.orders;
insert into sh.new_order_itemsselectORDER_ID, PRODUCT_ID,QUANTITY,unit_price*QUANTITY
from oe.order_items;
页:
[1]