Hi

Is full optimized this query ?

select mag.*, tabProd.DESCRIZIONE as
campo1,fornitor.codice,fornitor.descriz1 as campo2,tabgru.DESCRIZ as campo3
from mag
LEFT OUTER JOIN tabProd on mag.CODTABPROD = tabProd.CODICE
LEFT OUTER JOIN fornitor on mag.codforn = fornitor.codice
LEFT OUTER JOIN tabgru on mag.COD_MERCE = tabgru.COD_MERCE
where fornitor.codice = 2
order by  campo1,campo2,campo3

i have all indexs / fields Case Insensitive.


PLAN :

================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 10)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.
================================================================================

SELECT ALL "mag"."CODARTI" AS "CODARTI", "mag"."CODAFOR" AS "CODAFOR",
"mag"."CODORIGINALE" AS "CODORIGINALE", "mag"."DESCRIZ1" AS "DESCRIZ1",
"mag"."DESCRIZ2"
AS "DESCRIZ2", "mag"."DESCRIZIONE_LINGUA1" AS "DESCRIZIONE_LINGUA1",
"mag"."DESCRIZIONE_LINGUA2" AS "DESCRIZIONE_LINGUA2",
"mag"."DESCRIZIONE_LINGUA3" AS
"DESCRIZIONE_LINGUA3", "mag"."CODFORN" AS "CODFORN", "mag"."COD_LINEA" AS
"COD_LINEA"
"mag"."ANNO" AS "ANNO", "mag"."CODTESSUTO" AS "CODTESSUTO",
"mag"."CIDTAGMIS"
AS "CIDTAGMIS", "mag"."CODMARCA" AS "CODMARCA", "mag"."CODCATEG" AS
"CODCATEG",
"mag"."UNMISURA" AS "UNMISURA", "mag"."COD_MERCE" AS "COD_MERCE",
"mag"."CODTABPROD" AS "CODTABPROD", "mag"."SIGLA_LISTINO" AS
"SIGLA_LISTINO",
"mag"."COD_FAM_SCONTO" AS "COD_FAM_SCONTO", "mag"."TIPOCODBARRE" AS
"TIPOCODBARRE",
"mag"."GIAC_TOT" AS "GIAC_TOT", "mag"."QTA_ASSEGNATO_TOT" AS
"QTA_ASSEGNATO_TOT",
"mag"."GIAC_TOT_REMOTA" AS "GIAC_TOT_REMOTA", "mag"."ORDINATO_TOT_REMOTO" AS
"ORDINATO_TOT_REMOTO", "mag"."IMPEGNATO_TOT_REMOTO" AS
"IMPEGNATO_TOT_REMOTO",
"mag"."IMPE_TOT" AS "IMPE_TOT", "mag"."ORDI_TOT" AS "ORDI_TOT",
"mag"."DISP_TOT" AS "DISP_TOT"
"mag"."COD_IVA" AS "COD_IVA", "mag"."GIAC_MIN" AS "GIAC_MIN",
"mag"."GIACENZA_CONSIGLIATA" AS "GIACENZA_CONSIGLIATA", "mag"."PRECPREZAC"
AS "PRECPREZAC",
"mag"."SCONTO_ACQ_1" AS "SCONTO_ACQ_1", "mag"."SCONTO_ACQ_2" AS
"SCONTO_ACQ_2",
"mag"."SCONTO_ACQ_3" AS "SCONTO_ACQ_3", "mag"."PERC_PRZ_TRASP_ACQ" AS
"PERC_PRZ_TRASP_ACQ", "mag"."VAL_PRZ_TRASP_ACQ" AS "VAL_PRZ_TRASP_ACQ",
"mag"."PREZZO_LISTINO"
AS "PREZZO_LISTINO", "mag"."SCONTO_LISTINO_1" AS "SCONTO_LISTINO_1",
"mag"."SCONTO_LISTINO_2" AS "SCONTO_LISTINO_2", "mag"."SCONTO_LISTINO_3" AS
"SCONTO_LISTINO_3", "mag"."ULTPREZAC" AS "ULTPREZAC", "mag"."ULTDATAAC" AS
"ULTDATAAC",
"mag"."PERC_TRASPORTO" AS "PERC_TRASPORTO", "mag"."NUMSERIE" AS "NUMSERIE",
"mag"."CODVALUTA" AS "CODVALUTA", "mag"."PREZNET1" AS "PREZNET1",
"mag"."PREZNET2" AS
"PREZNET2", "mag"."PREZNET3" AS "PREZNET3", "mag"."PREZNET4" AS "PREZNET4",
"mag"."PREZNET5" AS "PREZNET5", "mag"."PREZIVA1" AS "PREZIVA1",
"mag"."PREZIVA2" AS
"PREZIVA2", "mag"."PREZIVA3" AS "PREZIVA3", "mag"."PREZIVA4" AS "PREZIVA4",
"mag"."PREZIVA5" AS "PREZIVA5", "mag"."RICARICO1" AS "RICARICO1",
"mag"."RICARICO2" AS
"RICARICO2", "mag"."RICARICO3" AS "RICARICO3", "mag"."RICARICO4" AS
"RICARICO4",
"mag"."RICARICO5" AS "RICARICO5", "mag"."SCOLIST1" AS "SCOLIST1",
"mag"."SCOLIST2" AS "SCOLIST2", "mag"."SCOLIST3" AS "SCOLIST3",
"mag"."SCOLIST4" AS "SCOLIST4"
"mag"."SCOLIST5" AS "SCOLIST5", "mag"."ARRPREZ1" AS "ARRPREZ1",
"mag"."ARRPREZ2" AS "ARRPREZ2", "mag"."ARRPREZ3" AS "ARRPREZ3",
"mag"."ARRPREZ4" AS "ARRPREZ4"
"mag"."ARRPREZ5" AS "ARRPREZ5", "mag"."DATA_ULT_PRZ" AS "DATA_ULT_PRZ",
"mag"."OBSOLETO" AS "OBSOLETO", "mag"."FLAG_ARTICOLO_BLOCCATO" AS
"FLAG_ARTICOLO_BLOCCATO", "mag"."DATA_ARTICOLO_BLOCCATO" AS
"DATA_ARTICOLO_BLOCCATO",
"mag"."MODALITA_UPD_PREZZO_ACQ" AS "MODALITA_UPD_PREZZO_ACQ",
"mag"."MODALITA_UPD_PREZZO_ACQ_GIRARE_FORNITORE" AS
"MODALITA_UPD_PREZZO_ACQ_GIRARE_FORNITORE", "mag"."STAMLIST"
AS "STAMLIST", "mag"."MOVIARTI" AS "MOVIARTI", "mag"."CODBARRE" AS
"CODBARRE",
"mag"."FLAG_PROM" AS "FLAG_PROM", "mag"."FLAGTIPOPROM" AS "FLAGTIPOPROM",
"mag"."SCOPROM" AS "SCOPROM", "mag"."SCOPROM_2" AS "SCOPROM_2",
"mag"."SCOPROM_3" AS
"SCOPROM_3", "mag"."SCOPROM_4" AS "SCOPROM_4", "mag"."QTAPROM" AS "QTAPROM",
"mag"."QTAPROM_FINO_A_1" AS "QTAPROM_FINO_A_1", "mag"."QTAPROM_FINO_A_2" AS
"QTAPROM_FINO_A_2", "mag"."QTAPROM_FINO_A_3" AS "QTAPROM_FINO_A_3",
"mag"."QTAPROM_FINO_A_4" AS "QTAPROM_FINO_A_4", "mag"."PREPRNET" AS
"PREPRNET", "mag"."PREPRNET_2"
AS "PREPRNET_2", "mag"."PREPRNET_3" AS "PREPRNET_3", "mag"."PREPRNET_4" AS
"PREPRNET_4", "mag"."PREPRIVA" AS "PREPRIVA", "mag"."PREPRIVA_2" AS
"PREPRIVA_2",
"mag"."PREPRIVA_3" AS "PREPRIVA_3", "mag"."PREPRIVA_4" AS "PREPRIVA_4",
"mag"."DATA_INIZ_PROM" AS "DATA_INIZ_PROM", "mag"."DATA_FINE_PROM" AS
"DATA_FINE_PROM",
"mag"."QTA_RIORDINO_MIN_FORNITORE" AS "QTA_RIORDINO_MIN_FORNITORE",
"mag"."DESC_ETI"
AS "DESC_ETI", "mag"."FLAG_MANODOPERA" AS "FLAG_MANODOPERA",
"mag"."FLAG_PRODOTTO_FINITO" AS "FLAG_PRODOTTO_FINITO", "mag"."UTENTECAR" AS
"UTENTECAR",
"mag"."UTENTENUM" AS "UTENTENUM", "mag"."FOTO" AS "FOTO", "mag"."PESO" AS
"PESO",
"mag"."CODREP" AS "CODREP", "mag"."CODSET" AS "CODSET", "mag"."CODFAM" AS
"CODFAM",
"mag"."POSIZMAGA" AS "POSIZMAGA", "mag"."QTACONFEZIONE" AS "QTACONFEZIONE",
"mag"."CODSCHEDAPROD" AS "CODSCHEDAPROD", "mag"."DESCRIZIONE_TEMP1" AS
"DESCRIZIONE_TEMP1", "mag"."DESCRIZIONE_TEMP2" AS "DESCRIZIONE_TEMP2",
"mag"."COD_FREQUENZA" AS
"COD_FREQUENZA", "mag"."COD_CAUS_VEND" AS "COD_CAUS_VEND",
"mag"."COD_CAUS_VEND_2" AS "COD_CAUS_VEND_2", "mag"."CODRAGGRUPPAMENTO" AS
"CODRAGGRUPPAMENTO",
"mag"."DISTINTA_AGGIORNAMENTO_AUTOMATICO" AS
"DISTINTA_AGGIORNAMENTO_AUTOMATICO",
"mag"."DISTINTA_PRZ_FIGLI_BLOCCATI" AS "DISTINTA_PRZ_FIGLI_BLOCCATI",
"mag"."DISTINTA_RICARICO" AS "DISTINTA_RICARICO",
"mag"."DISTINTA_TOTALE_LISTINO" AS
"DISTINTA_TOTALE_LISTINO", "mag"."DISTINTA_TOTALE_ACQ" AS
"DISTINTA_TOTALE_ACQ",
"mag"."DISTINTA_TOTALE" AS "DISTINTA_TOTALE",
"mag"."DISTINTA_PREZZO_SU_PADRE" AS
"DISTINTA_PREZZO_SU_PADRE", "mag"."DISTINTA_QTA_PADRE" AS
"DISTINTA_QTA_PADRE",
"mag"."DISTINTA_PREZZO_SU_FIGLI" AS "DISTINTA_PREZZO_SU_FIGLI",
"mag"."DISTINTA_QTA_FIGLI" AS "DISTINTA_QTA_FIGLI",
"mag"."DISTINTA_MOVIMENTA_PADRE" AS
"DISTINTA_MOVIMENTA_PADRE", "mag"."DISTINTA_MOVIMENTA_FIGLI" AS
"DISTINTA_MOVIMENTA_FIGLI",
"mag"."FLAG_NON_ESPORTARE" AS "FLAG_NON_ESPORTARE",
"mag"."FLAG_NON_CONTEGGIARE"
AS "FLAG_NON_CONTEGGIARE", "mag"."FLAG_NON_RINNOVABILE" AS
"FLAG_NON_RINNOVABILE", "mag"."CONFEZIONE_MODALITA_PREZZO" AS
"CONFEZIONE_MODALITA_PREZZO",
"mag"."CONFEZIONE_UNITA_MISURA" AS "CONFEZIONE_UNITA_MISURA",
"mag"."LEGAME_LISTINO_MODALITA" AS "LEGAME_LISTINO_MODALITA",
"mag"."LEGAME_LISTINO_COD_FORN" AS
"LEGAME_LISTINO_COD_FORN", "mag"."ORARIO_INIZIO_1" AS "ORARIO_INIZIO_1",
"mag"."ORARIO_FINE_1" AS "ORARIO_FINE_1", "mag"."ORARIO_INIZIO_2" AS
"ORARIO_INIZIO_2",
"mag"."ORARIO_FINE_2" AS "ORARIO_FINE_2", "mag"."CODTIPRISORSA" AS
"CODTIPRISORSA",
"mag"."DATAINS" AS "DATAINS", "mag"."ORA_INSERIMENTO" AS "ORA_INSERIMENTO",
"mag"."OPERATORE_INSERIMENTO" AS "OPERATORE_INSERIMENTO", "mag"."DATAULTVAR"
AS
"DATAULTVAR", "mag"."ORA_MODIFICA" AS "ORA_MODIFICA",
"mag"."OPERATORE_MODIFICA" AS
"OPERATORE_MODIFICA", "mag"."EC_VISIBILITA_IN_NEGOZIO" AS
"EC_VISIBILITA_IN_NEGOZIO"
"mag"."EC_IN_VENDITA" AS "EC_IN_VENDITA", "mag"."EC_MOSTRA_PREZZO" AS
"EC_MOSTRA_PREZZO", "mag"."EC_ARTICOLO_IN_HOMEPAGE" AS
"EC_ARTICOLO_IN_HOMEPAGE",
"mag"."EC_ARTICOLO_SEMPRE_ORDINABILE" AS "EC_ARTICOLO_SEMPRE_ORDINABILE",
"mag"."EC_DATA_INIZIO_DISPONIBILITA" AS "EC_DATA_INIZIO_DISPONIBILITA",
"mag"."EC_GIORNI_RIASSORTIMENTO" AS "EC_GIORNI_RIASSORTIMENTO",
"mag"."EC_DESCRIZIONE_BREVE" AS
"EC_DESCRIZIONE_BREVE", "mag"."EC_DESCRIZIONE_BREVE_LINGUA_1" AS
"EC_DESCRIZIONE_BREVE_LINGUA_1", "mag"."EC_DESCRIZIONE_METATAG" AS
"EC_DESCRIZIONE_METATAG",
"mag"."EC_DESCRIZIONE_SCHEDA_TECNICA" AS "EC_DESCRIZIONE_SCHEDA_TECNICA",
"mag"."EC_DESCRIZIONE_SCHEDA_TECNICA_LINGUA_1" AS
"EC_DESCRIZIONE_SCHEDA_TECNICA_LINGUA_1",
"mag"."EC_TITOLO_PAGINA" AS "EC_TITOLO_PAGINA",
"mag"."EC_TITOLO_PAGINA_LINGUA_1" AS "EC_TITOLO_PAGINA_LINGUA_1",
"mag"."EC_PAROLA_CHIAVE" AS "EC_PAROLA_CHIAVE"
"mag"."EC_DESCRIZIONE_ESTESA" AS "EC_DESCRIZIONE_ESTESA",
"mag"."EC_DESCRIZIONE_ESTESA_LINGUA_1" AS "EC_DESCRIZIONE_ESTESA_LINGUA_1",
"mag"."CARATTERISTICHE"
AS "CARATTERISTICHE", "mag"."NOTALUNGA" AS "NOTALUNGA", "mag"."NOTE" AS
"NOTE",
"tabProd"."DESCRIZIONE" AS "campo1", "fornitor"."codice" AS "codice",
"fornitor"."descriz1" AS "campo2", "tabgru"."DESCRIZ" AS "campo3" FROM "mag"
LEFT OUTER
JOIN "tabProd" ON "mag"."CODTABPROD" = "tabProd"."CODICE", LEFT OUTER JOIN
"tabgru" ON "mag"."COD_MERCE" = "tabgru"."COD_MERCE", LEFT OUTER JOIN
"fornitor" ON
"mag"."codforn" = "fornitor"."codice" WHERE "fornitor"."codice" = 2 ORDER BY
"tabProd"."DESCRIZIONE", "fornitor"."descriz1", "tabgru"."DESCRIZ"

Source Tables
-------------

mag: 12663 rows
tabProd: 1112 rows
fornitor: 285 rows
tabgru: 75 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the result set rows as they
were
generated:

"fornitor"."codice" = 2

Joins
-----

The driver table was the mag table

The mag table was joined to the tabProd table with the left outer join
expression:

"mag"."CODTABPROD" = "tabProd"."CODICE"

The mag table was joined to the tabgru table with the left outer join
expression:

"mag"."COD_MERCE" = "tabgru"."COD_MERCE"

The mag table was joined to the fornitor table with the left outer join
expression:

"mag"."codforn" = "fornitor"."codice"

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order

The following join condition was applied to the tabProd table:

"tabProd"."CODICE" = "mag"."CODTABPROD" [Index scan (tabprod.CODICE)]

The following join condition was applied to the tabgru table:

"tabgru"."COD_MERCE" = "mag"."COD_MERCE" [Index scan (tabgru.COD_MERCE)]

The following join condition was applied to the fornitor table:

"fornitor"."codice" = "mag"."codforn" [Index scan (fornitor.CODICE)]

================================================================================
1200 row(s) returned in 1,03 secs
================================================================================

Mauro


It looks pretty optimised. Its using index scans for all the joins. I'm not sure about

<<The following filter condition was applied to the result set rows as they
were
generated:

"fornitor"."codice" = 2>>

That may mean that there isn't an appropriate index to use or that ElevateDB decided that it was more efficient to scan each row as generated. I'm not 100% sure of the exact meaning of the new format of query plans yet.

Roy Lambert [Team Elevate]

Roy,

<< It looks pretty optimised. Its using index scans for all the joins. I'm
not sure about >>

That notation means that the filter condition was applied *after* the joins
due to the fact that the fornitor table was the target of a left-outer join.
In such a case EDB has to examine the row after the joins are executed in
order to take care of a situation where the left outer join generated a NULL
for the codice column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Yuknow I thought exactly that

Have a nice 4th - are you going to try and get to the top of the Statue of Liberty now she's reopened?


Roy Lambert

Roy,

<< Have a nice 4th - are you going to try and get to the top of the Statue
of Liberty now she's reopened? >>

I don't do heights normally, but I might have to make an exception for that.
 NYC is just a quick flight or semi-quick train ride from here, which is
great for short little trips.

--
Tim Young
Elevate Software
www.elevatesoft.com