analyze index t_idx validate structure : analyze « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. analyze

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 (JAVA2S.T_IDX) violated


SQL> insert into t values ( 1, NULL );
insert into t values ( 1, NULL )
*
ERROR at line 1:
ORA-00001: unique constraint (JAVA2S.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>