Home » SQL & PL/SQL » SQL & PL/SQL » INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i
INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187005] Thu, 10 August 2006 08:53 Go to next message
tali62
Messages: 4
Registered: August 2006
Junior Member
Hi Guys,
I'm stuck with a strange issue. Look at this harmless piece of code. It works in 8i but doesn't work in 10g. When you run this stmt in 10g, the query hangs and never finishes.

INSERT INTO table1
(col1, col2, col3)
SELECT seq.NEXTVAL, c2, c3
FROM view1 v1
WHERE v1.cx = 'abc'

However, if I start using a table in place of view1, this insert starts working. Any help is appreciated.

Following stmt with table in place of view1 works.

INSERT INTO table1
(col1, col2, col3)
SELECT seq.NEXTVAL, c2, c3
FROM table2 t2
WHERE t2.cx = 'abc'



Thanks,
Tauqueer.
Re: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187013 is a reply to message #187005] Thu, 10 August 2006 09:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you try the insert using the view more than once? Could it be you were blocked by someone locking table1?
Could it be the server was busy?
"it never finishes" How long did you wait?
What does the select from the view (without the insert-part) do?

In short: it should work.
Re: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187014 is a reply to message #187005] Thu, 10 August 2006 09:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The first insert statement is very unlikely to be hung.
Can you post an Explain Plan for it.

Also, what is the SQL behind the view V1?

And are your stats up to date?
Re: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187016 is a reply to message #187005] Thu, 10 August 2006 09:26 Go to previous messageGo to next message
tali62
Messages: 4
Registered: August 2006
Junior Member
Did you try the insert using the view more than once?
I tried it many times. Tried on 3 different db instances too.

Could it be you were blocked by someone locking table1?
This doesn't seem to be the case as I tried it many times on different instances. how can I confirm this.

Could it be the server was busy?
it was just me using the server with this sngle query.

"it never finishes" How long did you wait?
around 45 mins.

What does the select from the view (without the insert-part) do?
it returns 149 records. takes 6-7 seconds to return the recods. however, when i run the select query without any where clause then it takes forever. the longest that ive waited after running select * on this view is 15 mins and then i aborted.


thanks a lot for ur prompt response. if you have 10g easily available, could you please try this out. i can reproduce this problem with any view and the insert starts workin fine the moment i replace view with a table.

Thanks
Tauqueer.
Re: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187021 is a reply to message #187005] Thu, 10 August 2006 09:44 Go to previous messageGo to next message
tali62
Messages: 4
Registered: August 2006
Junior Member
The first insert statement is very unlikely to be hung.
Can you post an Explain Plan for it.
attaching explain plan.

Also, what is the SQL behind the view V1?
Here's the script. pls let me know if you have specific questions abt this view :

CREATE OR REPLACE VIEW view1
(AGGREGATE_CASE_ID, AGGREGATE_ID, CASE_ID)
AS
SELECT RAC.aggregate_case_id, RAC.aggregate_id, RAC.case_id
FROM report_aggregate_case RAC
WHERE RAC.agg_report_sub_type_code_id = 15000
AND NOT EXISTS
(SELECT 'X'
FROM v_r_psur_in_report PIR
WHERE RAC.aggregate_case_id = PIR.aggregate_case_id
AND RAC.aggregate_id = PIR.aggregate_id
AND RAC.case_id = PIR.case_id
)
/

And are your stats up to date?
stats look fine.

what seems strange to me is that the same thing works fine in 8i.

Re: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187024 is a reply to message #187021] Thu, 10 August 2006 09:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> drop table faq
  2  /

Table dropped.

SQL> CREATE TABLE faq
  2  as (select rownum id
  3  	 ,	object_name
  4  	 from	all_objects
  5  	)
  6  /

Table created.

SQL> create view faq_v
  2  as
  3    select id
  4    ,      object_name
  5    ,      to_char(id)||object_name made_up_column
  6    from   faq
  7  /

View created.

SQL> create table faq2
  2  as (select *
  3  	 from	faq_v
  4  	 where	1 = 2
  5  	)
  6  /

Table created.

SQL> create sequence faq_seq
  2  /

Sequence created.

SQL> set timing on
SQL> insert into faq2
  2  ( id
  3  , object_name
  4  , made_up_column
  5  )
  6  select faq_seq.nextval
  7  ,	    object_name
  8  ,	    made_up_column
  9  from   faq_v
 10  /

12082 rows created.

Elapsed: 00:00:00.39

Since you could reproduce with any view, I made it as simple as possible.

Your view is based on another view. The whole operation is just slow. Not hanging!

[Updated on: Thu, 10 August 2006 09:55]

Report message to a moderator

Re: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i [message #187029 is a reply to message #187005] Thu, 10 August 2006 10:03 Go to previous message
tali62
Messages: 4
Registered: August 2006
Junior Member
Frank, thanks for the quick test case. good to know that it's not a generic issue.

Your view is based on another view. The whole operation is just slow. Not hanging!
You may be right here. coz when i run a select * on this view, it takes forever. but the select stmt in the insert query is supposed to return only 149 cases and takes only 6-7 seconds when run seperatly.

any other suggestions for troubleshooting. could writnig this query differently help. like having loop where i'll fetch data from views and then execute simple insert query per record in the loop.
Previous Topic: Invalid month error...
Next Topic: Assigning PL/SQL Table type to Procedure parameter
Goto Forum:
  


Current Time: Sat Dec 03 12:02:01 CST 2016

Total time taken to generate the page: 0.08188 seconds