当前位置：Gxlcms > mysql > 索引组织表

索引组织表

时间：2021-07-01 10:21:17 帮助过：58人阅读

索引组织表 对于索引组织表必须有主键，问题它没有rowid，且只有逻辑rowid猜测到溢出部分。索引组织表可以进行分区，但只能是范围分区、散列分区、列表分区，但不能为复合分区。另外索引组织 表其实就是根据表的主键按照一定的顺序将数据表和索引进行了整合

索引组织表

对于索引组织表必须有主键，问题它没有rowid，且只有逻辑rowid猜测到溢出部分。索引组织表可以进行分区，但只能是范围分区、散列分区、列表分区，但不能为复合分区。另外索引组织

表其实就是根据表的主键按照一定的顺序将数据表和索引进行了整合一张表，如果该字段过大，那么访问相关特定数据行就会出现穿越多个块，因此对于不经常访问的列归为溢出部分，如果

启用溢出部分需要使用overflow关键字，including字段指定该列之后的列都会放到溢出部分中，另外pctthreshold指定当该块数据达到一个标准值之后，那么其余部分将会放到溢出部分中。

  1. SQL> create table locations_iot(
  2.   2  location_id number(4) not null,
  3.   3  street_address varchar2(40),
  4. postal_code varchar2(12),
  5.   4    5  city varchar2(20)
  6.   6  )
  7.   7  organization index; 
  8. organization index
  9.              *
  10. ERROR at line 7:
  11. ORA-25175: no PRIMARY KEY constraint found
  12. SQL> create table locations_iot(
  13.   2  location_id number(4) not null,
  14.   3  street_address varchar2(40),
  15. postal_code varchar2(12),
  16.   4    5  city varchar2(20),
  17.   6  constraint locations_iot_pk primary key(location_id)
  18.   7  )
  19.   8  organization index;
  20. Table created.
  21. SQL> create table locatios_iot(
  22.   2  location_id number(4) not null,
  23.   3  street_address varchar2(40),
  24.   4  postal_code varchar2(12),
  25.   5  city varchar2(20),
  26.   6  constraint locatios_iot primary key(location_id,street_address)
  27.   7  ) organization index
  28.   8  partition by list(street_address)
  29.   9  (
  30.  10  partition part1 values('Happy','New','YEAR') tablespace test,
  31.  11  partition part2 values('My','leadership','do','not','let','me','go','home') tablespace test
  32.  12  );
  33. Table created.
  34. SQL>

使用analyze table xxx list chained rows;确定pctthreshold设置是否合理。
出现表的链接记录和迁移记录的rowid都会保存到表chaind_rows中，但是该表必须提前创建，
eg：

  1. SQL> @?/rdbms/admin/utlchain.sql
  2. Table created.
  3. SQL> select table_name from user_tables where table_name='CHAINED_ROWS';
  4. TABLE_NAME
  5. ------------------------------
  6. CHAINED_ROWS
  7. SQL> analyze table t list chained rows;
  8. Table analyzed.
  9. SQL> select * from chained_rows;
  10. no rows selected
  11. SQL>

注意在添加映射表或是overflow之后都会自动创建相关表：
eg：

  1. SQL> alter table employees move mapping table;
  2. Table altered.
  3. SQL> select table_name,iot_name,iot_type from user_tables;                             
  4. TABLE_NAME                     IOT_NAME                       IOT_TYPE
  5. ------------------------------ ------------------------------ ------------
  6. SYS_IOT_MAP_87909              EMPLOYEES                      IOT_MAPPING
  7. SYS_IOT_OVER_87909             EMPLOYEES                      IOT_OVERFLOW
  8. EMPLOYEES                                                     IOT
  9. ACHIVEMENT
  10. ACHIVEEMENT
  11. T_LIST
  12. RANGE_LIST
  13. RANGE_LIST_PARTITION
  14. CHAINED_ROWS
  15. EMP
  16. SALGRADE
  17. TABLE_NAME                     IOT_NAME                       IOT_TYPE
  18. ------------------------------ ------------------------------ ------------
  19. DEPT
  20. 12 rows selected.
  21. SQL> alter table employees move nomapping;
  22. Table altered.
  23. SQL> select table_name,iot_name,iot_type from user_tables;                             
  24. TABLE_NAME                     IOT_NAME                       IOT_TYPE
  25. ------------------------------ ------------------------------ ------------
  26. SYS_IOT_OVER_87909             EMPLOYEES                      IOT_OVERFLOW
  27. EMPLOYEES                                                     IOT
  28. ACHIVEMENT
  29. ACHIVEEMENT
  30. T_LIST
  31. RANGE_LIST
  32. RANGE_LIST_PARTITION
  33. CHAINED_ROWS
  34. EMP
  35. SALGRADE
  36. DEPT
  37. 11 rows selected.
  38. SQL> alter table employees move mapping table;
  39. Table altered.
  40. SQL> select table_name,iot_name,iot_type from user_tables;
  41. TABLE_NAME                     IOT_NAME                       IOT_TYPE
  42. ------------------------------ ------------------------------ ------------
  43. SYS_IOT_MAP_87909              EMPLOYEES                      IOT_MAPPING
  44. SYS_IOT_OVER_87909             EMPLOYEES                      IOT_OVERFLOW
  45. EMPLOYEES                                                     IOT
  46. ACHIVEMENT
  47. ACHIVEEMENT
  48. T_LIST
  49. RANGE_LIST
  50. RANGE_LIST_PARTITION
  51. CHAINED_ROWS
  52. EMP
  53. SALGRADE
  54. TABLE_NAME                     IOT_NAME                       IOT_TYPE
  55. ------------------------------ ------------------------------ ------------
  56. DEPT
  57. 12 rows selected.
  58. SQL>

压缩索引组织表：
主要是值压缩复合主键列，如果主键设置为单列则无法进行压缩。
eg：

  1. SQL> alter table employees move compress 1;      
  2. alter table employees move compress 1
  3.             *
  4. ERROR at line 1:
  5. ORA-25193: cannot use COMPRESS option for a single column key
  6. SQL> SET PAGESIZE 2000
  7. SQL> R
  8.   1* SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','AMY') FROM DUAL
  9. DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','AMY')
  10. --------------------------------------------------------------------------------
  11.   CREATE TABLE "AMY"."EMPLOYEES"
  12.    (    "EMPLOYEE_ID" NUMBER(6,0),
  13.         "FIRST_NAME" VARCHAR2(20),
  14.         "LAST_NAME" VARCHAR2(25),
  15.         "EMAIL" VARCHAR2(24),
  16.         "PHONE_NUMBER" VARCHAR2(20),
  17.         "HIREE_DATE" DATE,
  18.          CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE
  19.    ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  20.   STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  21.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  22.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  23.   TABLESPACE "TEST"
  24.  PCTTHRESHOLD 40 MAPPING TABLE  INCLUDING "LAST_NAME" OVERFLOW
  25.  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  26.   STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  27.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  28.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  29.   TABLESPACE "TEST"
  30. SQL> DROP TABLE EMPLOYEES;
  31. Table dropped.
  32. SQL> PURGE RECYCLEBIN;
  33. Recyclebin purged.
  34. SQL> CREATE TABLE "AMY"."EMPLOYEES"
  35.   2     (    "EMPLOYEE_ID" NUMBER(6,0),
  36.   3          "FIRST_NAME" VARCHAR2(20),
  37.   4          "LAST_NAME" VARCHAR2(25),
  38.   5          "EMAIL" VARCHAR2(24),
  39.   6          "PHONE_NUMBER" VARCHAR2(20),
  40.   7          "HIREE_DATE" DATE,
  41.   8           CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID","FIRST_NAME") ENABLE
  42.   9     ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  43.  10    STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  44.  11    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  45.  12    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  46.  13    TABLESPACE "TEST"
  47.  14   PCTTHRESHOLD 40 MAPPING TABLE  INCLUDING "LAST_NAME" OVERFLOW
  48.  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  49.  15   16    STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  50.  17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  51.  18    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  52.  19    TABLESPACE "TEST";
  53. Table created.
  54. SQL> ALTER TABLE EMPLOYEES MOVE COMPRESS 1;
  55. Table altered.

创建二级位图索引：‘

  1. SQL> 
  2. SQL> ALTER TABLE EMPLOYEES MOVE MAPPING TABLE;
  3. Table altered.
  4. SQL> DESC EMPLOYEES;
  5.  Name                                                                                                              Null?    Type
  6.  ----------------------------------------------------------------------------------------------------------------- -------- 
  7. ----------------------------------------------------------------------------
  8.  EMPLOYEE_ID                                                                                                       NOT NULL NUMBER(6)
  9.  FIRST_NAME                                                                                                        NOT NULL VARCHAR2(20)
  10.  LAST_NAME                                                                                                                  VARCHAR2(25)
  11.  EMAIL                                                                                                                      VARCHAR2(24)
  12.  PHONE_NUMBER                                                                                                               VARCHAR2(20)
  13.  HIREE_DATE                                                                                                                 DATE
  14. SQL> CREATE BITMAP INDEX EMPLOYEES_BTIDX1 ON EMPLOYEES(LAST_NAME);
  15. Index created.
  16. SQL>

由于时间之后索引组织表可能存在叶块分裂导致逻辑猜测rowid不准确，需要重新调整索引：

重建索引组织表：
使用alter table move操作，如果是在线不会防止其他 访问应该使用online字，但是如果是分区索引组织表，那么只能通过user_tab_partitions获得分区名在对每个分区进行move
alter table employees move online；
alter table employees move partition part1;

查看索引组织表信息：
select a.table_name,
a.index_name,
a.index_type,
a.pct_threshold,
nvl(column_name, 'NONE') include_column
from user_indexes a
join user_tab_columns b
on (a.table_name = b.table_name)
and (a.include_column = b.column_id)
where index_type = 'IOT - TOP';

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！