Mega Code Archive

Analyze index t_idx validate structure

SQL> SQL> SQL> create table t ( x int, y int ); Table created. SQL> SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( 1, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> SQL> analyze index t_idx validate structure; Index analyzed. SQL> SQL> select name, lf_rows from index_stats; NAME LF_ROWS ------------------------------ ---------- T_IDX 3 SQL> SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); insert into t values ( NULL, 1 ) * ERROR at line 1: ORA-00001: unique constraint (RNTSOFT.T_IDX) violated SQL> insert into t values ( 1, NULL ); insert into t values ( 1, NULL ) * ERROR at line 1: ORA-00001: unique constraint (RNTSOFT.T_IDX) violated SQL> SQL> select x, y, count(*) from t group by x,y having count(*) > 1; X Y COUNT(*) ---------- ---------- ---------- 2 SQL> SQL> drop table t; Table dropped. SQL> SQL> create table t ( x int, y int NOT NULL ); Table created. SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> SQL> begin 2 dbms_stats.gather_table_stats(user,'T'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> set autotrace on SQL> SQL> select * from t where x is null; X Y ---------- ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2946670127 ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 | |* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("X" IS NULL) Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 456 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL>