Home » SQL & PL/SQL » SQL & PL/SQL » Insert hanging
Insert hanging [message #21789] Thu, 29 August 2002 19:46 Go to next message
krishna
Messages: 141
Registered: October 1998
Senior Member
This insert statement is hanging. What could be the problem.

INSERT INTO REJMIGVAS(esn_sim,
phone_n_,prodno, prodesc_complang,prod_trial, prod_start_trial,prod_end_trial, PROD_actIVATION)
( SELECT SUBSTR(trim(esn_sim),7),
trim(phone_n_),
trim(prodno),
trim(prodesc_complang),
trim(prod_trial),
trim(prod_start_trial),
trim(prod_end_trial),
trim(PROD_actIVATION)
FROM MIGVAS where (SUBSTR(trim(esn_sim),7), PHONE_N_ ) not in
(select n_simnumber,N_MOBILENUMBER from simmobile )
OR prodno not in (select V_STOCKCODE from itemmaster ));

Simmobile: 158597 rows
Itemmaster:287 records
MIgvas21,41,663
All columns in the where clause are indexed. But since functions are there, i understand indexes will not be used.
What is the solution?

Report message to a moderator

Re: Insert hanging [message #21792 is a reply to message #21789] Thu, 29 August 2002 22:24 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
It's probably not hanging, just taking a long, long time. It's hard to tell from your numbers if the MIGVAS table has 21M or 2M rows, but I would suggest something like:

select ...
  from migvas m
 where not exists (select null
                     from simmobile sm
                    where sm.n_simnumber = substr(trim(m.esn_sim), 7)
                      and sm.n_mobilenumber = m.phone_n_)
    or not exists (select null
                     from itemmaster im
                    where im.v_stockcode = m.prodno);


You will want to make sure there is an index on simmobile(n_simnumber) or simmobile(n_simnumber, n_mobilenumber), and itemmaster(v_stockcode).

Report message to a moderator

Re: Insert hanging [message #21836 is a reply to message #21789] Tue, 03 September 2002 19:12 Go to previous messageGo to next message
Su
Messages: 154
Registered: April 2002
Senior Member
Yes, you are right, since your WHERE clause includes a functions usage in it, it forces a full table scan and prevents the use of an index causing longer process for your query. By looking at your query, I can advise you to use MINUS operator instead of NOT IN. The UNION, INTERSECT and MINUS operators are stronger and faster. Since the combination of records is in heavy amount, the co-related query also causes longer process. So better you use MINUS and EXISTS operators (EXISTS is much better and faster than IN). Try the following and let me know how it works (in either case, better or no use). First you try the SELECT and check how long is it taking comparatively.

SELECT SUBSTR(trim(esn_sim),7),
trim(phone_n_),
trim(prodno),
trim(prodesc_complang),
trim(prod_trial),
trim(prod_start_trial),
trim(prod_end_trial),
trim(PROD_actIVATION)
FROM MIGVAS where (SUBSTR(trim(esn_sim),7), PHONE_N_) =ANY (SELECT SUBSTR(trim(esn_sim),7), PHONE_N_ from MIGVAS
MINUS
select n_simnumber,N_MOBILENUMBER from simmobile)

OR prodno != any (select V_STOCKCODE from itemmaster );

Now see, what it actully supposed to do is, the first part of WHERE condition is to filter out the records from MIGVAS those do not have a match in SIMMOBILE. The MINUS operator subtracts the records of SIMMOBILE from MIGVAS to leave the matched and result out only the unmatched (those do not have a match in SIMMOBILE) records of MIGVAS. That is, the first part of subquery returns the unmatched records of MIGVAS, same as your NOT IN operator checks to see that there is no match in SIMMOBILE. And then your WHERE clause checks to see the currently being processed row is one of the already filtered (unmatched) records, taking less time to do it as the records in subquery already filtered (and only once they are processed for the whole query as it is not a corelated subquery). So it takes comparatively less time. And the final and second part of the query was already simple, but modified to use "not =any" in place of "not in" for faster process. Try it out and let me know how it worked. Good luck :)

Report message to a moderator

Re: Insert hanging [message #21838 is a reply to message #21789] Tue, 03 September 2002 19:15 Go to previous message
Su
Messages: 154
Registered: April 2002
Senior Member
Yes, you are right, since your WHERE clause includes a functions usage in it, it forces a full table scan and prevents the use of an index causing longer process for your query. By looking at your query, I can advise you to use MINUS operator instead of NOT IN. The UNION, INTERSECT and MINUS operators are stronger and faster. Since the combination of records is in heavy amount, the co-related query also causes longer process. So better you use MINUS and EXISTS operators (EXISTS is much better and faster than IN). Try the following and let me know how it works (in either case, better or no use). First you try the SELECT and check how long is it taking comparatively.

SELECT SUBSTR(trim(esn_sim),7),
trim(phone_n_),
trim(prodno),
trim(prodesc_complang),
trim(prod_trial),
trim(prod_start_trial),
trim(prod_end_trial),
trim(PROD_actIVATION)
FROM MIGVAS where (SUBSTR(trim(esn_sim),7), PHONE_N_) =ANY (SELECT SUBSTR(trim(esn_sim),7), PHONE_N_ from MIGVAS
MINUS
select n_simnumber,N_MOBILENUMBER from simmobile)

OR prodno != any (select V_STOCKCODE from itemmaster );

Now see, what it actully supposed to do is, the first part of WHERE condition is to filter out the records from MIGVAS those do not have a match in SIMMOBILE. The MINUS operator subtracts the records of SIMMOBILE from MIGVAS to leave the matched and result out only the unmatched (those do not have a match in SIMMOBILE) records of MIGVAS. That is, the first part of subquery returns the unmatched records of MIGVAS, same as your NOT IN operator checks to see that there is no match in SIMMOBILE. And then your WHERE clause checks to see the one currently being processed row is one of the already filtered (unmatched) records, taking less time to do it as the records in subquery already filtered (and only once they are processed for the whole query as it is not a corelated subquery). So it takes comparatively less time. And the final and second part of the query was already simple, but modified to use "not =any" in place of "not in" for faster process. Try it out and let me know how it worked. Good luck :)

Report message to a moderator

Previous Topic: Multiple selects for a report.
Next Topic: Creating user and inserting data
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 20:38:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.