|
Skillset 3:
Section 3: Creating a Plug-in Tablespace Using the Transportable Tablespace Feature
create user sst identified by Sst1234;
grant connect,resource to sst;
rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN
convert datafile '/home/oracle/scripts/TRPDATA_6' from platform 'Solaris[tm] OE (64-bit)' format='/home/oracle/files/TRPDATA_6.dbf';
impdp system/oracle dumpfile=dump_dir:trans3_2.dmp transport_datafiles=/u01/app/oracle/oradata/PROD1/TRPDATA_6.dbf
alter tablespace trpdata read write;
- explain plan for
- SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
- FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
- WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
- AND c.cust_state_province = 'FL'
- AND ch.channel_desc = 'Direct Sales'
- AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
- t.calendar_quarter_desc;
复制代码
星型转换之后的执行计划:
建最大的维度表CUSTOMERS上的单行谓词 的位图连接索引(SALES):
- SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE , STATUS, VALIDATED from user_constraints where table_name='CUSTOMERS';
- CONSTRAINT_NAME C STATUS VALIDATED
- ------------------------------ - -------- -------------
- CUSTOMERS_COUNTRY_FK R ENABLED NOT VALIDATED
- SYS_C009620 C ENABLED VALIDATED
- SYS_C009621 C ENABLED VALIDATED
- SYS_C009622 C ENABLED VALIDATED
- SYS_C009623 C ENABLED VALIDATED
- SYS_C009624 C ENABLED VALIDATED
- SYS_C009625 C ENABLED VALIDATED
- SYS_C009626 C ENABLED VALIDATED
- SYS_C009627 C ENABLED VALIDATED
- SYS_C009628 C ENABLED VALIDATED
- SYS_C009629 C ENABLED VALIDATED
- SYS_C009630 C ENABLED VALIDATED
- SYS_C009631 C ENABLED VALIDATED
- SYS_C009632 C ENABLED VALIDATED
- SYS_C009633 C ENABLED VALIDATED
- SYS_C009634 C ENABLED VALIDATED
- CUSTOMERS_PK P ENABLED NOT VALIDATED
- 17 rows selected.
- SQL> create bitmap index sales_cust_state_province on sales ( cust_state_province )
- 2 from sales s , customers c
- 3 where s.cust_id=c.cust_id;
- from sales s , customers c
- *
- ERROR at line 2:
- ORA-25954: missing primary key or unique constraint on dimension
- SQL> alter table CUSTOMERS modify constraint CUSTOMERS_PK validate;
- Table altered.
- SQL> create bitmap index sales_cust_state_province on sales ( cust_state_province )
- 2 from sales s , customers c
- 3 where s.cust_id=c.cust_id;
- from sales s , customers c
- *
- ERROR at line 2:
- ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables
- SQL> create bitmap index sales_cust_state_province on sales ( cust_state_province )
- 2 from sales s , customers c
- 3 where s.cust_id=c.cust_id local ;
- Index created.
- SQL>
复制代码- SQL> explain plan for
- SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
- FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
- WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
- AND c.cust_state_province = 'FL'
- AND ch.channel_desc = 'Direct Sales'
- AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
- t.calendar_quarter_desc; 2 3 4 5 6 7 8
- Explained.
- SQL> @?/rdbms/admin/utlxplp
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Plan hash value: 1154733250
- -----------------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
- -----------------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 351 | 22113 | 503 (1)| 00:00:07 | | |
- | 1 | HASH GROUP BY | | 351 | 22113 | 503 (1)| 00:00:07 | | |
- |* 2 | HASH JOIN | | 351 | 22113 | 502 (1)| 00:00:07 | | |
- |* 3 | TABLE ACCESS FULL | TIMES | 274 | 4384 | 18 (0)| 00:00:01 | | |
- |* 4 | HASH JOIN | | 1869 | 87843 | 483 (1)| 00:00:06 | | |
- |* 5 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
- | 6 | PARTITION RANGE SUBQUERY | | 34469 | 706K| 76 (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
- | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 34469 | 706K| 76 (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|
- | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
- | 9 | BITMAP AND | | | | | | | |
- | 10 | BITMAP MERGE | | | | | | | |
- | 11 | BITMAP KEY ITERATION | | | | | | | |
- | 12 | BUFFER SORT | | | | | | | |
- |* 13 | TABLE ACCESS FULL | CHANNELS | 1 | 13 | 3 (0)| 00:00:01 | | |
- |* 14 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
- | 15 | BITMAP MERGE | | | | | | | |
- | 16 | BITMAP KEY ITERATION | | | | | | | |
- | 17 | BUFFER SORT | | | | | | | |
- |* 18 | TABLE ACCESS FULL | TIMES | 274 | 4384 | 18 (0)| 00:00:01 | | |
- |* 19 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
- |* 20 | BITMAP INDEX SINGLE VALUE | SALES_CUST_STATE_PROVINCE | | | | |KEY(SQ)|KEY(SQ)|
- -----------------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("S"."TIME_ID"="T"."TIME_ID")
- 3 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
- "T"."CALENDAR_QUARTER_DESC"='2000-02')
- 4 - access("S"."CUST_ID"="C"."CUST_ID")
- 5 - filter("C"."CUST_STATE_PROVINCE"='FL')
- 13 - filter("CH"."CHANNEL_DESC"='Direct Sales')
- 14 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
- 18 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
- "T"."CALENDAR_QUARTER_DESC"='2000-02')
- 19 - access("S"."TIME_ID"="T"."TIME_ID")
- 20 - access("S"."SYS_NC00008[ DISCUZ_CODE_2 ]quot;='FL')
- Note
- -----
- - star transformation used for this statement
- 46 rows selected.
复制代码
|
|