analyze table t compute statistics for table, for all indexes, for all indexed columns : Analyze « SQL Plus « Oracle PL / SQL






  1. Oracle PL / SQL
  2. SQL Plus
  3. Analyze

analyze table t compute statistics for table, for all indexes, for all indexed columns

  
SQL>
SQL> create table t
  2  as
  3  select to_char( to_date('01-jan-1995','dd-mon-yyyy')+rownum, 'yyyymmdd' ) str_date,
  4         to_date('01-jan-1995','dd-mon-yyyy')+rownum date_date
  5    from all_objects
  6    where rownum < 20
  7  /

Table created.

SQL>
SQL> create index i1 on t(str_date);

Index created.

SQL>
SQL> create index t_date_date_idx on t(date_date);

Index created.

SQL>
SQL> analyze table t compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed.

SQL>
SQL> set autotrace on explain
SQL> select * from t
  2  where str_date between '20001231' and '20100101' and rownum < 20;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 508354683

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     1 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    20 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<20)
   2 - filter("STR_DATE">='20001231' AND "STR_DATE"<='20100101')

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20100101','yyyymmdd') and rownum<20;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 508354683

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     1 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    20 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<20)
   2 - filter("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2010-01-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL>
SQL> select * from t
  2  where str_date between '20001231' and '20100101' and rownum<20;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 508354683

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     1 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    20 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<20)
   2 - filter("STR_DATE">='20001231' AND "STR_DATE"<='20100101')

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20100101','yyyymmdd')
  2  and rownum < 20;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 508354683

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |     1 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    20 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<20)
   2 - filter("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2010-01-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;

Table dropped.








Related examples in the same category

1.analyze materialized view
2.analyze index
3.Analyze table with primary key and foreign key
4.Demonstrate IN versus EXISTS performance
5.analyze table tableName compute statistics for table, for all indexes, for all indexed columns;
6.ANALYZE TABLE employee COMPUTE STATISTICS
7.analyze index validate structure
8.analyze table compute statistics
9.analyze table compute statistics for all indexes
10.analyze table compute statistics for a table with two indexes
11.create or replace outline and analyze table compute statistics
12.Analyze a table with/without index
13.Anayze all objects in the APPOWNER schema.
14.Analyze index on varchar2 value and date type value
15.analyze and autotrace full outer join and union
16.analyze and autotrace single column key and multi-column key
17.analyze and autotrace table with primary key
18.analyze index t_idx validate structure
19.analyze table TABLENAME compute statistics;
20.analyze table after creating index
21.analyze table estimate statistics
22.analyze table students compute statistics
23.analyze table t compute statistics for table for columns id;