文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> 模式对象> 索引

索引

分享
更新时间: 2019/08/09 GMT+08:00

索引支持更快地检索记录,可以用于性能调优。索引为索引列中显示的每个值创建条目。在DWS中创建索引时,无法同时指定模式名称和索引名称。索引会自动创建在索引表所在的模式中。

输入Create Index(指定模式名

CREATE INDEX scott.ix_tab1_col1 ON scott.tab1 (col1) 
   /*TABLESPACE users*/ 
   pctfree 10 initrans 2 
   storage ( initial 256 K NEXT 256 K 
minextents 1 maxextents 
unlimited );

输出

CREATE INDEX ix_tab1_col1 ON scott.tab1 (col1) 
   /*TABLESPACE users*/ 
   pctfree 10 initrans 2 
   storage ( initial 256 K NEXT 256 K 
minextents 1 maxextents 
unlimited );

输入:局部分区索引:范围分区表,指定全局分区索引

CREATE TABLE sales
  ( prod_id NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold NUMBER(10,2)
  , time_id DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS 
  THAN (TO_DATE('01-APR-2006'
  ,'dd-MON-yyyy'))
     /*TABLESPACE users*/
 , PARTITION sales_q2_2006 VALUES LESS   
  THAN (TO_DATE('01-JUL-2006'
  ,'dd-MON-yyyy'))
    /*TABLESPACE users*/
);

CREATE INDEX amount_sold_ix ON sales(amount_sold, quantity_sold)
   GLOBAL PARTITION BY 
RANGE(amount_sold)
      ( PARTITION p_100 VALUES LESS THAN 
   (100)
      , PARTITION p_1000 VALUES LESS 
   THAN (1000)
      , PARTITION 
   p_greater_than_1000000 VALUES 
   LESS THAN (maxvalue)
);

输出

CREATE TABLE sales
  ( prod_id NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold NUMBER(10,2)
  , time_id DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS 
  THAN (TO_DATE('01-APR-2006'
  ,'dd-MON-yyyy'))
     /*TABLESPACE users*/
 , PARTITION sales_q2_2006 VALUES LESS   
  THAN (TO_DATE('01-JUL-2006'
  ,'dd-MON-yyyy'))
    /*TABLESPACE users*/
);

CREATE INDEX amount_sold_ix ON sales(amount_sold, quantity_sold)
   LOCAL;

输入:列表分区表,指定全局分区索引

CREATE TABLE sales_by_region 
         ( deptno NUMBER
         , deptname varchar2 (20)
         , quarterly_sales NUMBER (10,2)
         , quantity_sold NUMBER(3)
         , 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_southcentral VALUES ( 'OK', 'TX' )
           );

CREATE INDEX sale_by_reg_ix ON 
 sales_by_region (quarterly_sales,quantity_sold)
   GLOBAL PARTITION BY RANGE(quarterly_sales)
    ( PARTITION p_100 VALUES LESS THAN (100)
    , PARTITION p_1000 VALUES LESS THAN (1000)
    , PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
     );

输出

CREATE TABLE sales_by_region 
         ( deptno NUMBER
         , deptname varchar2 (20)
         , quarterly_sales NUMBER (10,2)
         , quantity_sold NUMBER(3)
         , 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_southcentral VALUES ( 'OK', 'TX' )
           )*/;

CREATE INDEX sale_by_reg_ix ON 
 sales_by_region (quarterly_sales,quantity_sold)
   ;

输入:局部分区索引:范围分区表,指定局部分区索引

CREATE TABLE sales2
  ( prod_id NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold NUMBER(10,2)
  , time_id DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales2_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) /*TABLESPACE users*/
  , PARTITION sales2_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) /*TABLESPACE users*/
 );

CREATE INDEX amount_sold_ix ON sales2(amount_sold)
   LOCAL 
      ( PARTITION sales_q1_ix
      , PARTITION sales_q2_ix
      );

输出

CREATE TABLE sales2
  ( prod_id NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold NUMBER(10,2)
  , time_id DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales2_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) /*TABLESPACE users*/
  , PARTITION sales2_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) /*TABLESPACE users*/
 );

CREATE INDEX amount_sold_ix ON sales2(amount_sold)
   LOCAL 
      ( PARTITION sales_q1_ix
      , PARTITION sales_q2_ix
      );

输入:局部分区索引:含有分区索引的范围分区表,不指定分区名

CREATE TABLE sales
  ( prod_id NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold NUMBER(10,2)
  , time_id DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))/* TABLESPACE users */
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))/* TABLESPACE users */
);
CREATE INDEX amount_sold_idx ON sales(amount_sold) LOCAL;

输出

CREATE
     TABLE
          sales (
               prod_id NUMBER (6)
               ,quantity_sold NUMBER (3)
               ,amount_sold NUMBER (
                    10
                    ,2
               )
               ,time_id DATE
          ) PARTITION BY RANGE (time_id) (
               PARTITION sales_q1_2006
               VALUES LESS THAN (
                    TO_DATE( '01-APR-2006' ,'dd-MON-yyyy' )
               ) /* TABLESPACE users */
               ,PARTITION sales_q2_2006
               VALUES LESS THAN (
                    TO_DATE( '01-JUL-2006' ,'dd-MON-yyyy' )
               ) /* TABLESPACE users */
          ) ;

输入:创建索引并指定 REVERSE 关键字

CREATE INDEX IDX_DB2_CONVER_DTL ON SEAS_DB2_CONVER_DTL (BEATCH_ID) REVERSE
 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE DRMS ;
 

输出

CREATE INDEX IDX_DB2_CONVER_DTL ON SEAS_DB2_CONVER_DTL (
    BEATCH_ID )
/*REVERSE*/
/*PCTFREE 10*/
/*INITRANS 2*/
/*MAXTRANS 255*/
/*COMPUTE STATISTICS*/
/*STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)*/
/*TABLESPACE DRMS */
;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区