创建查询分区表和索引

查看分区表的信息

select TABLE_NAME,PARTITION_NAME,PARTITIONING_TYPE,SUBPARTITION_COUNT,PARTITION_COUNT,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS,BLOCKS,AVG_SPACE,CHAIN_CNT,LAST_ANALYZED,STATUS,,COMPOSITE,DEF_TABLESPACE_NAME FROM ALL_PART_TABLES A,ALL_TAB_PARTITIONS  B WHERE  A.TABLE_NAME=B.TABE_NAME AND A.TABLE_NAME='SALES_HISTORY'

SELECT * FROM ALL_PART_KEY_COLUMNS WHERE NAME='SALES_HISTORY';

查看列的统计信息

SELECT TABLE_NAME,PARTITION_NAME,COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,LAST_ANALYZED FROM ALL_PART_COL_STATISTICS WHERE TABLE_NAME='SALES_HISTORY';

select segment_name,partition_name,segment_type,tablespace_name from dba_segments where tablespace_name like 'P%';

查看分区索引信息

select index_name,table_name,partitioning_type,partition_count from user_part_indexes where table_name='SALES_HISTORY';

select index_name,partition_name,status,tablespace_name,blevel,leaf_blocks,distinct_keys from user_ind_partitions;

创建范围索引分区表

CREATE TABLE sales
    ( invoice_no NUMBER,
      sale_year  INT NOT NULL,
      sale_month INT NOT NULL,
      sale_day   INT NOT NULL )
  PARTITION BY RANGE (sale_year, sale_month, sale_day)
    ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
        TABLESPACE tsa,
      PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
        TABLESPACE tsb,
      PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
        TABLESPACE tsc,
      PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
        TABLESPACE tsd );

下面的是创建了hash分区，将数据放到了gear1,gear2,gear3,gar4这4个表空间中

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4
   STORE IN (gear1, gear2, gear3, gear4);

下面的是列表分区CREATE TABLE q1_sales_by_region
      (deptno number,
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

可以再堆表和索引组织表上创建分区表，除了包含long或long raw的表。可以创建非分区全局索引，范围或hash分区全局索引，在分区表上创建本地索引。

判断一条记录在哪个分区上 方法一------------------------------------------------------------------------------

SQL> select dbms_rowid.rowid_object('AAAM1SAAHAAAADSABP') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAM1SAAHAAAADSABP')
---------------------------------------------
                                        52562

SQL> select subobject_name,object_name from dba_objects where object_id=52562;

SUBOBJECT_NAME OBJECT_NAME
--------------------------------------------------------------------------------

P2 SALES_HISTORY

方法二---------------------------------------------------------------------------------------------------------

SQL> select dbms_rowid.rowid_relative_fno('AAAM1SAAHAAAADSABP') from dual;

DBMS_ROWID.ROWID_RELATIVE_FNO('AAAM1SAAHAAAADSABP')
---------------------------------------------------
                                                  7

SQL> select dbms_rowid.rowid_block_number('AAAM1SAAHAAAADSABP') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAM1SAAHAAAADSABP')
---------------------------------------------------
                                                210

SQL> select owner,file_id,segment_name, segment_type, block_id, partition_name f
rom dba_extents where  file_id=7 and block_id<=210 and (block_id + blocks- 1) >=
 210;

OWNER                             FILE_ID
------------------------------ ----------
SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE         BLOCK_ID PARTITION_NAME
------------------ ---------- ------------------------------
SYS                                     7
SALES_HISTORY
TABLE PARTITION           137 P2

查看一个分区上有多少记录，需要收集统计信息，否则是空的，不准确的。

select table_name,partition_name,num_rows from dba_tab_partitons;

 select count(*) from sales_history partition(p1);

创建范围分区全局索引

CREATE INDEX month_ix ON sales(sales_month)
   GLOBAL PARTITION BY RANGE(sales_month)
      (PARTITION pm1_ix VALUES LESS THAN (2)
       PARTITION pm2_ix VALUES LESS THAN (3)
       PARTITION pm3_ix VALUES LESS THAN (4)
       PARTITION pm4_ix VALUES LESS THAN (5)
       PARTITION pm5_ix VALUES LESS THAN (6)
       PARTITION pm6_ix VALUES LESS THAN (7)
       PARTITION pm7_ix VALUES LESS THAN (8)
       PARTITION pm8_ix VALUES LESS THAN (9)
       PARTITION pm9_ix VALUES LESS THAN (10)
       PARTITION pm10_ix VALUES LESS THAN (11)
       PARTITION pm11_ix VALUES LESS THAN (12)
       PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

创建一个hash分区全局索引

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
     PARTITION BY HASH (c1,c2)
     (PARTITION p1  TABLESPACE tbs_1,
      PARTITION p2  TABLESPACE tbs_2,
      PARTITION p3  TABLESPACE tbs_3,
      PARTITION p4  TABLESPACE tbs_4);