Re: Explain plan

BHAVESH PATEL schrieb am 05-Aug-98 02:29:47 zum Thema "Explain plan":

SQL>> explain plan
> 2 set statement_id = 't1'
> 3 for
> 4 select a.id from customer b,sales_order a
> 5 where exists (select 'x' from sales_order_items c where c.id= a.id
>and
> 6 c.prod_id not in (601,700))
> 7 and
> 8 a.cust_id = b.id and a.region = 'CHICAGO';

>Explained.

>hi can anyone please be able to send me some idea of how to tune this query
>to avoid the full table scan on the sales orders table.

Hello out there,
a first try whould be an index on sales_order(cust_id). Without it the only way to find all orders for a customer is a full table scan.
If you do have such an index, you could try to use a hint to make the optimizer use it:

select /*+ INDEX(a ix_seles_order_cust_id) */

   a.id from ...

You probably have to check the correct syntax as I am writing this at home out of memory.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |