-
Query doing lot of disk reads.
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
-
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.
-
table contains around 1444422 records. So i dont think full scan will help. And yes clustering factor is "1240861"
Rgds
Parag
-
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
-
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
-
why dont you try the full table scan? you are updating 10% of the records - prime target for a FTS
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|