Re: Need URGENT HELP on PL/SQL

From: CashCloister <CashCloister_at_Yahoo.com>
Date: Sat, 17 Apr 1999 07:59:48 -0500
Message-ID: <ufqd3JNi#GA.213_at_upnetnews03>

Take a look at stored procedures. You probably need to run a function to do the large breakouts and then finish the query.

CashCloister

    Steven Chin wrote in message <37184E41.17069ABA_at_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,Accoun t.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 - 14:59:48 CEST