| Can this query be improved for performance? [message #445495] |
Tue, 02 March 2010 03:55  |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
while bad_records_cursor_rec%found
loop
update reviews
set invalid_review='Y'
where isbn = bad_record_cursor.isbn
and book_id = bad_recods_cursor.id
fetch bad_records_cursor into bad_records_cursor_rec;
end loop;
The above query results in a read on a table that has about 10,000 rows and for each row it searches another table that has about 3 million rows. This means it will search the 3 million row table for each of the rows in the 10,000 row table. Is there any way i can improve the query.
Is there any difference in performance if i do something like this
update reviews
set invalid_review='Y'
where exists <bad rows table>
thanks
|
|
|
|
|
|
|
|
|
|
| Re: Can this query be improved for performance? [message #445512 is a reply to message #445507] |
Tue, 02 March 2010 05:07  |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
Basicly i have two tables with the following columns
Reviews
-------
id
isbn
name
title
author
invalid_review
Bad_records
-----------
isbn
book_id
reason_rejected
The bad_records table contains rows that should be marked as invalid in the reviews tables. What i am trying to do is set the invalid_review columns to 'Y' for each row that is in the "Bad_records" table in the "reviews" table.
The while loop shown above just loops through each row in the bad_records table and update the reviews table if the isbn and book_id match. This is done by selecting all records in bad_records into a cursor.
while bad_records_cursor_rec%found
loop
update reviews
set invalid_review='Y'
where isbn = bad_record_cursor.isbn
and book_id = bad_recods_cursor.id
fetch bad_records_cursor into bad_records_cursor_rec;
end loop;
This means that for each row in the bad_Records table i will scan 2 million rows in the reviews table to find the record to mark as invalid. This seems ineficient. How can i do it in one query? Could you post an example.
THanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
