analyze index : Analyze « SQL Plus « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » SQL Plus » Analyze 
analyze index
  
SQL>
SQL> create table indextest as select from dba_objects
  2  where owner in ('OUTLN','PUBLIC','SCOTT','SYS','SYSTEM');

Table created.

SQL>
SQL>
SQL>  create index indxtest_objname_idx
  2   on indextest (object_name)
  3   pctfree 0;

Index created.

SQL>
SQL>  analyze table indextest compute statistics;

Table analyzed.

SQL>
SQL>  analyze index indxtest_objname_idx validate structure;

Index analyzed.

SQL>
SQL>  select name, height, lf_blks, pct_used
  2   from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDXTEST_OBJNAME_IDX                    2         36         97

SQL>
SQL>
SQL>  insert into indextest (owner, object_name)
  2   values ('a','a');

row created.

SQL>
SQL>  commit;

Commit complete.

SQL>
SQL>  analyze index indxtest_objname_idx validate structure;

Index analyzed.

SQL>
SQL>
SQL>  select name, height, lf_blks, pct_used
  2   from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDXTEST_OBJNAME_IDX                    2         36         97

SQL>
SQL>  insert into indextest (owner, object_namevalues ('ZZZZZ','_ZZZZZZZZZZZ');

row created.

SQL>
SQL>  commit;

Commit complete.

SQL>
SQL>
SQL>  analyze index indxtest_objname_idx validate structure;

Index analyzed.

SQL>
SQL>
SQL>  select name, height, lf_blks, pct_used
  2   from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDXTEST_OBJNAME_IDX                    2         36         97

SQL>
SQL>  alter index indxtest_objname_idx rebuild pctfree 10;

Index altered.

SQL>
SQL>  analyze index indxtest_objname_idx validate structure;

Index analyzed.

SQL>
SQL> drop table indextest;

Table dropped.

SQL>
SQL>

   
  
Related examples in the same category
1.analyze materialized view
2.Analyze table with primary key and foreign key
3.Demonstrate IN versus EXISTS performance
4.analyze table tableName compute statistics for table, for all indexes, for all indexed columns;
5.ANALYZE TABLE employee COMPUTE STATISTICS
6.analyze index validate structure
7.analyze table compute statistics
8.analyze table compute statistics for all indexes
9.analyze table compute statistics for a table with two indexes
10.create or replace outline and analyze table compute statistics
11.Analyze a table with/without index
12.Anayze all objects in the APPOWNER schema.
13.Analyze index on varchar2 value and date type value
14.analyze and autotrace full outer join and union
15.analyze and autotrace single column key and multi-column key
16.analyze and autotrace table with primary key
17.analyze index t_idx validate structure
18.analyze table TABLENAME compute statistics;
19.analyze table after creating index
20.analyze table estimate statistics
21.analyze table students compute statistics
22.analyze table t compute statistics for table for columns id;
23.analyze table t compute statistics for table, for all indexes, for all indexed columns
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.