Need URGENT HELP on PL/SQL

From: Steven Chin <steven.chin_at_pointinfo.com>
Date: Sat, 17 Apr 1999 17:03:42 +0800
Message-ID: <37184E6E.52E8A078@pointinfo.com>

Hi,

I need your expert advise on the following SQLs as I'm pretty new to SQL. What really concerned me is the time taken to return the result of the query. By looking at the explain plan, I realised that the execution is doing a full table scan on the account and campaign table each with 1 million records. How could I structure these query to use an index? Appreciate all the help that I could get. Thanks a million!

SELECT /*+ FIRST_ROWS */
PRIMARYKEY,Account.Name,Account.PreferredName,Account.ID,Account.Type,Account.Source,Account.ContactKey

from Account where OIC = 'FE' AND SYSIND = '1' OR primarykey in (Select Accountkey from Lead Where Office = '0000000S0' AND SYSIND = '1')
>select operation, object_name, options from sys.plan_table;

OPERATION                      OBJECT_NAME                    OPTIONS
------------------------------ ------------------------------
------------------------------

SELECT STATEMENT
FILTER

TABLE ACCESS                   ACCOUNT                        FULL
TABLE ACCESS                   LEAD                                 BY
ROWID
INDEX                                   LD_OFFICE

RANGE SCAN
5 rows selected.

SELECT /*+ FIRST_ROWS */
PRIMARYKEY,AccountKey,Priority,Status,ContactKey from Lead where Office = 'OUB0000000S0' AND ForeignKey in (select PrimaryKey from campaign

                   where (to_char(startdate,'YYYYMMDD') <= '19990414'
                   AND to_char(enddate,'YYYYMMDD') >= '19990414')
                   AND ID not like 'SYS%')
AND ((CreatedBy = 'BATCHML'
      AND ForeignKey in (select campaignkey
                         from AssignedTarget
                         where InfoKey = '0000000S0'
                         AND DistrInd = '1'
                         )
      )
      OR (CreatedBy <> 'BATCHML')

    )
AND SYSIND = '1'
> select operation, object_name, options from sys.plan_table;

OPERATION                      OBJECT_NAME                    OPTIONS
------------------------------ ------------------------------
------------------------------

SELECT STATEMENT
FILTER
NESTED LOOPS

TABLE ACCESS                   CAMPAIGN                       FULL
TABLE ACCESS                   LEAD                           BY ROWID
AND-EQUAL
INDEX                          LD_FOREIGNKEY                  RANGE SCAN

INDEX                          LD_OFFICE                      RANGE SCAN

TABLE ACCESS                   ASSIGNEDTARGET                 BY ROWID
INDEX                          AT_INFOKEY_DISTRIND            RANGE SCAN

10 rows selected.

Regards,
Steven Chin Received on Sat Apr 17 1999 - 04:03:42 CDT