建立查詢分區表和索引

檢視分區表的資訊

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);