HI all
i have two tables . both are having references.
i need to update one table bye joining those two common columns.
first table has 4crors of data and second one 1.5 crors of data
i had written a query
update table_name set column_name=(select column_name from table_name2 where table_name2.matching_column=table_name.matching_column)
for this query its taking more than 2.30 hrs time. here i canceled the query.
please any body can help me in this regard to improve query performence.
can i use /*+ direct */ command in update query.
thanks in advance
any hints for improving update query performance
Moderator: NorbertKrupa
Re: any hints for improving update query performance
Hi bhupal,
Yes you can use a direct keyword in update query. This will write all the records onto the disk instead of the memory and then to disk.
You can also try with the merge command in Vertica.
1. Make sure no other queries are running while your updates are going.
2. Try assigning a customized resource pool for your query.
3. Make sure your tables are distributed on 'matching_column' so that all reside on the same node and there is no re distribution of data.
4. Analyze the explain plan
Hope this helps
Yes you can use a direct keyword in update query. This will write all the records onto the disk instead of the memory and then to disk.
You can also try with the merge command in Vertica.
Code: Select all
update [ /*+ direct */ ] table_name set column_name=(select column_name from table_name2 where table_name2.matching_column=table_name.matching_column)
2. Try assigning a customized resource pool for your query.
3. Make sure your tables are distributed on 'matching_column' so that all reside on the same node and there is no re distribution of data.
4. Analyze the explain plan
Hope this helps