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@(protected)
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
