Home » SQL & PL/SQL » SQL & PL/SQL » Request Size
Request Size [message #268084] Mon, 17 September 2007 08:46 Go to next message
waveblue
Messages: 22
Registered: December 2004
Junior Member
Hello,

I have a request like this:

insert into table (col1, col2,..coln)
select coll1, coll2...
from table
where ...


I would to known space usage of the SELECT.

Have you an idea of the solution?

thanks a lot,

have a nice day.
Re: Request Size [message #268085 is a reply to message #268084] Mon, 17 September 2007 08:47 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What in the world do you mean by space usage?

data ? Memory ? for the statement ? for the physical data ?
Re: Request Size [message #268090 is a reply to message #268085] Mon, 17 September 2007 08:57 Go to previous messageGo to next message
waveblue
Messages: 22
Registered: December 2004
Junior Member
Sorry , i wasn't explicit.

I mean i would like to know space usage that will be used by the transaction to sort the result in tablepace TEMP .


Re: Request Size [message #268096 is a reply to message #268090] Mon, 17 September 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want Oracle sorts something if you don't give an "order by" or "group by"?
(OK it can use it for hash/merge join but this has nothing special to do with insert)

Regards
Michel
Re: Request Size [message #268101 is a reply to message #268090] Mon, 17 September 2007 09:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
waveblue wrote on Mon, 17 September 2007 14:57

I mean i would like to know space usage that will be used by the transaction to sort the result in tablepace TEMP .




Based on the snippet of code that you supplied. I would say that it probably wouldn't use any space since there is (as mentioned by Michel) no order by, group by or even join detailed in your code snippet. What manual are you getting this question from?
Re: Request Size [message #268117 is a reply to message #268101] Mon, 17 September 2007 10:24 Go to previous messageGo to next message
waveblue
Messages: 22
Registered: December 2004
Junior Member
I ask this question because there is a transaction which failed because with this error:

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Re: Request Size [message #268125 is a reply to message #268117] Mon, 17 September 2007 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But this is not this one.

Regards
Michel
Re: Request Size [message #268130 is a reply to message #268125] Mon, 17 September 2007 12:23 Go to previous messageGo to next message
waveblue
Messages: 22
Registered: December 2004
Junior Member
Of course , this is this transaction.

my transaction:
insert into table (col1, col2,..coln)
select coll1, coll2...
from table
where ...
minus
select coll1, coll2...
from table
where ...


I think minus must do a sort.
Re: Request Size [message #268131 is a reply to message #268130] Mon, 17 September 2007 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why don't you post the real question?
Why don't you post your Oracle version?
Why don't you follow the guidelines?

Regards
Michel
Re: Request Size [message #268133 is a reply to message #268130] Mon, 17 September 2007 12:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
waveblue wrote on Mon, 17 September 2007 18:23
Of course , this is this transaction.

my transaction:
insert into table (col1, col2,..coln)
select coll1, coll2...
from table
where ...
minus
select coll1, coll2...
from table
where ...


I think minus must do a sort.

No it's not. If this was your transaction then it would fail because of syntax errors (... is not valid sql)
Re: Request Size [message #268135 is a reply to message #268131] Mon, 17 September 2007 12:59 Go to previous messageGo to next message
waveblue
Messages: 22
Registered: December 2004
Junior Member
thanks Michel Cadot,

but I think it's not necessary to respond me by this kind of answer.
So why don't you post the real question?
Why don't you post your Oracle version?
Why don't you follow the guidelines?


I 'm looking for help but it seems to be too hard for you.

Maybe i forgot some information but it's not justify


have a nice day,

Re: Request Size [message #268137 is a reply to message #268135] Mon, 17 September 2007 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Maybe i forgot some information but it's not justify

And maybe you still do so it is justify.

Regards
Michel
Re: Request Size [message #268141 is a reply to message #268084] Mon, 17 September 2007 14:23 Go to previous messageGo to next message
waveblue
Messages: 22
Registered: December 2004
Junior Member
whaoooo

your advices are more and more effective
Re: Request Size [message #268144 is a reply to message #268084] Mon, 17 September 2007 14:39 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You expect us to read your mind ?

We DON'T know your system.
We DON'T have access to your database.
You REFUSE to include complete information.

If you want a solution, post all of the pertinent information properly formatted or YOYO (Your on your own)
Re: Request Size [message #268146 is a reply to message #268141] Mon, 17 September 2007 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
waveblue wrote on Mon, 17 September 2007 21:23
whaoooo

your advices are more and more effective


Always for those who want them.

Regards
Michel

[Updated on: Mon, 17 September 2007 14:40]

Report message to a moderator

Re: Request Size [message #268190 is a reply to message #268117] Mon, 17 September 2007 22:39 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
waveblue wrote on Mon, 17 September 2007 10:24
I ask this question because there is a transaction which failed because with this error:

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP




ORA-01652 unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent for temporary segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one 
or more files to the tablespace indicated.

Re: Request Size [message #268233 is a reply to message #268190] Tue, 18 September 2007 00:42 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you even bother to read the complete thread?
The original poster is well aware of the meaning of the error-message. That is why he asked about the amount of temp-space his statement would take.
A simple copy-paste of the explanation of the occurred error will not help a bit.

@waveblue: suppose you went to your garagist saying that your car makes a funny noise. Then, after careful examination he cannot find anything, so you tell him, "Well, actually this only happens when I drive my 200+ kg mother in law and we drive very bumpy roads"
Don't you think he will be not-so-pleased you did not tell him in the first place?
Previous Topic: Unicode support in the where clause of DELETE statement
Next Topic: Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword
Goto Forum:
  


Current Time: Mon Dec 05 05:03:56 CST 2016

Total time taken to generate the page: 0.12168 seconds