Full Table Scan on a Partition table

[Subha]

I am running a sql statement like

select * from
from dummy ad
where credit_acct not in (select dt.account_number
from Customer_Accounts_100 dt
where ad.credit_acct = dt.account_number));

The table customer_accounts has 40 partitions .The above querry goes for a full table scan on Customer_Accounts rather than the partition Customer_Accounts_100.

why ? what should we do to run only on the partition table .

[Subha]

SQL> explain plan for SELECT * FROM DUMMY AD
4 WHERE CREDIT_ACCT NOT IN (SELECT DT.ACCOUNT_NUMBER FROM
5 CUSTOMER_ACCOUNTS_100 DT WHERE AD.CREDIT_ACCT = DT.ACCOUNT_NUMBER ) ;

Explained.

SQL> get explain
1 select lpad(' ',2*(level-1))|| operation||' '||
2 options||' '||object_name||
3 ' '||object_type||' '||object_instance||' '||
4 decode(id,0,'Cost = '||position) "Query Plan"
5 from plan_table
6 start with id = 0 -- start with statement_id = 'MyTest'
7* connect by prior id = parent_id
SQL> /

Query Plan
----------------------------------------------------------------------------------------
SELECT STATEMENT Cost = 2684
FILTER
TABLE ACCESS FULL DUMMY 1
TABLE ACCESS FULL CUSTOMER_ACCOUNTS 3
TABLE ACCESS FULL ZIP_CHECK 1
TABLE ACCESS FULL DUMMY 1
TABLE ACCESS FULL CUSTOMER_ACCOUNTS 3
SELECT STATEMENT Cost =
FILTER
TABLE ACCESS FULL DUMMY 1
TABLE ACCESS FULL CUSTOMER_ACCOUNTS 3
TABLE ACCESS FULL ZIP_CHECK 1
TABLE ACCESS FULL DUMMY 1
TABLE ACCESS FULL CUSTOMER_ACCOUNTS 3

This is my explain plan .I have only two tables involved in the select tables .I do'nt know why it is doing a scan on the ZIP_CHECK also .

[Shestakov]

In my mind u have problem with "NOT IN" in select statment, because
oracle optimazer usually convert it into:

where
1)
CREDIT_ACCT <> (SELECT DT.ACCOUNT_NUMBER FROM
5 CUSTOMER_ACCOUNTS_100 DT WHERE AD.CREDIT_ACCT = DT.ACCOUNT_NUMBER )
-- and oracle can use index
OR !!!
2)
CREDIT_ACCT IS NOT NULL
-- and oracle should use FULL SCAN TABLE.

[Subha]

Thanks .How do I modify this querry .Why does a third table comes into picture when there are only tables invloved (No views are used at all)

[Shestakov]

First question:
Is DUMMY is ORACLE DUMMY table, I hope not.
Because, if yes then dummy.CREDIT_ACCT is nothing.

SELECT ad.CREDIT_ACCT
FROM DUMMY AD
WHERE
NOT EXISTS AD.CREDIT_ACCT = (SELECT DT.ACCOUNT_NUMBER FROM CUSTOMER_ACCOUNTS_100 DT);

But may be better:

SELECT * FROM DUMMY AD
WHERE CREDIT_ACCT NOT IN (SELECT DT.ACCOUNT_NUMBER FROM
CUSTOMER_ACCOUNTS_100 DT WHERE AD.CREDIT_ACCT = DT.ACCOUNT_NUMBER)
and ad.CREDIT_ACCT >= 0;
;

[Subha]

The dummy table is a created my me .It is having the same structure of customer_accounts .

I will work on your new statements and inform about the performance gains .