Re: Efficient SQL

Hi,
Apparently you are using the rule based optimizer that has been always in Oracle, but has been replaced by the cost-based optimizer in Oracle7. The rule based optimizer still exists, but it has been desupported, no new development will take place. To take advantage of the cost-based optimizer you need to ANALYZE your tables. To do this for a complete schema issue exec dbms_utility.analyze_schema('<schemaname>','COMPUTE')

That being said, I will provide a brief comparison between the two optimizer.
The rule based optimizer follows simple heuristics: what are the relationships between tables, what is the primary key, are the foreign keys being indexed etc etc.
If however the optimizer can't decide between several plans, it will, as the statement is being parsed backwards, always consider the last table in a FROM clause as being driving.
General rules
Never use function(indexed_column) = constant, as this will result in a full table scan.
Always use : foreign key column = primary key column, as the reverse might be misinterpreted
Always put the most selective predicate last. Never use (in a join) dept.deptno = constant , always use emp.deptno = constant.
If you can replace a in (select ...) by a exists (select / correlated subquery) you should do so.

The cost based optimizer only looks at statistics. It tries to minimize the number of io's needed to get results. This explains why on small tables you will always see full table scans, as using an index is usually less efficient.

I don't know the O'Reilly book, my current bible is Guy Harrison Oracle SQL High performance tuning. This mainly focuses on the cost based optimizer including parallel query, and contains many case studies.

Hth,

Sybrand Bakker, Oracle DBA

Altitude wrote in message <3764C9E2.96305BBB_at_logica.com>...
>Hi,
>
>We've been looking at improving the efficiency and peformance of some
>Oracle SQL (Both in Pro*C and in triggers). I've found out that for
>SELECT statements, the FROM clause should always have the smallest table
>last (since this is the driving table). Why is this? How does Oracle
>actually parse a SELECT stament and why is this method more efficient?
>My second question is more general ... are there any books which discuss
>the performance and effciency issues with respect to Oracle SQL?
>Nothing to complicated, as I'm only a beginner, but just something which
>will explain to me how to write efficient joins for example.
>
>Any help will be greatly appreciated.
>Thanks in advance.
>
>
Received on Mon Jun 14 1999 - 12:28:15 CDT