Tuning SQL query with views

Hi,

Following the here posted tuning tips I replaced the view fields and view names with the corresponding table fields and table names. I also added the where clause to my statement. Then I tried to create one index on the 'worst' behaving table with just one field that is really accurate and now my explain plan is totally different: I got rid of my full table scan, the number of retrieved rows in the different steps is now acceptable and most important, the statement retrieves the result almost instantly.

Now the new explain plan is:

SELECT STATEMENT Optimizer=CHOOSE
  SORT (ORDER BY)
    NESTED LOOPS

      NESTED LOOPS
        TABLE ACCESS (BY INDEX ROWID) OF PS_PAYMENT_TBL
          INDEX (RANGE SCAN) OF PSZPAYMENT_TBL (NON-UNIQUE)
        TABLE ACCESS (BY INDEX ROWID) OF PS_BANK_ACCT_DEFN
          INDEX (RANGE SCAN) OF PSZBANK_ACCT_DEFN (NON-UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF PS_BANK_ACCT_CPTY
        INDEX (UNIQUE SCAN) OF PS_BANK_ACCT_CPTY (UNIQUE)

whereas the old one was:

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE

  42607     NESTED LOOPS
  42607      TABLE ACCESS (FULL) OF 'PS_BANK_ACCT_CPTY'
  42607      INDEX (UNIQUE SCAN) OF 'PS_BANK_ACCT_DEFN' (UNIQUE)
 132864     TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAYMENT_TBL'
 175471      INDEX (RANGE SCAN) OF 'PSAPAYMENT_TBL' (NON-UNIQUE)

Now I have some other questions:
I always thought that it is important to have the same field order in the indexes as in the where clause. Therefore I had never thought that a new index with only the fourth field from the where clause would be used.
Can I conclude that I was wrong? Is the order in the where clause important for the use of indexes? Is it a bad idea to create indexes with fields that are used in conditions like _<>_, _IN_, _LIKE_, etc.?

Thanks in advance for any help.

Bart.

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 27 2000 - 15:36:09 CST