Common ORA errors

Comments

2. How frequently index should be rebuild.
Answer: NO, We never need to rebuild index because it is not increase database performance

Where to start? There is so much inaccuracy with your answers my mind boggles.

YES - Index rebuilds are needed occasionally!

Please do research before putting up things like this.

Index rebuild should be done periodically. The period should depend on the data movement.

mazqurayahoocom, you say

Quote:
1. select count(*) or count(columname) which is faster ?
Answer: Both are same in performance in speed.

This is not necessarily correct.
First, count(*) will count the rows but count(columnname) will count the number of rows where the column is not null. So the queries are not equivalent. For example:

orcl> select count(*) from emp;

  COUNT(*)
----------
        14

orcl> select count(comm) from emp;

COUNT(COMM)
-----------
          4

orcl>

Second, performance may be very different because count(*) may be able to use an index but count(columnname) might not be able to:

orcl> select count(*) from emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------

orcl> select count(ename)from emp;

COUNT(ENAME)
------------
          14


Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |     6 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

orcl>

Rebuilding indexes on a schedule is a DBA best practice!!!!!!!!!

http://www.dba-oracle.com/t_scheduling_oracle_index_rebuilding.htm

Rebuilding indexes is indeed a common error. The Burleson website is not a very good reference: some of the information there is out of date or controversial or (as in this case) both. wrt out-of-date, Burleson is talking about indexes in freelist managed tablespaces; do you think he would apply the same reasoning to ASSM tablespaces? wrt to being controversial, he speaks almost exclusively about index rebuilds being beneficial for SELECT. He ignores the effect on INSERT. After you rebuild an index, the keys are compressed into the minimum possible number of blocks. This means that for days afterwards, most of your inserts will require block splits. Because of this performance will be degraded until the index has enough free space in its leaf nodes.