| Re: simple update statement [message #375275] |
Sun, 19 August 2001 22:17 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Dear Trevorb, here I guess you are giving a repeated condition forcing oracle to check twice and taking more time than expected for your query.
OK, let us review your code with a closer look.
update customer
set (first_name, last_name, do_not_email)=
(select customer_stage.first_name, customer_stage.last_name
,customer_stage.do_not_email
from customer_stage
where CUSTOMER_STAGE.CUSTOMER_KEY = CUSTOMER.CUSTOMER_KEY
AND CUSTOMER_STAGE.STORE_KEY = CUSTOMER.STORE_KEY)
where exists
(select *
from customer_stage
where CUSTOMER_STAGE.CUSTOMER_KEY = CUSTOMER.CUSTOMER_KEY
AND CUSTOMER_STAGE.STORE_KEY = CUSTOMER.STORE_KEY)
Have a look at the UPPERed code of your query. You do find that the condition given in correlated sub-query and outer main query is same and repeated.
I dont understand why do you repeat the same condition in the main and subquerys. The subquery returns rows from CUSTOMER_STAGE those which only match the CUSTOMER_KEY and STORE_KEY of CUSTOMER. The rows will be compared only when they exist. Then why do you check their existence again in the main EXISTS query wasting time to process it. It is absolutely unnecessary.
As you are aware of and it is sure that an SQL query is processed with a bit of time consumption, and especially a correlated subquery (which mostly depends on its parent query) takes some more time than a simple/normal one.
So obviously your query takes double the time it has to take for a single query, since it has repeated conditions. Try like this:
update customer
set (first_name, last_name, do_not_email)=
(select customer_stage.first_name, customer_stage.last_name
,customer_stage.do_not_email
from customer_stage
where customer_stage.customer_key = customer.customer_key
and customer_stage.store_key = customer.store_key);
As you are saying the CUSTOMER table has more than a million, use the ORACLE optimizer execution path trick, CHOOSE. Then the ORACLE optimizer chooses the better path it considers to execute it smarter and quicker. Also make sure the CUSTOMER and CUSTOMER_STAGE tables each have a primary key (probably CUSTOMER_KEY and/or STORE_KEY) and they are indexed properly (like not indexing on a key which is allowed to take nulls) on them. Make sure both the tables are indexed on CUSTOMER_KEY and STORE_KEY seperately or composite, and the CUSTOMER_KEY and STORE_KEY are NOT NULL keys and unique in their respective tables. If they are not indexed on these keys and since these keys are main parts of condition checking for rows retrieval, you better to index the tables on these keys. Then try to give them as a clue to the ORACLE optimizer and run it.
update customer
set (first_name, last_name, do_not_email)=
(select /*+ CHOOSE */ customer_stage.first_name, customer_stage.last_name
,customer_stage.do_not_email
from customer_stage
where customer_stage.customer_key = customer.customer_key
and customer_stage.store_key = customer.store_key);
/*+CHOOSE*/ is a clue to ORACLE optimizer to choose the better execution path either COST based (for larger table) or RULE based (query order based).
Try also with another cluse /*+INDEX(customer_key_index_name,store_key_index_name) */ in place of CHOOSE in the above query. Try all and compare to determine which is better for your query.
You can also give a clue in UPDATE command I guess. I am not sure, you check it up. It would just look like:
update /*+ CHOOSE */ customer
set (first_name, last_name, do_not_email)=
(select /*+ INDEX(index_names) */ customer_stage.first_name, customer_stage.last_name
,customer_stage.do_not_email
from customer_stage
where customer_stage.customer_key = customer.customer_key
and customer_stage.store_key = customer.store_key);
I would be very happy to help you my level best if you still have a problem with your query. Let me know.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
