Autor Téma: Plan NATURAL - ako to čítať a riešiť?  (Přečteno 494 krát)

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 2996
  • Karma: 29
    • Verze Delphi: XE7 professional
Plan NATURAL - ako to čítať a riešiť?
« kdy: 07-05-2017, 17:17:45 »
Dávam tu konkrétny prípad. Akurát viem, že to znamená prácu bez indexu a nad celou tabuľkou.
FKxxx sú cudzie kľúče, nad ktorými je automaticky vytvorený index.

Plan:
PLAN SORT (JOIN (INVOICEINCOMINGITEMS NATURAL, INVOICEINCOMINGS INDEX (PK_INVOICEINCOMINGS), SERVICEPARAMETERS INDEX (FK_SERVICEPARAMETERS_1)))
------------------------------------------------
Table Operations:
+--------------------------+-----------+-----------+-----------+-----------+-----------+
|        Table Name        | Index | Non-Index | Updates | Deletes | Inserts  |
|                          | reads |   reads   |         |         |          |
+--------------------------+-----------+-----------+-----------+-----------+-----------+
|      INVOICEINCOMINGITEMS|         0 |        52 |         0 |         0 |         0 |
|          INVOICEINCOMINGS|        52 |         0 |         0 |         0 |         0 |
|         SERVICEPARAMETERS|        49 |         0 |         0 |         0 |         0 |
Kód: Delphi [Vybrat]
  1. FROM
  2.   INVOICEINCOMINGS
  3.   INNER JOIN INVOICEINCOMINGITEMS ON (INVOICEINCOMINGS.IDINVOICEINCOMINGS = INVOICEINCOMINGITEMS.FKINVOICEINCOMINGS)
  4.   INNER JOIN SERVICEPARAMETERS ON (INVOICEINCOMINGITEMS.FKSERVICES = SERVICEPARAMETERS.FKSERVICES)
  5.  
Ja z toho viem vyčítať akurát to, že sa jedná o tabuľku INVOICEINCOMINGITEMS. A to je celkom jednoduchá situácia.
« Poslední změna: 07-05-2017, 17:19:40 od Stanislav Hruška »
Delphi XE7, FireBird
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 2996
  • Karma: 29
    • Verze Delphi: XE7 professional
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #1 kdy: 07-05-2017, 17:30:23 »
Radšej prikladám celý Select
Kód: [Vybrat]
SELECT
  INVOICEINCOMINGITEMS.FKSERVICES,
  INVOICEINCOMINGITEMS.PRICEUNIT,
  MIN(INVOICEINCOMINGITEMS.DATEFROM) AS DATEFROM,
  MAX(INVOICEINCOMINGITEMS.DATETO) AS DATETO,
  COALESCE(INVOICEINCOMINGITEMS.FKSUBACCOUNTS, -1) AS FKSUBACCOUNTS
FROM
  INVOICEINCOMINGS
  INNER JOIN INVOICEINCOMINGITEMS ON (INVOICEINCOMINGS.IDINVOICEINCOMINGS = INVOICEINCOMINGITEMS.FKINVOICEINCOMINGS)
  INNER JOIN SERVICEPARAMETERS ON (INVOICEINCOMINGITEMS.FKSERVICES = SERVICEPARAMETERS.FKSERVICES)
WHERE
  INVOICEINCOMINGITEMS.ISCOST = 1 AND
  INVOICEINCOMINGS.FKFOCS = 1776 AND
  SERVICEPARAMETERS.FKDIVIDEBYCOST1 IS NOT NULL AND
  SERVICEPARAMETERS.PERIOD = 2016
  AND
((EXTRACT(YEAR FROM INVOICEINCOMINGITEMS.DATEFROM) = 2015 AND EXTRACT(YEAR FROM INVOICEINCOMINGITEMS.DATETO) = 2016) OR
(EXTRACT(YEAR FROM INVOICEINCOMINGITEMS.DATEFROM) = 2016 AND EXTRACT(YEAR FROM INVOICEINCOMINGITEMS.DATETO) = 2016) OR
(EXTRACT(YEAR FROM INVOICEINCOMINGITEMS.DATEFROM) = 2016 AND EXTRACT(YEAR FROM INVOICEINCOMINGITEMS.DATETO) = 2017))
GROUP BY
  INVOICEINCOMINGITEMS.FKSERVICES,
  INVOICEINCOMINGITEMS.PRICEUNIT,
  COALESCE(INVOICEINCOMINGITEMS.FKSUBACCOUNTS, -1);
a definícia tabuľky
Kód: [Vybrat]
CREATE TABLE INVOICEINCOMINGITEMS (
  IDINVOICEITEMS PRIMARYKEY NOT NULL,
  FKINVOICEINCOMINGS FOREIGNKEY NOT NULL,
  FKSERVICES FOREIGNKEY NOT NULL,
  FKCAANALYTICOPTIONS FOREIGNKEY NOT NULL,
  FKUNITS FOREIGNKEY NOT NULL,
  FKVATRATES FOREIGNKEY NOT NULL,
  TITLE MEMO NOT NULL,
  AMOUNT CURRENCY NOT NULL,
  PRICEUNIT CURRENCY NOT NULL,
  PRICE CURRENCY NOT NULL,
  PRICEWITHVAT CURRENCY NOT NULL,
  PRICETOROUND CURRENCY NOT NULL,
  CENTSETTLEMENT CURRENCY NOT NULL,
  PRICETOTAL CURRENCY NOT NULL,
  DATEFROM DATEFROM NOT NULL,
  DATETO DATETO,
  ISCOST "BOOLEAN" DEFAULT '0' NOT NULL,
  FKSUBACCOUNTS FOREIGNKEYNULL);

ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT PK_INVOICEINCOMINGITEMS PRIMARY KEY (IDINVOICEITEMS);
ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT FK_INVOICEINCOMINGITEMS FOREIGN KEY (FKINVOICEINCOMINGS) REFERENCES INVOICEINCOMINGS(IDINVOICEINCOMINGS) ON DELETE CASCADE;
ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT FK_INVOICEINCOMINGITEM_SUBACC FOREIGN KEY (FKSUBACCOUNTS) REFERENCES SUBACCOUNTS(IDSUBACCOUNTS);
ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT FK_INVOICEITEM_ANALOPTION FOREIGN KEY (FKCAANALYTICOPTIONS) REFERENCES CAANALYTICOPTIONS(IDCAANALYTICOPTIONS);
ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT FK_INVOICEITEM_SERVICE FOREIGN KEY (FKSERVICES) REFERENCES SERVICES(IDSERVICES);
ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT FK_INVOICEITEM_UNIT FOREIGN KEY (FKUNITS) REFERENCES UNITS(IDUNITS);
ALTER TABLE INVOICEINCOMINGITEMS ADD CONSTRAINT FK_INVOICEITEM_VATRATE FOREIGN KEY (FKVATRATES) REFERENCES VATRATES(IDVATRATES);

GRANT SELECT, INSERT, DELETE, REFERENCES, UPDATE ON INVOICEINCOMINGITEMS TO SYSDBA WITH GRANT OPTION;
« Poslední změna: 07-05-2017, 17:34:53 od Stanislav Hruška »
Delphi XE7, FireBird
Expert na kladenie nejasne formulovaných otázok.

Online vandrovnik

  • Hrdina
  • ****
  • Příspěvků: 286
  • Karma: 17
    • Verze Delphi: 10.2
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #2 kdy: 07-05-2017, 20:26:02 »
Jestli jsem se v tom neztratil, tak to akorát znamená, že pro podmínku INVOICEINCOMINGITEMS.ISCOST = 1 nejde použít žádný index, tak se prochází celá tabulka řádek po řádku.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 2996
  • Karma: 29
    • Verze Delphi: XE7 professional
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #3 kdy: 07-05-2017, 20:29:41 »
To je pravda. Takže pri všetkých poliach typu Boolean, ktoré bežne používam vo WHERE, si môžem tak akurát trhnúť nohou  a budem mať NATURAL.
A kvôli tomu som ich definoval.
Hm, žeby mi mohol pomôcť zložený index? Napr. myBoolean + PrimaryKey. Nie, je to volovina.
« Poslední změna: 07-05-2017, 20:32:02 od Stanislav Hruška »
Delphi XE7, FireBird
Expert na kladenie nejasne formulovaných otázok.

Online vandrovnik

  • Hrdina
  • ****
  • Příspěvků: 286
  • Karma: 17
    • Verze Delphi: 10.2
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #4 kdy: 07-05-2017, 20:33:24 »
To by mělo vyřešit:

CREATE INDEX INVOICEINCOMINGITEMS_ISCOST ON INVOICEINCOMINGITEMS (ISCOST);

Akorát index na takovém poli prý může být někdy ignorován kvůli nízké selektivitě, zatím jsem se s tím ale u sebe nesetkal (používám něco podobného třeba pro rychlý výběr záznamů, které se změnily a je nutné je exportovat).

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 2996
  • Karma: 29
    • Verze Delphi: XE7 professional
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #5 kdy: 07-05-2017, 20:35:59 »
Keď som sa pokúsil vytvoriť index nad ISCOST pomocou DB manažéra, ale mi to odmietol.
Už ho vytvoril. Občas sa sere. Ééééé, často.
« Poslední změna: 07-05-2017, 20:38:08 od Stanislav Hruška »
Delphi XE7, FireBird
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 2996
  • Karma: 29
    • Verze Delphi: XE7 professional
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #6 kdy: 08-05-2017, 14:54:25 »
Prečo mi vypisuje NATURAL pri takýchto jednoduchých select-och?
Kód: [Vybrat]
SELECT METERTYPES.IDMETERTYPES, METERTYPES.TITLE, METERTYPES.PRECISIONS, UNITS.IDUNITS, UNITS.TITLE
FROM METERTYPES
  INNER JOIN UNITS ON (METERTYPES.FKUNITS = UNITS.IDUNITS)
Plan:
PLAN JOIN (METERTYPES NATURAL, UNITS INDEX (PK_UNITS))

Skoro vždy sa jedná o zoznamy (tzv. číselníky). Je tam len niekoľko záznamov, takže o výkon sa nebojím. Ale keď tam mám len PK a názov (string), tak tomu nerozumiem. A index nad názvom nič nemení. Už sa dá vytvoriť len zložený index, čo určite nič nevyrieši a pokladám to za nie dobrý nápad.
Delphi XE7, FireBird
Expert na kladenie nejasne formulovaných otázok.

Online vandrovnik

  • Hrdina
  • ****
  • Příspěvků: 286
  • Karma: 17
    • Verze Delphi: 10.2
Re:Plan NATURAL - ako to čítať a riešiť?
« Odpověď #7 kdy: 08-05-2017, 14:57:33 »
Protože tabulku METERTYPES prochází jednu položku po druhé (což je as OK, ne?), k ní je přes index připojená tabulka UNITS.

 

S rychlou odpovědí můžete používat BB kódy a emotikony jako v běžném okně pro odpověď, ale daleko rychleji.

Upozornění: do tohoto tématu bylo naposledy přispěno před 120 dny.
Zvažte prosím založení nového tématu.

Jméno: E-mail:
Ověření:
Křestní jméno zpěváka Gotta: