Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Miguel.

Asked: October 09, 2006 - 10:22 am UTC

Last updated: September 21, 2010 - 4:04 pm UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Mr. Tom. How are you? I hope good.

I must make a new table based in the data of, by example, three more tables.

I have not experience in Oracle but I have some experience in SQL (I can't say "a lot" but more or less), then I think that I must to use a single query, somthing like

create table t1 as select z.*, x.*
from t2 zz
left join xx on zz.field1 = xx.field1

But my boss said that is very most effective to use a cursor(!!!) like

for cursor in (select * from t1)
loop

select the_fields into dummy
from t2 where t2.fiels1 = cursor.field1;

end loop;

Well, I repeat, I do not know Oracle, finally is SQL but may be possible that in Oracle cursors works better than queries. Then :CURSORS OR QUERIES?

I beg your pardon if the question is too simple.

Thanks a lot.

and Tom said...

SQL by the way is not a product name - I think you meant to say "I have experience using MS SQLServer as a database", they did not invent, own, innovate SQL - they bought their server from Sybase - they are "not SQL", they are MS SQLServer.

Your boss needs to go back to school I would say. That is the single most INEFFECIENT approach to loading data.

I have a name for that type of approach, I call it:

SLOW by SLOW processing - avoid it.


Get a new mentor, you'll be better for it long term.


I write this in my books:

I have a pretty simple mantra when it comes to developing database software:

 You should do it in a single SQL statement if at all possible.
 If you cannot do it in a single SQL Statement, then do it in PL/SQL.
 If you cannot do it in PL/SQL, try a Java Stored Procedure.
 If you cannot do it in Java, do it in a C external procedure.
 If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…


Rating

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Miguel De Belliz, October 09, 2006 - 11:27 am UTC


Miguel De Belliz, October 09, 2006 - 11:40 am UTC


Clarity required...

A reader, October 10, 2006 - 3:50 pm UTC

Hey Tome...

can you explain what exactly do you want to convey... ?
What I felt from ur answer is we should go for PL/SQL, JAVA and bla bla but should not use cursors.

Is it ?

2nd thing... IF we dont use cursors then why cursors have been invented and where those are used for ?

Cursors are easy to implement, person is not required to have knowledge of Java/C...

Correct...?

Pls. execuse me if I wrote / ask something silly...

Regards,
Star

Tom Kyte
October 10, 2006 - 8:20 pm UTC

I'm saying:

IF you can do it in a single sql statement, by all MEANS DO IT THAT WAY. do not waste time, energy, cpu cycles writing procedural code that will run slower than regular SQL.

If you cannot do it in a single sql statement - then use plsql....

cursors were invented for when you cannot do it in a single sql statement - read the "mantra" above, it does NOT say "thou shall never use procedural code", it says "only do procedural code when thou MUST"

Correct.... But One minute Pls.!!!

Star Nirav, October 10, 2006 - 8:28 pm UTC

Dear TK,

Agreed but let me ask you one question wrt Hogging Queries.

I read somewhere in oracle site that for performance, we need to kill queries from OS (kill command in AIX).

My question is, Sir, if a query is required some CPU resource, it will take and once it completed, will release the CPU. Correct...? So why should we kill from OS level..? Cant we tune from oracle itself... ?

And to find out the most expensive queries, what things we need to keep in mind and what to do to avoid completely in future... ? Can we restrict that user from OEM ?

Can you share the script or link with me pleaseeeee ???

Sometimes, we also need to see the execution plan and in that we need to check that whether FTS is a culprit here or not... ?

Correct me if i am wrong.... Thanks

Tom Kyte
October 10, 2006 - 8:33 pm UTC

you did not read that on this oracle site, please don't say "i read somewhere out there on the internet (and if it is published it must be true)..."

If you think you read something somewhere, point to it and let us see it in context, you may have misread or misinterpreted what it was saying.

I would NOT recommend killing queries, no.

Yeah... May be correct.

Star Nirav, October 10, 2006 - 8:43 pm UTC

Hey TK,

>> you may have misread or misinterpreted what it was saying. <<

Seems that correct, as I have not made that as bookmark and could not remember that whole link. Anyways, forget that part please and tell me if you not recommend to killing queries then what should I do to stop the queries which hogging my servers cpu. Dev. team is saying that they are tuning those queries at their best level, but still it is chocking the cpu and mine HDD (I am having SAN).

Waiting for your anticipation...

Regards,


Tom Kyte
October 11, 2006 - 8:00 am UTC

You can use the resource manager (see admin guide) to partition out the resources of the machine when the server gets near 100% utilized. For example, you could put a policy in place that says "if your query is going to take more than one minute, we won't even start it" or "if machine is getting maxed out on cpu, you will only get 20% of the cpu - leaving 80% for the rest of us" and so on

You can use profiles (see admin guide) to limit the aggregate amount of resources a user or call to the database can use - that is, you can limit cpu per session, cpu per call, IO's and so on.



Reinventing the wheel?

Miguel De Belliz, October 11, 2006 - 9:07 am UTC

I repeat that I do not know about oracle and this was the reason of mi question to Mr. Kyte.

But is basic that using data bases we must to tray to resolve the problem in the "server side".

When I submit a query I give the desicion to resolve it to the server because the server going to resolve the problem, in general, in the correct way. ¿The cost is greatest than using cursors? I do not know but is the best.

When I use ADO connection to access to a data base. ¿Is most effective to use a store procedure in the server side that return a result or, in other case, to submit a query for each record? The answer is simple.

And ¿Why the cursor was invented? In the same way that was invented the shovel and the Caterpillar. If I going to remove a hill can use a shovel, is cheap but mean a lot of work and time.

Mr. Kyte, based in your response I make two procedures. One query based and the other based in cursor. The first was a lot faster than the query. Is enough to me.

Thanks a lot again.


SQL approach

Alex, October 16, 2006 - 3:11 am UTC

Speaking about boss. In one of my project I have got the dispute as well with my boss about using cursors while loading tables. He had similuar approach and wants me to follow it. But I changed it to single SQL statements. In fact, it was a performance booster from 10-15 hours to some minutes, but it cost me many frustrating disputes with my boss during developing.

Tom Kyte
October 16, 2006 - 7:50 am UTC

just use the term "slow by slow" in the future - it really makes the point....


Why do you want to do "slow by slow" processing, databases were born to do set operations...

Tom's Mantra and Nothing but the Mantra

Robert, October 19, 2006 - 10:00 am UTC


>> I have a pretty simple mantra when it comes to developing database software:

ahh been loving that very mantra of yours since 5 years back...

Miguel...time to fire that boss there's always greener pasture. You ain't going far with that guy.

The anti-database creed

Duke Ganote, November 01, 2006 - 8:54 am UTC

This is the creed I've heard for years from some database managers and DBAs:

* No use of innovative features in the database means less blame for the database team when the application encounters bugs.
* Minimizing work allocated to the database means less blame when the application fails to perform.

THEREFORE:
1. Only keyed reads of tables are permitted.
2. The application code does all joins, ESPECIALLY outer joins.
3. The application code must be out of the database to make the database clean and fast.
4. Never use any feature that hasn't been proven since Tom Kyte started working with Oracle version 5.1.5c ($99 single user version for DOS on 360 KB floppy disks).

"You can always tell the pioneers: they're the ones with arrows in their backs."

And that's a fact.

Tom Kyte
November 01, 2006 - 8:55 am UTC

wow, that is like "backwards day" isn't it.

In order to minimize work on the database, you have to use database features. The less of the feature set in general you use, the more work the database has to do overall.



The anti-database creed additions...

Jmv, November 01, 2006 - 10:09 am UTC

4a) Foreign keys are only defined within the application, none within the database. (not sure about availability in 5.1.5c though)
4b) Dimensions same as 5 above.
5) The database is not broke, so don't patch or upgrade it.
6) 1 schema per db
6a) 1 db per instance
6b) 1 set of Oracle executables per instance
6c) Multiple instances per box
7) Test backups or recovery procedures...why?


Tom Kyte
November 01, 2006 - 11:15 am UTC

you just listed half of my database worst practices material!

The anti-database creed

Duke Ganote, November 01, 2006 - 10:39 am UTC

And of course the infamous:

8. Keep your DBHR close to 100% to guarantee good performance!
9. Employ a squad of expensive DBAs to monitor and protect the database from any innovative usage.

The anti-database creed

Tim, November 01, 2006 - 1:16 pm UTC

Tried to resist - couldn't. Had to join in.
:P

10) Cold backups are the only way to go.
11) Bind variables? Waste of developer time.
12) Sequence objects? Why bother - select max(id) works great.


Creed of the worst practitioners

Duke Ganote, November 01, 2006 - 1:29 pm UTC

The only other place I find that phrase "worst practices" at this point is:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:288016031632#11581508742910 <code>

More for the anti-database creed....

Jmv, November 01, 2006 - 1:53 pm UTC

Oh the temptation....restraint...it is overwhelming....

13) Everything in 1 big table
13a) columns like description1, userdefined1, and filler1... all char(2000) (where 1 is incremented and the resulting column added upon "need")
13b) each column indexed
13c) non partitioned
14) Trunc and reload table during every batch window



Tom Kyte
November 01, 2006 - 6:18 pm UTC

13 - hit in the presenation (along with 11 from above of course)
13b - in the "you probably do not need configuration management section"


are you tryin I shoot me????

Miguel De Belliz, November 01, 2006 - 2:02 pm UTC

you destroyed my illusions men!

I am working in Argentina thinking that in other place of the world there are people who work ideally!
I ask Tom about queries versus cursors and OK! Queries are better!
So what! In all the wide world there are DBAs saying :

Why do you like to use MY DATABASE? Take some data and go to Access!
Do you like to create transitory tables? Are you crazy? NO. You have permissions to use select and is enough!
Indexes???? why do you need another indexes?

Is this true?

Damn world already nothing has sense.

Regards.






... more creed

Leo Mannhart, November 01, 2006 - 3:57 pm UTC

15) for sure there are silver bullets but only a top consultant knows them so hurry to hire one
16) if you dig deep enough you'll find this famous fast=true initialization parameter (but this is not a normal underscore parameter as everybody knows them now; I bet it is one of this famous double underscore-dollar parameters, probably __$fast=true, but nobody will really tell it once he/she knows it). This must be true by the fact that Jonathan Lewis has one of these new SQL*Plus binary where, after he enters the command "go faster", a query will runfaster by a order of magnitude.

;-)

Anti-Database Creed contributions for Database Worst practices...

Jmv, November 02, 2006 - 8:58 am UTC

Tom,

Thanks for the link!

I did not know you had something like this going already, should have figured as much though.

I really have to get better at checking both AskTom and your blog. AskTom is available easily at work, while your blog was blocked. A link to the presentation would also be helpful.

Perhaps I may yet have a contribution or two.



Tom Kyte
November 02, 2006 - 9:08 am UTC

click on my files tab above and sort by created by from big to small, it is a recent upload.

Database Worst Pratices....

Jmv, November 02, 2006 - 12:34 pm UTC

The slide on "...just whip things together..." sadly speaks volumes for only being 3 images!!

Thanks Tom!


Creed vs mere list of Worst Practices

Duke Ganote, November 03, 2006 - 1:59 pm UTC

I think the best part of your "worst practices" presentation
</code> http://tkyte.blogspot.com/2006/10/this-site-owes-me-new-monitor.html <code>
is exactly what I was trying to capture in the Creed: Statements of WHY the 'worst practices' happen, the thinking behind them!

WHY just use VARCHAR2 instead of other datatypes? "It's so much easier"

WHY just code literals into SQL strings? "It's so much easier"

Everyone is pressed for completing their "piece of the pie", yet gotta be 'blameless' when the inevitable glitches show up. Oh, maybe I'm suboptimizing the overall system, but as long as my piece functions and got done on time....

Tom Kyte
November 04, 2006 - 12:09 pm UTC

but the problem is (and this I go through in the talk) that the premise that they are easier is provably FALSE.

It is harder to NOT bind
It is harder to use varchar2 for everything

because of all of the inevitable downstream problems they create.

Their "piece of the pie"...

Jmv, November 09, 2006 - 11:07 am UTC

Tom,

I agree, though not everybody is capable of stepping back from their "piece of the pie", and looking at the bigger picture to see those "downstream problems".

As may have been stated in other threads, some people are content to say, for a variety of reasons, things like:
- "Get it done now, to 'proof' it out for..." from a developer perhaps
- "Add it to the issues list.." from the tech lead
- "Redress that during warranty..." from the team lead
- "That can be an enhancement request..." project manager
- "Fix that in the next update/fixpack/version..." product manager
- "Performance of xxxx bites...." end user
- "Wow nice code..." Support person
- "Why is it running this way..." DBA

The "Fix" to this is two part in my mind. The first is education, such as this site, wherein a problem is posed, examined, solutions provided and address some of the "downstream problems". Such that those "downstream problems" can be avoided by knowing the "why" of it.

The second is all the viewers out there reading it, commenting on it and learning from it, trying to make their lives, environment and/or company better for knowing the "why" of it.

It should go without saying, but is worth repeating, that Tom does an excellent job of this.

Here's to wishing I could have seen the live version of the presentation!




your Mantra

A reader, July 09, 2010 - 11:03 am UTC


Hi Tom,

I want to READ the first line in your mantra to as shown below:

->You should do it in a single SQL statement in pl/sql (in a pl/sql block/procedure/function) if at all possible with out using any Procedural logic/language features.

If you cannot do it in a single SQL statement, do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java stored procedure.
If you cannot do it in Java, do it in a C external procedure.
If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.

Is it correct to say ? because while developing a software - we should always put the SQL in pl/sql only - but we should try to avoid the procedural logic. Can you please correct me if I am wrong ?


Tom Kyte
July 09, 2010 - 4:49 pm UTC

I like the sql in a stored procedure because then I can find it, fix it, make it better. If you (the developer that doesn't like sql in the first place, that finds it a burden) hide it in your code, I cannot find it, fix it.

So, if in plsql, I can at least see it.

Then, try to do as much as you can in sql, do not for example do this:


for x in ( select * from t1 ) loop
insert into t2 values x;
end loop;


Just do this:

insert into t2 select * from t1;


don't do procedurally what we can efficiently (more efficiently actually) do in a single sql statement

that is what I'm trying to say there.

Any case where pl/sql is faster than SQL

Sutaria Chirayu, July 09, 2010 - 7:36 pm UTC

Hi Tom,
Just curious.. have you ever encountered a scenario.. or could there be a scenario where PL/SQL could be more faster than SQL.
Tom Kyte
July 19, 2010 - 7:58 am UTC

yes, there are rare cases.

Remember,

Never say Never
Never say Always
I Always say....



In almost all cases - doing as much as you can in a single sql statement will outperform (greatly) the equivalent slow by slow procedural code.

There are of course rare exceptions to this - very rare and generally very unique and somewhat complex (so it is not straightforward to give you a cookbook that says "in this case, do this instead of sql")

re: Any case where pl/sql is faster than SQL

Duke Ganote, July 09, 2010 - 8:59 pm UTC

Any? Maybe due to wonky statistics so the optimizer choses a woefully suboptimal plan. Basically you can only beat the optimizer when you know more than it does. Very few people consistently win at blackjack; almost all lose. Think of the optimizer as "the house"; it's hard to beat!

When explicit cursors are necessary ?

A reader, July 12, 2010 - 7:06 am UTC

Hi Tom,

I have been your fan since long time.

Normally I also prefer implicit cursor; it seems that there may be rare situations where explicit cursors can be required.

The reason which drawn me to this question was a question asked by a team member. That was - “When explicit cursors are required?” OR “When should we go for an explicit cursor?”

I gave few answers –
1 ) To pass data through cursor via an out parameter (SYS_REFCURSOR).
2 ) To perform – Select for update …
3 ) To write a file, which can be used by other interface systems.

I could not go on further. Can you give some information on – when exactly we need explicit cursors in real time applications?

BT

Tom Kyte
July 19, 2010 - 10:29 am UTC

you need to use an explicit cursor when:

a) you need to use a ref cursor, ref cursors have to be explicit

b) you want to array process data using bulk collect (when the bulk collecting done by implicit cursors is not sufficient)


for example of (b):


for x in ( select * from t ) 
loop
   utl_file.put_line( x.... );
end;


that code DOES NOT need to array fetch, we already do it for you (10g and above - 100 rows at a time). The above code should use an implicit cursor as I have shown. However:


for x in (select * from t) /* this is bad code */
loop
     .... process row ....
     update t set ... where t.pk = X.PK;
end loop;



that code would want to use an EXPLICIT cursor so we could array fetch 100 rows - process 100 rows - array update 100 rows, eg:
open c; -- for select * from t
loop
   fetch c bulk collect into l_data_array limit 100;
   l_eof := sql%notfound;
   for i in 1 .. l_data_array.count 
   loop
       ... process ...
   end loop;
   forall i in 1 .. l_data_array.count update t set ... 
                                        where t.pk = l_data_array.pk(i);
   exit when l_eof;
end loop;
close c;




and that is it, select for update shouldn't be in your list - nor should "write a file"

Where current of... ?

Kim Berg Hansen, July 20, 2010 - 6:26 am UTC

Hi, Tom

You answered the previous commentary, that "select for update" should not be on his list. And you had an example of bad practice of updating row-by-row in a for loop.

Does that mean that you generally disapprove of the "where current of" syntax?

I would guess that is why the previous commenter believed a "select for update" needed an explicit cursor since "where current of" is not possible (AFAIK) with an implicit cursor.

Anyway I tend to agree, just curious to confirm whether you like "where current of" or not :-)

Tom Kyte
July 23, 2010 - 7:05 am UTC

... Does that mean that you generally disapprove of the "where current of" syntax? ..

yes, for any update of any size other then a single row or two.

it (where current of) would make sense for a row or two, but not for a large bulk update.


they asked about "for update", which in general is used to lock a stable set of rows and MAY or MAY NOT be used with "where current of".

ops$tkyte%ORA10GR2> create table t ( x int , y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for x in ( select rowid rid, x, y from t for update )
  3          loop
  4                  update t set y = 2 where rowid = x.rid;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.



is identical to "where current of", where current of is syntactic sugar for "where rowid = ?" - in fact, if you trace a where current of, you'll see we really just added rowid to your select and use it in the where clause.

your mantra

A reader, September 21, 2010 - 6:59 am UTC

Hi Tom,

Can we use your mantra in our blog ?

http://bix-erusadla.blogspot.com/
Tom Kyte
September 21, 2010 - 4:04 pm UTC

sure

question

devu mani, April 23, 2014 - 5:28 am UTC

what is a full form of cusror?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library