RE: Index Rebuilds (aka scan a smaller index)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 30 Jul 2006 11:31:06 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKEEKFIFAA.mwf@rsiz.com>

Others have already suggested the rownum=1 (or rownum<2) trick if you're really just checking for existence.

Barring that, isn't this pretty much what bit map indexes were created for? True, you'll have another index, but IF this query is important and frequently issued AND response time is important to the business, that is one way to make it a lot quicker.

Alternatively, you could partition by type and at least get some pruning.

If it gets into the level of importance of extreme solutions, you could map your types to numbers putting 'live' low and index "type_id" by itself. Then type_id <= 1 (ie. the lookup of 'live' in the table types table) should be pretty quick.

This is partly presuming that changes to type are less volatile than changes to l_m_d, but even so it should be much smaller. The type being volatile would mitigate against the bitmap and partitioning solutions.

Now if this isn't either frequent or very important to the business that it is quick when the question is asked, then you've identified a case of CTD (complusive tuning disorder) versus a case for an extreme solution.

Of course you could be exploring this for academic reasons to add to your kit bag of solutions. That's always my excuse when I realize I've succumbed to CTD. Oh, wait, I don't have CTD, I'm doing research!

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stalin
Sent: Friday, July 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
--

<snip>

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jul 30 2006 - 10:31:06 CDT