Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Arrow Unanswered: rewriting SQL to avoid multiple scan of a table

    Hi,

    Is it possible to rewrite the following statement in order to avoid multiple scan of the tables:

    SELECT
    (ACT.ID_ACCES_CLIENT_TYPE * 100000) + 30024 ss_key,
    TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_key,
    30024 transtype_key,
    ACT.ID_ACCES_CLIENT_TYPE client_acces_d_sskey,
    T.ID_MODELE fonct_mobile_d_sskey,
    '0' type_mobile_key,
    1 MEMBER
    FROM ACCES_CLIENT_TYPE ACT, ACCES AC, TYPE_MODELE T
    where ACT.FLAG_ACTIF is not null
    and NVL(( ACT.DAT_FIN ),( SYSDATE ))> SYSDATE - ((3 + 0)*30)
    and AC.ID_ACCES = ACT.ID_ACCES
    and AC.FLAG_ACTIF is not null
    and T.TAC = AC.TAC_1
    AND mod(ACT.ID_ACCES_CLIENT_TYPE, 2) = 1
    union all
    SELECT
    (ACT.ID_ACCES_CLIENT_TYPE * 100000) + 30025 ss_key,
    TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_key,
    30025 transtype_key,
    ACT.ID_ACCES_CLIENT_TYPE client_acces_d_sskey,
    T.ID_MODELE fonct_mobile_d_sskey,
    '1' type_mobile_key,
    1 MEMBER
    FROM ACCES_CLIENT_TYPE ACT, ACCES AC, TYPE_MODELE T
    where ACT.FLAG_ACTIF is not null
    and NVL(( ACT.DAT_FIN ),( SYSDATE ))> SYSDATE - ((3 + 0)*30)
    and AC.ID_ACCES = ACT.ID_ACCES
    and AC.FLAG_ACTIF is not null
    and T.TAC = AC.TAC_U
    AND mod(ACT.ID_ACCES_CLIENT_TYPE, 2) = 1
    union all
    SELECT
    (ACT.ID_ACCES_CLIENT_TYPE * 100000) + 30026 ss_key,
    TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_key,
    30026 transtype_key,
    ACT.ID_ACCES_CLIENT_TYPE client_acces_d_sskey,
    T.ID_MODELE fonct_mobile_d_sskey,
    '2' type_mobile_key,
    1 MEMBER
    FROM ACCES_CLIENT_TYPE ACT, ACCES AC, TYPE_MODELE T
    where ACT.FLAG_ACTIF is not null
    and NVL(( ACT.DAT_FIN ),( SYSDATE ))> SYSDATE - ((3 + 0)*30)
    and AC.ID_ACCES = ACT.ID_ACCES
    and AC.FLAG_ACTIF is not null
    and T.TAC = AC.TACG_G
    AND mod(ACT.ID_ACCES_CLIENT_TYPE, 2) = 1



    Thanks for help

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Are you assuming that the table scan is not a the optimal access path that your DBMS (whatever it may be) will choose regardless of the number of rows and organization of the tables?

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112
    The query is doing 3 table scan of acces_client_type but maybe this query can be rewrite without union all. But how can i vary in one query type_mobile_key and ss_key ?

Posting Permissions

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