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_at_freelists.org 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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 28 2006 - 19:15:15 CDT