金沙澳门官网下载app-金沙澳门官网网址

金沙澳门官网下载app索引建立两种模式

复制代码 代码如下: SELECT height, /*Height of the B-Tree*/ blocks, /* Blocks in the index segment */ name, /*index name */ lf_rows, /* number of leaf rows in the index */ lf_blks, /* number of leaf blocks in the index */ del_金沙澳门官网下载app,lf_rows, /* number of deleted leaf rows in the index */ rows_per_key /* average number of rows per distinct key */ blk_gets_per_access /* consistent mode block reads (gets) */ FROM INDEX_STATS WHERE NAME='INDEX_NAME'; 复制代码 代码如下: ANALYZE index INDEX_NAME VALIDATE STRUCTURE HEIGHT: This column refers to the height of the B-tree index, and it's usually at the 1, 2, or 3 level. If large inserts push the index height beyond a level of 4, it's time to rebuild, which flattens the B-tree. DEL_LF_ROWS: This is the number of leaf nodes deleted due to the deletion of rows. Oracle doesn't rebuild indexes automatically and, consequently, too many deleted leaf rows can lead to an unbalanced B-tree. BLK_GETS_PER_ACCESS: You can look at the BLK_GETS_PER_ACCESS column to see how much logical I/O it takes to retrieve data from the index. If this row shows a double-digit number, you should probably start rebuilding the index.

---查询索引名称,类型,服务的表

SQL> select index_name,index_type,table_name,uniqueness from user_indexes;

 

EMP_DEPARTMENT_IX  NORMAL     EMPLOYEES  NONUNIQUE

 

INDEX_NAME              INDEX_TYPE TABLE_NAME UNIQUENES


EMP_NAME_IX          NORMAL     EMPLOYEES      NONUNIQUE

EMP_MANAGER_IX   NORMAL     EMPLOYEES      NONUNIQUE

EMP_EMP_ID_PK       NORMAL     EMPLOYEES      UNIQUE

EMP_EMAIL_UK        NORMAL     EMPLOYEES      UNIQUE

EMP_JOB_IX              NORMAL     EMPLOYEES      NONUNIQUE

DEPT_LOCATION_IX NORMAL     DEPARTMENTS   NONUNIQUE                            

DEPT_ID_PK              NORMAL     DEPARTMENTS  UNIQUE

 

---查询索引的名称,表名,列名                            

SQL> select index_name,table_name,column_name from user_ind_columns order by 2,3;

 

INDEX_NAME      TABLE_NAME   COLUMN_NAME


COUNTRY_C_ID_PK COUNTRIES    COUNTRY_ID

DEPT_ID_PK          DEPARTMENTS  DEPARTMENT_ID

DEPT_LOCATION_IX DEPARTMENTS  LOCATION_ID

EMP_DEPARTMENT_IX EMPLOYEES    DEPARTMENT_ID

EMP_EMAIL_UK    EMPLOYEES    EMAIL

EMP_EMP_ID_PK   EMPLOYEES    EMPLOYEE_ID

EMP_NAME_IX     EMPLOYEES    FIRST_NAME

EMP_JOB_IX      EMPLOYEES    JOB_ID

EMP_NAME_IX     EMPLOYEES    LAST_NAME

EMP_MANAGER_IX  EMPLOYEES    MANAGER_ID

 

**索引建立两种模式: 隐士建立和显示建立;

SQL>  create table commit as select * from scott.emp;

SQL> select * from user_indexes;

no rows selected

SQL>  alter table commit add constraint zhu_fk primary key(empno);

SQL> select index_name,index_type,table_owner,table_name,uniqueness,tablespace_name,blevel from user_indexes;

索引名称          索引类型      用户            服务表名          类型         表空间存储     高度                     

ZHU_FK          NORMAL     YANG       COMMIT       UNIQUE       USERS             0

 

SQL> select index_name,table_name,column_name from user_ind_columns;

 

INDEX_NAME      TABLE_NAME   COLUMN_NAME


ZHU_FK          COMMIT       EMPNO

 

==显示创建:create unique index  abc on commit(comm);

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

不能创建唯一索引,副键创建

SQL> create unique index  abc on commit(ENAME);

SQL> select index_name,index_type,table_owner,table_name,uniqueness,tablespace_name,blevel from user_indexes;

 

INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME   UNIQUENESS TABLESPACE   BLEVEL



ZHU_FK     NORMAL     YANG       COMMIT       UNIQUE     USER     0

ABC        NORMAL     YANG       COMMIT       UNIQUE     USER     0

SQL> select index_name,table_name,column_name from user_ind_columns;

 

INDEX_NAME TABLE_NAME   COLUMN_NAME


ABC        COMMIT       ENAME

ZHU_FK     COMMIT       EMPNO

 

一个列可以有多个索引吗?

ORA-01408: such column list already indexed 这样的列表已经有索引的

 

索引如何储存的

SQL> analyze index abc validate structure;          --分析一下

SQL>  select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

高度          索引总块      枝干         叶子块        叶子内行数     叶子中被删除的行数

HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS


     1          8                    0          1                       15           0

SQL> update commit set ename='YANG' where empno=7934;

SQL> commit;

SQL> analyze index abc validate structure;

SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

 

HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS


     1          8          0          1         16           0    ==发现cOmmit,提交,变更后,块中储存多了一行

 

 

create unique index  abc on commit(comm);===一个主键+一个唯一,限制了插入数据;

ORA-00001: unique constraint (YANG.ZHU_FK) violated

ORA-00001: unique constraint (YANG.ABC) violated

alter table commit disable constraint zhu_fk;

alter table commit disable constraint abc;

ORA-02431: cannot disable constraint (ABC) - no such constraint ---没有这样的约束

SQL> select INDEX_NAME,status from user_indexes;

 

INDEX_NAME STATUS


ABC        VALID

SQL> alter index abc unusable;

SQL> select INDEX_NAME,status from user_indexes;


ABC        UNUSABLE

 

SQL> create index a1 on commit(empno);

 

ORA-01502: index 'YANG.ABC' or partition of such index is in unusable state

---插入数据发现,还是报错;

SQL> alter index abc rebuild;

drop index abc;

本文由金沙澳门官网下载app发布于金沙澳门官网,转载请注明出处:金沙澳门官网下载app索引建立两种模式

您可能还会对下面的文章感兴趣: