《Oracle英文版培训课件之Data Warehouse：les03》由会员分享，可在线阅读，更多相关《Oracle英文版培训课件之Data Warehouse：les03（30页珍藏版）》请在金锄头文库上搜索。
1、Partitioning BasicsObjectivesAfter completing this lesson, you should be able to do the following:Outline the Oracle Database partitioning architectureDescribe the supported partition List options for creating a partitioned tableCreate partitioned tablesPartitioned Tables and IndexesLarge tables and
2、 indexes can be partitioned into smaller, more manageable pieces.Table T1Index I1Table T1Index I1Manageability: Rolling Window OperationsOCT00JUL01Benefits of Partitioning: Performance ConsiderationsThe optimizer eliminates (prunes) partitions that do not need to be scanned.Partitions can be scanned
3、, updated, inserted, or deleted in parallel.Join operations can be optimized to join “by the partition.”Partitions can be load-balanced across physical devices.Partition pruning: Only the relevant partitions are accessed.Sales01-May01-Apr01-Feb01-Jan01-Mar01-JunSQL SELECT SUM(amount_sold) 2 FROM sal
4、es 3 WHERE time_id BETWEEN 4 TO_DATE(01-MAR-2000, 5 DD-MON-YYYY) AND 6 TO_DATE(31-MAY-2000, 7 DD-MON-YYYY);Performance Consideration:Partition PruningTable Versus Index PartitioningA nonpartitioned table can have partitioned or nonpartitioned indexes.A partitioned table can have partitioned or nonpa
5、rtitioned indexes.Table T1Index I1Index I2Table T2Index I3Index I4Partitioning MethodsRange partitioning maps data to partitions on the basis of ranges of partition key values for each partition.Hash partitioning maps data to partitions by using a hashing algorithm applied to a partitioning key. Lis
6、t partitioning maps rows to partitions by using a list of discrete values for the partitioning column. Composite partitioning:Range-Hash subpartitions the range partitions using a hashing algorithm.Range-List subpartitions the range partitions using an explicit list.SQL CREATE TABLE example 2 ( idx
7、NUMBER, txt VARCHAR2(20) ) 3 PARTITION BY RANGE ( idx ) 4 ( PARTITION VALUES LESS THAN ( 0 ) 5 TABLESPACE data01 6 , PARTITION VALUES LESS THAN ( MAXVALUE );CREATE TABLE Statement with PartitioningA partitioned table declaration contains three elements:The logical structure of the tableThe partition
8、 structure defining the type and columnsThe structure of each table partitionLogical and Physical AttributesLogical attributes:Normal table structure (columns, constraints)Partition typeKeys and valuesRow movementPhysical attributes:TablespaceExtent sizes, block attributesPartitioning TypeThe partit
9、ion type is declared in the PARTITION BY clause:SQL CREATE TABLE ( column ) 2 PARTITION BY RANGE ( column_list ) 3 ( PARTITION specifications ) ; 2 PARTITION BY HASH ( column_list ) 2 PARTITION BY LIST ( column ) 2 PARTITION BY RANGE ( column_list ) SUBPARTITION BY HASH ( column_list2 ) 2 PARTITION
10、BY RANGE ( column_list ) SUBPARTITION BY LIST ( column )Specifying Partition AttributesEach partition is specified in a partition value clause.There can be up to 65,535 partitions per table. . PARTITION simple_p1 VALUES ( HIGH, MED ) TABLESPACE data01 PCTFREE 5 , PARTITION simple_p2 VALUES ( LOW ) T
11、ABLESPACE data02 STORAGE ( INITIAL 1M ) .Partition Key ValueThe partition key value must be a literal.Constant expressions are not allowed, with the exception of TO_DATE conversion.The partition key can consist of up to 16 columns.Range PartitioningSpecify the columns to be partitioned, and the brea
12、k values for each partition.Each partition must be defined.The MAXVALUE value allows the greatest possible value and fits all data types.The MAXVALUE value includes NULL values.Range Partitioning: ExampleCREATE TABLE salestable(s_productid NUMBER,s_saledate DATE,s_custid NUMBER,s_totalprice NUMBER)P
13、ARTITION BY RANGE(s_saledate)(PARTITION sal99q4 VALUES LESS THAN (TO_DATE(01-JAN-2000, DD-MON-YYYY),PARTITION sal00q1 VALUES LESS THAN (TO_DATE(01-APR-2000, DD-MON-YYYY),PARTITION sal00q2 VALUES LESS THAN (TO_DATE(01-JUL-2000, DD-MON-YYYY),PARTITION sal00q3 VALUES LESS THAN (TO_DATE(01-OCT-2000, DD-
14、MON-YYYY),PARTITION sal00q4 VALUES LESS THAN (TO_DATE(01-JAN-2001, DD-MON-YYYY);List PartitioningCREATE TABLE sales_list(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_state VARCHAR2(20),sales_amount NUMBER(10),sales_date DATE)PARTITION BY LIST(sales_state)(PARTITION sales_west VALUES(Califo
15、rnia, Hawaii),PARTITION sales_east VALUES (New York, Virginia, Florida),PARTITION sales_central VALUES(Texas, Illinois),PARTITION sales_other VALUES(DEFAULT);Hash Partitioning,Named PartitionsSpecify the columns to be partitioned, and the number of partitions:Partition may be defined, or just quanti
16、fied.NULL is placed in the first partition.The number of partitions should be a power of two.CREATE TABLE simple (idx NUMBER, txt VARCHAR2(20) PRIMARY KEY) ORGANIZATION INDEX PARTITION BY HASH ( txt ) ( PARTITION s_h1 tablespace data01 , PARTITION s_h2 tablespace data03 ) ;Hash-Range Partitioning Ha
17、sh-Range partitioning example:SQL CREATE TABLE simple 2 ( idx NUMBER, txt VARCHAR2(20) ) 3 PARTITION BY RANGE ( idx ) 4 SUBPARTITION BY HASH ( txt ) 5 SUBPARTITIONS 4 STORE IN (data01, data02) 6 ( PARTITION ns_lo VALUES LESS THAN ( 0 ) 7 , PARTITION ns_hi VALUES LESS THAN ( 1E99 ) 8 , PARTITION ns_m
18、x 9 VALUES LESS THAN ( MAXVALUE ) 10 SUBPARTITIONS 2 STORE IN ( data03 ) ) ;Range-List PartitioningFunctionality supported for range-hash partitioning is extended to range-list partitioning.Well suited for:Historical data at the partition levelControlled data distribution at subpartition levelList (
19、state)Range (year)p1_s1 2000 2001 2002 SELECT idx 2 FROM simple PARTITION ( s_neg ) ;SQL DELETE FROM simple SUBPARTITION ( s_h2 ) ;SQL CREATE TABLE sim2 2 AS SELECT * FROM simple PARTITION ( p3 ) ;Create Partitions with Enterprise ManagerEquipartitioningIf two tables have the same partition keys and
20、 partition key values, then they are equipartitioned.This is useful for tables with a common key, like master-detail relationships.A partitionwise join operation requires equipartitioning.Indexes can be equipartitioned with the table.Full Partitionwise JoinsA full partitionwise join divides a large
21、join into smaller joins.The tables being joined must be equipartitioned on their join keys.When a full partitionwise join is executed in parallel, the granule of parallelism is a partition.SELECT c.cust_last_name, COUNT(*)FROM sales s, customers cWHERE s.cust_id = c.cust_id AND s.time_id BETWEEN TO_
22、DATE(01-JUL-1999, DD-MON-YYYY) AND(TO_DATE(01-OCT-1999, DD-MON-YYYY)GROUP BY c.cust_last_name HAVING COUNT(*) 100;Partial Partitionwise JoinsPartial partitionwise joins require you to partition only one table on the join key.The partitioned table is called the reference table.The other table is dyna
23、mically repartitioned based on the partitioning of the reference table.Partial partitionwise joins can be done only in parallel.SummaryIn this lesson, you should have learned how to:Outline the Oracle Database partitioning architectureDescribe the supported partition List options for creating a partitioned tableCreate partitioned tablesPractice 3: OverviewThis practice covers the following topics:Analyzing execution plans involving partition pruningEmploying subpartition templates in range-list partitioned table creation
