课程第35次（2018-08-27星期一）

botang · 发表于 2018-8-27 19:54:41

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.

复制代码