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

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?

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 ?