Re: Tablespace, Diskspace or sql abuse..

"nikey11" <member31783_at_dbforums.com> wrote in message news:3037651.1056466812_at_dbforums.com...
>
> First of all, I'd like to say 10x to Arcangelo, Norm And Nuno. your
> swift replies have been most helpfull in confronting my evil dba...
> I've proven to him that he was wrong by his assumption that my query is
> not logical by running it on a smaller table.
>
> My question for you now is this:
> Have I been unproffesional by creating a query that combines a table of
> 1358453 rows 3 times to itself?

Not necessarily. If you have just written

select a.*,b.*,c.*
from large_tab a,large_tab b,large_tab c;

Then this wouldn't be the best piece of code ever seen. On the other hand we might routinely include the same large table 3 times in the same query. Access would nearly always be via indexed columns and return small resultsets (in theory - in practice Accountants write the sql sometimes). Self Joins aren't in and of themselves a bad thing, but they need to be part of a sensible design and anticipated access paths.

> should I considered a different approach?

You should consider EXPLAINing all SQL that takes longer than a cutoff period (say 10 seconds) to return results and see if the SQL is sufficiently tuned. There are 3 things if you take this approach seriously that you need to know.

1. It can be hard :(
2. There are tools and your DBA to help you.
3. If you consider the efficiency of your SQL and not just wether it returns the right results your DBA will love you. Or at least not hit you often.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************