创建分区表及分区索引(全局)

SQL>  create table MyTest00

 (

        id number,

        name nvarchar2(100)

 )

 partition by range(id)

 (

  partition T1 values less than (100) tablespace myTableSpace_1,

  partition T2 values less than (1000) tablespace myTableSpace_2,

  partition T3 values less than (2000) tablespace myTableSpace_3);

SQL>   insert into MyTest00 values (-1,'f1111');

1 row created.

SQL> insert into MyTest00 values (10,'f1111');

1 row created.

SQL>  insert into MyTest00 values (20,'f1112');

1 row created.

SQL>  insert into MyTest00 values (100,'f1112');

1 row created.

SQL>  insert into MyTest00 values (200,'f1112');

1 row created.

SQL>  insert into MyTest00 values (1991,'f1112');

1 row created.

SQL>  insert into MyTest00 values (2001,'f1112');

 insert into MyTest00 values (2001,'f1112')

             *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

SQL>   select * from MyTest00;

        ID

----------

NAME

--------------------------------------------------------------------------------

        -1

f1111

        10

f1111

        20

f1112

        ID

----------

NAME

--------------------------------------------------------------------------------

       100

f1112

       200

f1112

      1991

f1112

6 rows selected.

SQL>  select * from myTest00 partition(T1);

        ID

----------

NAME

--------------------------------------------------------------------------------

        -1

f1111

        10

f1111

        20

f1112

SQL>  select * from myTest00 partition(T2);

        ID

----------

NAME

--------------------------------------------------------------------------------

       100

f1112

       200

f1112

SQL> select * from myTest00 partition(T3);

        ID

----------

NAME

--------------------------------------------------------------------------------

      1991

f1112

创建全局分区索引

SQL> create index global_index_mytest00 on mytest00(id) GLOBAL

partition by  range(id)

 (

  partition T1 values less than (100) tablespace users,

  partition T2 values less than (1000) tablespace users,

  partition T3 values less than (maxvalue) tablespace users);

Index created.

SQL> set autotrace traceonly

SQL> select * from mytest00 where id=20;

Execution Plan

----------------------------------------------------------

Plan hash value: 2770249213

--------------------------------------------------------------------------------

---------------------------------------------

| Id  | Operation                           | Name                  | Rows  | By

tes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

---------------------------------------------

|   0 | SELECT STATEMENT                    |                       |     1 |

165 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE             |                       |     1 |

165 |     2   (0)| 00:00:01 |     1 |     1 |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| MYTEST00              |     1 |

165 |     2   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                 | GLOBAL_INDEX_MYTEST00 |     1 |

    |     1   (0)| 00:00:01 |     1 |     1 |

--------------------------------------------------------------------------------

---------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("ID"=20)

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

          9  recursive calls

          0  db block gets

         29  consistent gets

          0  physical reads

          0  redo size

        465  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2137543/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448824/viewspace-2137543/