Home » SQL & PL/SQL » SQL & PL/SQL » Improve select with inner JOIN (Oracle 12, SQL, Linux )
| Improve select with inner JOIN [message #689153] |
Thu, 28 September 2023 15:24  |
 |
carlino70
Messages: 15
Registered: April 2012
|
Junior Member |
|
|
HI, I need to improve the slow ejecution over this sentence.
select /*+ parallel(ASSET_MP,16)*/ distinct FF.ID_MAYORISTA,
FF.NUMERO_PROFORMA,
FF.PERIODO,
FF.FECHA_RECARGA,
FF.ID_RECARGA,
FF.NRO_LINEA,
FF.MONTO_RECARGA,
FF.CONSUMO_DATOS,
FF.CONSUMO_VOZ,
FF.NOMBRE_ARCHIVO,
A.MSISDN__C ,
A.VLOCITY_CMT__BILLINGACCOUNTID_,
B.ID, B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C,
C.ID,
C.LOCATION_ID__C,
C.PROVINCE__C,
D.ID,
D.MUNICIPALITY__C,
D.STATENAME__C
from A001540.FF_ENTIDAD_MERCADOPAGO_AUX FF
left join A001540.ASSET_MP A on FF.nro_linea = A.MSISDN__C
left join A001540.ACCOUNT B on A.VLOCITY_CMT__BILLINGACCOUNTID_ = B.id
left join A001540.ADDRESS2__C C on B.BILLING_ADDRESS__C = C.id
left join A001540.LOCATION__C D on C.LOCATION_ID__C = D.id
where FF.ASSET_BILLINGACC is null;
ROWS count on FF_ENTIDAD_MERCADOPAGO_AUX
COUNT(1)
----------
3010478
ROWS count on ASSET_MP;
COUNT(1)
----------
21502266
ROWS count on ACCOUNT;
COUNT(1)
----------
65028597
Rows count on A001540.ADDRESS2__C;
COUNT(1)
----------
39866344
Rows count on A001540.LOCATION__C;
COUNT(1)
----------
8835
Explain_Plan_and_Indexes
The execution never give me results...
Could you give me any idea to improve this?
Regards
|
|
|
|
|
|
|
|
| Re: Improve select with inner JOIN [message #689169 is a reply to message #689168] |
Fri, 29 September 2023 08:00  |
Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Explain plan does table FF full san. If row count where FF.ASSET_BILLINGACC IS NULL would be relatively small then function based index would help. However, based on your answer, 83% of rows have null FF.ASSET_BILLINGACC. Other than that it is hard to tell without knowing your data. For example, what is the count for:
SELECT COUNT(DISTINCT FF.NRO_LINEA)
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX FF
WHERE FF.ASSET_BILLINGACC IS NULL
/
SELECT COUNT(*),
COUNT(DISTINCT A.VLOCITY_CMT__BILLINGACCOUNTID_)
FROM A001540.ASSET_MP A
WHERE A.MSISDN__C in (
SELECT FF.NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX FF
WHERE FF.ASSET_BILLINGACC IS NULL
)
/
[Updated on: Fri, 29 September 2023 08:10] Report message to a moderator |
|
|
|
| Re: Improve select with inner JOIN [message #689170 is a reply to message #689169] |
Fri, 29 September 2023 08:22 |
|
carlino70
Messages: 15
Registered: April 2012
|
Junior Member |
|
|
SQL> SELECT COUNT(DISTINCT FF.NRO_LINEA)
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX FF
WHERE FF.ASSET_BILLINGACC IS NULL
/ 2 3 4
COUNT(DISTINCTFF.NRO_LINEA)
---------------------------
1849478
SQL> SELECT COUNT(*)
FROM A001540.ASSET_MP A
WHERE A.MSISDN__C in (
SELECT FF.NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX FF
)
/ 2 3 4 5 6 7
COUNT(*)
----------
2011037
[Updated on: Fri, 29 September 2023 08:23] Report message to a moderator |
|
|
|
| Re: Improve select with inner JOIN [message #689171 is a reply to message #689170] |
Fri, 29 September 2023 08:48 |
Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And post result of:
WITH T AS (
SELECT DISTINCT NUMERO_PROFORMA,
PERIODO,
FECHA_RECARGA,
ID_RECARGA,
NRO_LINEA,
MONTO_RECARGA,
CONSUMO_DATOS,
CONSUMO_VOZ,
NOMBRE_ARCHIVO
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
SELECT COUNT(*)
FROM T
/
SY.
|
|
|
|
| Re: Improve select with inner JOIN [message #689172 is a reply to message #689171] |
Fri, 29 September 2023 09:23 |
|
carlino70
Messages: 15
Registered: April 2012
|
Junior Member |
|
|
WITH T AS (
SELECT DISTINCT NUMERO_PROFORMA,
PERIODO,
FECHA_RECARGA,
ID_RECARGA,
NRO_LINEA,
MONTO_RECARGA,
CONSUMO_DATOS,
CONSUMO_VOZ,
NOMBRE_ARCHIVO
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
SELECT COUNT(*)
FROM T
/
COUNT(*)
----------
2482552
[Updated on: Fri, 29 September 2023 11:40] Report message to a moderator |
|
|
|
| Re: Improve select with inner JOIN [message #689177 is a reply to message #689172] |
Fri, 29 September 2023 16:41 |
Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Same 83%. However only about 10% of A001540.ASSET_MP rows have a match in A001540.FF_ENTIDAD_MERCADOPAGO_AUX (condition FF.NRO_LINEA = A.MSISDN__C). I'd test the following. How long it takes to select all rows (not just first screen as many tools fetch screen by screen) and how many rows it selects:
WITH T1 AS (
SELECT /*+ MATERIALIZE */
MSISDN__C ,
VLOCITY_CMT__BILLINGACCOUNTID_
FROM A001540.ASSET_MP
WHERE MSISDN__C in (
SELECT NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
),
T2 AS (
SELECT /*+ MATERIALIZE */
T1.*,
B.ID, B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C
FROM T1
LEFT JOIN
A001540.ACCOUNT B
ON T1.VLOCITY_CMT__BILLINGACCOUNTID_ = B.ID
),
T3 AS (
SELECT /*+ MATERIALIZE */
T2.*,
C.ID,
C.LOCATION_ID__C,
C.PROVINCE__C
FROM T2
LEFT JOIN
A001540.ADDRESS2__C C
ON T2.BILLING_ADDRESS__C = C.ID
),
T4 AS (
SELECT /*+ MATERIALIZE */
T3.*,
D.ID,
D.MUNICIPALITY__C,
D.STATENAME__C
FROM T2
LEFT JOIN
A001540.LOCATION__C D
ON T3.LOCATION_ID__C = D.ID
)
SELECT *
FROM T4
/
and how long it takes to select all rows and how many rows it selects:
WITH T1 AS (
SELECT /*+ MATERIALIZE */
MSISDN__C ,
VLOCITY_CMT__BILLINGACCOUNTID_
FROM A001540.ASSET_MP
WHERE MSISDN__C in (
SELECT NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
),
T2 AS (
SELECT /*+ MATERIALIZE */
T1.*,
B.ID, B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C
FROM T1
LEFT JOIN
A001540.ACCOUNT B
ON T1.VLOCITY_CMT__BILLINGACCOUNTID_ = B.ID
),
T3 AS (
SELECT /*+ MATERIALIZE */
T2.*,
C.ID,
C.LOCATION_ID__C,
C.PROVINCE__C
FROM T2
LEFT JOIN
A001540.ADDRESS2__C C
ON T2.BILLING_ADDRESS__C = C.ID
),
T4 AS (
SELECT /*+ MATERIALIZE */
T3.*,
D.ID,
D.MUNICIPALITY__C,
D.STATENAME__C
FROM T2
LEFT JOIN
A001540.LOCATION__C D
ON T3.LOCATION_ID__C = D.ID
)
SELECT DISTINCT *
FROM T4
/
SY.
|
|
|
|
| Re: Improve select with inner JOIN [message #689182 is a reply to message #689177] |
Mon, 02 October 2023 08:19 |
|
carlino70
Messages: 15
Registered: April 2012
|
Junior Member |
|
|
Hi, Solomon:
ERROR at line 44:
ORA-00904: "T3"."LOCATION_ID__C": invalid identifier
--I tried this, an its OK
SQL> select distinct LOCATION_ID__C from A001540.ADDRESS2__C where rownum < 10; LOCATION_ID__C
------------------
a6af3000000LS9JAAW
1 SELECT D.ID,
2 D.MUNICIPALITY__C,
3 D.STATENAME__C
4 FROM A001540.ADDRESS2__C C,
5 A001540.LOCATION__C D
6 WHERE C.LOCATION_ID__C = D.ID
7* AND rownum < 10
SQL> /
ID MUNICIPALITY__C STATENAME__C
------------------ -------------------------------------------------------------------------------- --------------------------------------------------
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS9JAAW CORONEL DE MARINA L ROSALES BUENOS AIRES
a6af3000000LS7nAAG BAHIA BLANCA BUENOS AIRES
9 rows selected.
[Updated on: Mon, 02 October 2023 08:55] Report message to a moderator |
|
|
|
| Re: Improve select with inner JOIN [message #689183 is a reply to message #689182] |
Mon, 02 October 2023 09:50 |
Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Ah, a typo, FROM clause in T4 should be FROM T3, not FROM T2:
WITH T1 AS (
SELECT /*+ MATERIALIZE */
MSISDN__C ,
VLOCITY_CMT__BILLINGACCOUNTID_
FROM A001540.ASSET_MP
WHERE MSISDN__C in (
SELECT NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
),
T2 AS (
SELECT /*+ MATERIALIZE */
T1.*,
B.ID, B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C
FROM T1
LEFT JOIN
A001540.ACCOUNT B
ON T1.VLOCITY_CMT__BILLINGACCOUNTID_ = B.ID
),
T3 AS (
SELECT /*+ MATERIALIZE */
T2.*,
C.ID,
C.LOCATION_ID__C,
C.PROVINCE__C
FROM T2
LEFT JOIN
A001540.ADDRESS2__C C
ON T2.BILLING_ADDRESS__C = C.ID
),
T4 AS (
SELECT /*+ MATERIALIZE */
T3.*,
D.ID,
D.MUNICIPALITY__C,
D.STATENAME__C
FROM T3
LEFT JOIN
A001540.LOCATION__C D
ON T3.LOCATION_ID__C = D.ID
)
SELECT *
FROM T4
/
WITH T1 AS (
SELECT /*+ MATERIALIZE */
MSISDN__C ,
VLOCITY_CMT__BILLINGACCOUNTID_
FROM A001540.ASSET_MP
WHERE MSISDN__C in (
SELECT NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
),
T2 AS (
SELECT /*+ MATERIALIZE */
T1.*,
B.ID, B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C
FROM T1
LEFT JOIN
A001540.ACCOUNT B
ON T1.VLOCITY_CMT__BILLINGACCOUNTID_ = B.ID
),
T3 AS (
SELECT /*+ MATERIALIZE */
T2.*,
C.ID,
C.LOCATION_ID__C,
C.PROVINCE__C
FROM T2
LEFT JOIN
A001540.ADDRESS2__C C
ON T2.BILLING_ADDRESS__C = C.ID
),
T4 AS (
SELECT /*+ MATERIALIZE */
T3.*,
D.ID,
D.MUNICIPALITY__C,
D.STATENAME__C
FROM T3
LEFT JOIN
A001540.LOCATION__C D
ON T3.LOCATION_ID__C = D.ID
)
SELECT DISTINCT *
FROM T4
/
SY.
|
|
|
|
|
|
| Re: Improve select with inner JOIN [message #689185 is a reply to message #689184] |
Mon, 02 October 2023 11:11 |
Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
WITH T1 AS (
SELECT /*+ MATERIALIZE */
MSISDN__C ,
VLOCITY_CMT__BILLINGACCOUNTID_
FROM A001540.ASSET_MP
WHERE MSISDN__C in (
SELECT NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
),
T2 AS (
SELECT /*+ MATERIALIZE */
T1.*,
B.ID B_ID,
B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C
FROM T1
LEFT JOIN
A001540.ACCOUNT B
ON T1.VLOCITY_CMT__BILLINGACCOUNTID_ = B.ID
),
T3 AS (
SELECT /*+ MATERIALIZE */
T2.*,
C.ID C_ID,
C.LOCATION_ID__C,
C.PROVINCE__C
FROM T2
LEFT JOIN
A001540.ADDRESS2__C C
ON T2.BILLING_ADDRESS__C = C.ID
),
T4 AS (
SELECT /*+ MATERIALIZE */
T3.*,
D.ID D_ID,
D.MUNICIPALITY__C,
D.STATENAME__C
FROM T3
LEFT JOIN
A001540.LOCATION__C D
ON T3.LOCATION_ID__C = D.ID
)
SELECT *
FROM T4
/
WITH T1 AS (
SELECT /*+ MATERIALIZE */
MSISDN__C ,
VLOCITY_CMT__BILLINGACCOUNTID_
FROM A001540.ASSET_MP
WHERE MSISDN__C in (
SELECT NRO_LINEA
FROM A001540.FF_ENTIDAD_MERCADOPAGO_AUX
WHERE ASSET_BILLINGACC IS NULL
)
),
T2 AS (
SELECT /*+ MATERIALIZE */
T1.*,
B.ID B_ID,
B.BILLINGSTATECODE,
B.CUSTOMERINTEGRATIONID__C,
B.ACCOUNTINTEGRATIONID__C,
B.BILLING_ADDRESS__C
FROM T1
LEFT JOIN
A001540.ACCOUNT B
ON T1.VLOCITY_CMT__BILLINGACCOUNTID_ = B.ID
),
T3 AS (
SELECT /*+ MATERIALIZE */
T2.*,
C.ID C_ID,
C.LOCATION_ID__C,
C.PROVINCE__C
FROM T2
LEFT JOIN
A001540.ADDRESS2__C C
ON T2.BILLING_ADDRESS__C = C.ID
),
T4 AS (
SELECT /*+ MATERIALIZE */
T3.*,
D.ID D_ID,
D.MUNICIPALITY__C,
D.STATENAME__C
FROM T3
LEFT JOIN
A001540.LOCATION__C D
ON T3.LOCATION_ID__C = D.ID
)
SELECT DISTINCT *
FROM T4
/
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 07 01:22:48 CST 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
