Can this sql be tuned?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Can this sql be tuned?

  1. #1
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29

    Can this sql be tuned?

    Dear All,

    I would like to find out if this sql can be tuned:

    >>>>>
    SELECT a.rowid, a.dm_ind
    from dt_company_contact_person a, dt_company_contact_person@to_v3seq b
    where a.code_office_created_by = b.code_office_created_by and
    a.id_cont_person = b.id_cont_person and
    a.dm_ind = 'Y' and
    b.dm_ind = 'N';
    <<<<<

    For the local table, this is the count result:

    >>>>>
    SQL> select count(*), dm_ind from dt_company_contact_person group by dm_ind
    2 ;

    COUNT(*) D
    ---------- -
    1062 N
    2193119 Y

    <<<<<


    For the remote table, this is the count result:

    >>>>>

    COUNT(*) D
    ---------- -
    1056 N
    2194648 Y
    <<<<<


    For my sql statement, it is doing full table scan even though I have the index on code_office_created_by, id_cont_person.

    >>>>>
    ID pid OPTIMIZE OPERATION OPTIONS OBJECT_NAME
    ---- ---- -------- -------------------- -------------------- ---------------------------
    0 CHOOSE SELECT STATEMENT
    1 0 HASH JOIN
    2 1 ANALYZED TABLE ACCESS FULL DT_COMPANY_CONTACT_PERSON
    3 1 REMOTE <<<<<

    Actually, the result set got from the remote table is small. Is there any way I can avoid the full table scan on the local table?

    Thanks,

    -- Chris

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It sounds like what you need to happen is for the small set of remote rows meeting the dm_ind = 'N' condition to be brought to the local database, then you want an index lookup against the local table, right?

    So the hint Driving_Site(a) will make sure that the remore rows are brought to the local database for the join, not the other wayaround. You can also try the hinting with Leading(b) to make the remote table the first in the join order.

    So, try ...

    Code:
    SELECT /*+ Driving_Site(a) Leading (b) */
    a.rowid, a.dm_ind
    from dt_company_contact_person a, dt_company_contact_person@to_v3seq b
    where a.code_office_created_by = b.code_office_created_by and
    a.id_cont_person = b.id_cont_person and
    a.dm_ind = 'Y' and
    b.dm_ind = 'N';
    ... and let us know how it goes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Either one of the tables (remote or local) will go for FTS.
    may be index on dm_ind (bit map) on remote with driving table local will avoid you FTS.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You'd probably only be interested in indexing the "N" values-- including the "Y"'s in the index would be a waste of time.

    You can do this with a function-based index, by ..

    Code:
    create index
       my_index
    on dt_company_contact_person 
       (Decode(dm_ind,'N','N',Null));
    You would then access the indexed "N" values by querying ..
    Code:
    Where
       Decode(dm_ind,'N','N',Null) = 'N'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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