RE: Index Rebuilds

Doyou really need to actual count from the query, or you just need to know if 
there is at least one row there? If you only need to see if there is such row 
and do not care what the value is, then you can use
 
select count(*)
from
objects where type = 'live'  and rownum=1;
 
which could boost the performance.
 
Just a thought.
 
Guang

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Stalin
Sent: Fri 7/28/2006 7:08 PM
To: Oracle Discussion List
Subject: Index Rebuilds



Hi All,

One of system started to choke today and apparantly it turned to be
the sql that was executed more often had sub-optimal plan. After
digging around i noticed that the index was fragmented as the index
was heavily inserted/deleted and the query was using tablescan to
index fast full scan. The index was a contcatenated index on type and
last modified date which gets updated on last_modified_date often.

LBLKS: 66653
BlVL: 3
CF: 679426
NROWS: 829734
AVG_DATA_BLK/KEY: 2

Coalescing the index got the lblks to 11k and the desired plan
however, the performance is still under water. 10046 trace on the sql
is

select count(*)
from
objects where type = 'live'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     10.39      31.07      65484      69693          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     10.40      31.08      65484      69693          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
 832154   INDEX FAST FULL SCAN IX_TYPE_LASTMODIFIED (object id 25015)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                       536        0.00          0.23
  db file scattered read                       5518        0.09         25.62
  SQL*Net message from client                     2       22.65         22.65

Any suggestions as to what i should be looking further. I really don't
want to rebuild the index :)

Thanks,
Stalin
--
http://www.freelists.org/webpage/oracle-l

Other related posts: