Home -> Usenet -> c.d.o.tools -> Need URGENT HELP on PL/SQL

Need URGENT HELP on PL/SQL

From: Steven Chin <steven.chin_at_pointinfo.com>
Date: Sat, 17 Apr 1999 17:02:57 +0800
Message-ID: <37184E41.17069ABA_at_pointinfo.com> 





Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit



Hi,



[Quoted] 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



[Quoted] 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




--------------0AE01CE46D97000B16BC09A2
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit





<HTML>


<TT><FONT SIZE=-1>Hi,</FONT></TT>



<P><TT><FONT SIZE=-1>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!</FONT></TT>
<BR><TT><FONT SIZE=-1>&nbsp;</FONT></TT>



<P><B><TT><FONT SIZE=-1>SELECT /*+ FIRST_ROWS */ PRIMARYKEY,Account.Name,Account.PreferredName,Account.ID,Account.Type,Account.Source,Account.ContactKey</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>from Account&nbsp; where OIC = 'FE' AND SYSIND
= '1'</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>OR primarykey in (Select Accountkey from Lead
Where Office = '0000000S0' AND SYSIND = '1')</FONT></TT></B>



<P><TT><FONT SIZE=-1>>select operation, object_name, options from sys.plan_table;</FONT></TT>
<BR><TT><FONT SIZE=-1>OPERATION&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
OPTIONS</FONT></TT>

<BR><TT><FONT SIZE=-1>------------------------------ ------------------------------
------------------------------</FONT></TT>
<BR><TT><FONT SIZE=-1>SELECT STATEMENT</FONT></TT>


<BR><TT><FONT SIZE=-1>FILTER</FONT></TT>
<BR><TT><FONT SIZE=-1>TABLE ACCESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
ACCOUNT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
FULL</FONT></TT>


<BR><TT><FONT SIZE=-1>TABLE ACCESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
LEAD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
BY ROWID</FONT></TT>


<BR><TT><FONT SIZE=-1>INDEX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
LD_OFFICE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

RANGE SCAN</FONT></TT>
<BR><TT><FONT SIZE=-1>5 rows selected.</FONT></TT>
<BR><TT><FONT SIZE=-1>&nbsp;</FONT></TT>
<BR><TT><FONT SIZE=-1>&nbsp;</FONT></TT>
<BR><TT><FONT SIZE=-1>&nbsp;</FONT></TT>

<P><B><TT><FONT SIZE=-1>SELECT /*+ FIRST_ROWS */ PRIMARYKEY,AccountKey,Priority,Status,ContactKey</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>from Lead where Office = 'OUB0000000S0'</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>AND ForeignKey in (select PrimaryKey from campaign</FONT></TT></B>

<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
where (to_char(startdate,'YYYYMMDD') &lt;= '19990414'</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;


AND to_char(enddate,'YYYYMMDD') >= '19990414')</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND ID not like 'SYS%')</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>AND ((CreatedBy = 'BATCHML'</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND ForeignKey
in (select campaignkey</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
from AssignedTarget</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
where InfoKey = '0000000S0'</FONT></TT></B>

<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AND DistrInd = '1'</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;


)</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OR (CreatedBy &lt;>
'BATCHML')</FONT></TT></B>


<BR><B><TT><FONT SIZE=-1>&nbsp;&nbsp;&nbsp; )</FONT></TT></B>
<BR><B><TT><FONT SIZE=-1>AND SYSIND = '1'</FONT></TT></B>



[Quoted] [Quoted] <P><TT><FONT SIZE=-1>> select operation, object_name, options from sys.plan_table;</FONT></TT>
<BR><TT><FONT SIZE=-1>OPERATION&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
OPTIONS</FONT></TT>

<BR><TT><FONT SIZE=-1>------------------------------ ------------------------------
------------------------------</FONT></TT>
<BR><TT><FONT SIZE=-1>SELECT STATEMENT</FONT></TT>
<BR><TT><FONT SIZE=-1>FILTER</FONT></TT>


<BR><TT><FONT SIZE=-1>NESTED LOOPS</FONT></TT>
<BR><TT><FONT SIZE=-1>TABLE ACCESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
CAMPAIGN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
FULL</FONT></TT>


<BR><TT><FONT SIZE=-1>TABLE ACCESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
LEAD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

BY ROWID</FONT></TT>
<BR><TT><FONT SIZE=-1>AND-EQUAL</FONT></TT>
<BR><TT><FONT SIZE=-1>INDEX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;


LD_FOREIGNKEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
RANGE SCAN</FONT></TT>


<BR><TT><FONT SIZE=-1>INDEX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
LD_OFFICE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
RANGE SCAN</FONT></TT>


<BR><TT><FONT SIZE=-1>TABLE ACCESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
ASSIGNEDTARGET&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
BY ROWID</FONT></TT>


<BR><TT><FONT SIZE=-1>INDEX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
AT_INFOKEY_DISTRIND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

RANGE SCAN</FONT></TT>
<BR><TT><FONT SIZE=-1>10 rows selected.</FONT></TT>
<BR><TT><FONT SIZE=-1>&nbsp;</FONT></TT>

<P><TT><FONT SIZE=-1>Regards,</FONT></TT>
<BR><TT><FONT SIZE=-1>Steven Chin</FONT></TT>


<BR>&nbsp;


<BR>&nbsp;</HTML>



--------------0AE01CE46D97000B16BC09A2--
Received on Sat Apr 17 1999 - 11:02:57 CEST












Original text of this message