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 Go to next message
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
Attached:
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 #689158 is a reply to message #689153] Thu, 28 September 2023 17:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
So table FF_ENTIDAD_MERCADOPAGO_AUX has 3010478 rows, but how many:

SELECT  COUNT(*)
  FROM  A001540.FF_ENTIDAD_MERCADOPAGO_AUX FF 
  WHERE FF.ASSET_BILLINGACC IS NULL
/
SY.
Re: Improve select with inner JOIN [message #689168 is a reply to message #689158] Fri, 29 September 2023 07:14 Go to previous messageGo to next message
carlino70
Messages: 15
Registered: April 2012
Junior Member
Hi,

 COUNT(*)
----------
  2482552

What would be your idea or suggestion Solomon?
Re: Improve select with inner JOIN [message #689169 is a reply to message #689168] Fri, 29 September 2023 08:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
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
                       )
/
SY.

[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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
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 Go to previous messageGo to next message
carlino70
Messages: 15
Registered: April 2012
Junior Member
Hi, Solomon:

ERROR at line 44:
ORA-00904: "T3"."LOCATION_ID__C": invalid identifier
--in both blocks you sent

--I tried this, an its OK
SQL>  select distinct LOCATION_ID__C from A001540.ADDRESS2__C where rownum < 10;
LOCATION_ID__C
------------------

a6af3000000LS9JAAW
--And This is fine:
  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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
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
/
and


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 #689184 is a reply to message #689183] Mon, 02 October 2023 11:01 Go to previous messageGo to next message
carlino70
Messages: 15
Registered: April 2012
Junior Member
--error:
WITH T1 AS (
*
ERROR at line 1:
ORA-00918: column ambiguously defined
--but this is OK
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
                                 )
           )
SELECT  *
  FROM  T1
/		   
1844739 rows selected.

Elapsed: 00:02:44.93
Re: Improve select with inner JOIN [message #689185 is a reply to message #689184] Mon, 02 October 2023 11:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
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
/
and

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.
Re: Improve select with inner JOIN [message #689186 is a reply to message #689185] Mon, 02 October 2023 13:20 Go to previous message
carlino70
Messages: 15
Registered: April 2012
Junior Member
Now it's working...I'll tell you the result...
Previous Topic: Grouping similar strings together using UTL_MATCH Jaro-Winkler
Next Topic: Need a pl/sql block to be entered in existing package
Goto Forum:
  


Current Time: Sun Feb 25 13:07:23 CST 2024