Re: SQL performance tuning..

[Jerry Coleman <colemanj@WASHPOST.COM>]

As to the order, in Oracle it depends on the optimizer mode you're using.
If you're using the cost-based optimizer, then it doesn't matter as to the
join order.  In rule-based, it does - you want to put the smallest table
(the one returning the least rows) last.

It doesn't matter the order of the where clause in either case.  It does
matter that you join all possible indexed fields, whether they be unique or
non-unique indexes.  Try to join as may keys as possible.  You can force an
index read sometimes by saying "where fieldx > ' '".

Look in the docs about using explain plan.  It'll tell you what Oracle
does.  If you use Toad, hit CTRL-E and see if it'll explain it for you.

As for courses, I took an Oracle course by satellite a few years ago, and
found it worthwhile.   A performance tuning book by Richard Niemiec is also
pretty good.





                    Rick_Creel@AON
                    CONS.COM              To:     SQR-USERS@list.iex.net
                    Sent by:              cc:
                    "Discussion of        Subject:     SQL performance tuning..
                    SQR, Brio
                    Technology's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    07/17/00 10:39
                    AM
                    Please respond
                    to sqr-users






The SQR I am working on is in a PeopleSoft environment, specifically
PeopleSoft
Projects.
PSoft Ver 7.53
Oracle Ver 8.06
SQR Ver 4.34

I am joining the PROJ_RESOURCE table (which has over 3 million of rows of
data)
  to the PROJ_ACTIVITY table
(which has just over 127,000 rows of data).

For purposes of efficiency, is there a "rule of thumb" as to what sequence
to
specify the table names in the "FROM" clause?
Do you say
FROM PS_PROJ_RESOURCE PR,
      PS_PROJ_ACTIVITY PA

or
FROM PS_PROJ_ACTIVITY PA,
        PS_PROJ_RESOURCE PR

or does it matter?

In the WHERE clause, what about the sequence of the keys for each table,
especially the keys that are
used to join the 2 tables?

The SQR I have works, but it is slow as molasses in the winter!  I need to
fine-tune the queries to make them
as fast and efficient as possible.  I hate to admit it, but I have no clue
how
to optimize SQL code and have never
been given any training in how to optimize code.

Thanks for any help/advice!