Full Table Scan on a Partition table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Full Table Scan on a Partition table

  1. #1
    Join Date
    Feb 2001
    Posts
    119
    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 .


  2. #2
    Join Date
    Feb 2001
    Posts
    119
    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 .


  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  4. #4
    Join Date
    Feb 2001
    Posts
    119
    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)

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;
    ;

  6. #6
    Join Date
    Feb 2001
    Posts
    119
    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 .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width