Re: SQL Tuning

The version of Oracle is 7.3.4.0.1

Below is an access path for the statement in question. Also, could you tell me in this example the order in which Oracle compares columns in the where clause. Thanks a lot for your help.

Optimiser mode: ANALYZED
Optimiser mode: CHOOSE
  SELECT STATEMENT
    SORT ORDER BY

      NESTED LOOPS
        NESTED LOOPS OUTER
          NESTED LOOPS OUTER
            MERGE JOIN
              SORT JOIN
                FILTER
                  MERGE JOIN OUTER
                    SORT JOIN
                      NESTED LOOPS
                        MERGE JOIN
                          SORT JOIN
                            TABLE ACCESS FULL TABLE_MOD_LEVEL
                          SORT JOIN
                            TABLE ACCESS FULL TABLE_PART_NUM
                        TABLE ACCESS BY ROWID TABLE_SITE_PART
                          BITMAP CONVERSION TO ROWIDS
                            BITMAP MINUS
                              BITMAP INDEX SINGLE VALUE
IND_SITE_PART2PART_INFO
                              BITMAP INDEX SINGLE VALUE IND_LEVEL_TO_BIN
                    SORT JOIN
                      TABLE ACCESS FULL TABLE_PRODUCTBIN
              SORT JOIN
                TABLE ACCESS FULL TABLE_SITE
            INDEX UNIQUE SCAN VENDOR_OBJINDEX
          INDEX UNIQUE SCAN VENDOR_OBJINDEX
        INDEX UNIQUE SCAN ADDRESS_OBJINDEX

Dave Wotton wrote in message <71rrag$hgj$1_at_dns.camcnty.gov.uk>...
>>Roman Gelfand <rgelfand_at_masmid.com> wrote:
>>I am trying to tune an sql statement by manipulating initialization
>>parameters, indexes... etc. These SQL statements are vendor supplied and
I
>>am not at liberty to change them.
>
>Hi,
>
>could you post the explain plan?
>
>What version of Oracle are you running?. Which optimiser?
>
>I think you may be stuck if you are not at liberty to change the SQL.
>Changing parameters and indexes aren't always sufficient to fix all
>performance problems.
>
>If you're using the Cost based analyser, you may need to add hints to
>your query ( does this constitute changing it? ). It may turn out that
>the view definition needs to be changed in order to speed it up. Does this
>constitute "changing the SQL?". If you're using the rule based optimiser
>you'll almost certainly have to tweak the query or the view.
>
>Dave.
>--
>Remove the no-spam bit from my email address to reply.
Received on Thu Nov 05 1998 - 10:53:33 CST