Re: Writing efficient SQL ?

mosty not true.

welcome to the real world. you can tune till your blue but if the db is not normalized, and if your queries are not optimized, you dead.

you can get an exectution map for any sql in sql*plus or sql*dba.

--

In article <4bhc6k$5jt_at_news.Belgium.EU.net>, Andre Vergison
<avergison_at_infosoft.be> wrote:



> Instance tuning (db_block_buffers, shared pool size, spreading data files 


> across multiple disks, etc, etc) is important.

> But we have experienced that writing SQL statements in the right way is 

> at least as important as instance tuning is.

> 

> Writing efficient SQL is not evident, especially when joins and selects 

> with sub-selects show up.

> We have spent days examining our existing applications and discovered 

> queries that would consume tens of seconds of cpu time and thousands of 

> disk blocks per run. After re-arranging them they would return the same 

> result sometimes 10 times faster, with much less disk i/o. We did this 

> not only by applying know techniques such as adding indexes. E.g., 

> sometimes we had to add an extra table in a join, in order to force the 

> optimizer to follow a certain path instead of "hoping for the best".

> 

> Oracle does document the mechanics of the execution of SQL - the 

> optimizer - fairly well, altough we have found that much was still left 

> to trial and error. (server 7.2)

> 

> Does anyone know about specialized books or papers on this topic (or 

> maybe offer services in this domain) ? Especially good stuff on the cost 

> based optimizer would be welcome.

> 

> thanks

> andre vergison