课程第35次（2018-08-27星期一）
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 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> selectCONSTRAINT_NAME, CONSTRAINT_TYPE , STATUS, VALIDATEDfrom user_constraints where table_name='CUSTOMERS';
CONSTRAINT_NAME C STATUS VALIDATED
------------------------------ - -------- -------------
CUSTOMERS_COUNTRY_FK R ENABLEDNOT VALIDATED
SYS_C009620 C ENABLEDVALIDATED
SYS_C009621 C ENABLEDVALIDATED
SYS_C009622 C ENABLEDVALIDATED
SYS_C009623 C ENABLEDVALIDATED
SYS_C009624 C ENABLEDVALIDATED
SYS_C009625 C ENABLEDVALIDATED
SYS_C009626 C ENABLEDVALIDATED
SYS_C009627 C ENABLEDVALIDATED
SYS_C009628 C ENABLEDVALIDATED
SYS_C009629 C ENABLEDVALIDATED
SYS_C009630 C ENABLEDVALIDATED
SYS_C009631 C ENABLEDVALIDATED
SYS_C009632 C ENABLEDVALIDATED
SYS_C009633 C ENABLEDVALIDATED
SYS_C009634 C ENABLEDVALIDATED
CUSTOMERS_PK P ENABLEDNOT VALIDATED
17 rows selected.
SQL> create bitmap index sales_cust_state_province on sales ( cust_state_province )
2from sales s , customers c
3where 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 CUSTOMERSmodify constraint CUSTOMERS_PKvalidate;
Table altered.
SQL> create bitmap index sales_cust_state_province on sales ( cust_state_province )
2from sales s , customers c
3where 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 )
2from sales s , customers c
3where s.cust_id=c.cust_idlocal ;
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.
页:
[1]