Query doing lot of disk reads.

[paragp1981]

Hi,
In one of our application.One update query is doing lot of disk reads .It is using index but still it is slow.

Query:-

UPDATE REBOOKER SET REBOOK_CO='Y' WHERE RBK_ID=:B1

Explain plan is this:-
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'REBOOKER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_RBK_ID' (NON-UNIQUE)

Query is doing around 30831 disk reads for 123712 rows.
This columns contains lot of repeated values. Is non-unique index ok.

Anything else i need to do.

Thanks in advance :-)

Rgds
Parag

[slimdave]

How many rows are there in that table, and can you read the clustering factor of the I_RBK_ID index from user_indexes? I'm wondering whether you might be better with a full table scan here.

[paragp1981]

table contains around 1444422 records. So i dont think full scan will help. And yes clustering factor is "1240861"

Rgds
Parag

[slimdave]

You're reading about one tenth of the table then -- I'd try hinting the query to use a full scan and see how that performs

[paragp1981]

Dave,
Thanks for clue I read all about clustering factor. Hmmmm Full table scan i dont think is good idea. Will reducing clustering factor help. I read 2-3 ways to do it like export import. Creating table_bkp as select * from original. then drop original and again create original. Like that will that help.

I read this article..
http://www.dizwell.com/html/clustering_factor.html

Rgds
Parag

[davey23uk]

why dont you try the full table scan? you are updating 10% of the records - prime target for a FTS