Re: Need URGENT HELP on PL/SQL

>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,Acc
ount.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

There are a couple of things I saw you can do initially....

In one of my tuning books, it states that the use of an OR will prevent the use of an index. I tested it once, and it was true then (I have not tested it on Oracle8, so maybe its al little better) The way around that problem is to use a UNION ie on the first query
SELECT /*+ FIRST_ROWS */
PRIMARYKEY,Account.Name,Account.PreferredName,Account.ID,Account.Type,Acco unt.Source,Account.ContactKey
from Account where primarykey in (select primarykey from account where OIC = 'FE' AND SYSIND = '1'
union
select Accountkey from Lead Where Office = '0000000S0' AND SYSIND = '1') On the 2nd query, change the data comparisons from (to_char(startdate,'YYYYMMDD') <= '19990414' to
startdate <= to_date('19990414','YYYYMMDD'). You do not want to use a function on the data base column if you can help it. That will also prevent the use of a index on that column.

[Quoted] The use of LIKE also prevents the use of an index, The way I get around that for a leading LIKE is to do something like this: ID >=' SYS' and ID <='SYS~' (The ~ is the last ASCII character, so any printable character is less than or = to this.) Now for the NOT LIKE... I'll leave that up to you...

Hope these hints help you out a little. You are on the right track.

Dan Hekimian-Williams Received on Tue Apr 20 1999 - 06:37:00 CEST