Mark D Powell schrieb am 28.03.2007 in
<1175113203.799657.105820_at_y66g2000hsf.googlegroups.com>:
>>> correct. What way I have to go to find out what indexes match to a
>>> query?
Is there an answer to this question? Can I read about it anywhere?
>>>> In general you want to enter the table chain with a filter condition
>>>> on a selective column via an index access and drive from this table
>>>> via the next table that filters out rows through the rest of the set
>>>> of tables.
>>>> In some cases there is no selective filter and few if any rows are
>>>> eliminated by the join so run time is pretty a result of just the
>>>> quantity of the data and the capability of the hardware.
>>> I try to explain the situation (shortcut):
>>> There is a table that contains trees (the real ones).
>>> All of them are situated near a road and for each tree there are 2
>>> responsible offices. A tree can be cut and a row has a person that
>>> worked on it last time. A tree can sometimes stand near a parking. A
>>> tree has a class name
I try to give you an existing query. The query is a preselect for the
user to change the data. It is called 5 times an hour. Before I had the
table NEBENANLAGEN it took between 0 and 5 seconds. Now the query took
up to 2000!!! seconds.
I found no way to make the query faster for last 3 days and nights.
I tried different indexes, to change JOIN- clauses and to update column
'0000000000' to NULL (it became slower).
Hope you can help and I can sleep next night ...
(I wrote comments at the joins and wheres)
Oracle 9.2.0.7i
SELECT
- Do not ask me why I need all the columns, costumer wish
BAUM.CIDNEBENANLAGE "CIDNEBENANLAGE",
BAUM.CIDEXPORTLOG "EXP",
BAUMD.packagewerkzeuge.gibstrasze(
NEBENANLAGE.CSTRASSENKLASSE,
NEBENANLAGE.CSTRASSENNR,
NEBENANLAGE.CSTRASSENZUSATZ) "STRASSE",
NEBENANLAGE.CVNK "VNK",
NEBENANLAGE.CNNK "NNK",
NEBENANLAGE.CABLAENGE "Abschn.-länge",
NEBENANLAGE.CVST "VST",
NEBENANLAGE.CBST "BST",
NEBENANLAGE.CVSTALT "VST (alt)",
NEBENANLAGE.CBSTALT "BST (alt)",
NEBENANLAGE.CLAGE "Lage",
BAUM.CABSTANDMINFBR "Abst. FBR",
BAUMD.packagewerkzeuge.gibjanein(
BAUM.CISTSBAZUSTAENDIG) "SBV zuständig",
BAUMD.packagewerkzeuge.gibjanein(BAUM.CISTGEFAELLT) "gefällt",
BAUM.CID "Objektnummer",
CODE.CDEUTSCH "Baumart (deutsch)",
BAUM.CDATUMBEARBEITUNG "Stand",
NUTZER.CNAME "letzter Bearbeiter",
IU.CNAME "SM Unterhaltung",
IO.CNAME "SM Ort",
NEBENANLAGE.CIDSMORT "ID SM Ort",
NEBENANLAGE.CIDSMUNTERHALT "ID SM Unterhaltung",
BAUM.CIDCODE "ID Baumart",
BAUM.CIDVITALITAET "Vitalität",
BAUM.CIDSCHADENWURZEL "Schaden (W)",
BAUM.CIDSCHADENFUSS "Schaden (F)",
BAUM.CIDSCHADENSTAMM "Schaden (S)",
BAUM.CIDSCHADENKRONE "Schaden (K)",
BAUM.CIDSCHIEFSTAND "Schiefstand",
BAUM.CIDPFLEGENOTWENDIGKEIT "Pflegenotwendigkeit",
BAUM.CIDSCHUTZSTATUS "Schutzstatus",
NEBENANLAGE.CSTRASSENKLASSE "Straßenklasse",
NEBENANLAGE.CSTRASSENNR "Straßennummer",
NEBENANLAGE.CSTRASSENZUSATZ "Straßenzusatz",
BAUM.CASK "CASK",
BAUM.CSTAMMDM "CSTAMMDM",
BAUM.CKRONEDM "CKRONEDM",
BAUM.CHOEHE "CHOEHE",
BAUM.CALTERSSTUFE "CALTERSSTUFE",
BAUM.CDATUMPFLANZUNG "CDATUMPFLANZUNG",
BAUM.CDATUMFAELLUNG "CDATUMFAELLUNG",
BAUM.CDATUMERFASSUNG "CDATUMERFASSUNG",
BAUM.CTK5 "CTK5",
BAUM.CISTHINDERNIS "CISTHINDERNIS",
BAUM.CBEMERKUNG "CBEMERKUNG",
BAUM.CBEMERKUNGINTERN "CBEMERKUNGINTERN",
BAUM.ZKOSTENPFLANZUNG "ZKOSTENPFLANZUNG",
BAUM.ZGEWAEHRLEISTUNG "ZGEWAEHRLEISTUNG",
BAUM.ZPFLEGEINDEX "ZPFLEGEINDEX",
BAUM.ZTK25 "ZTK25",
BAUM.ZCOORDX "ZCOORDX",
BAUM.ZCOORDY "ZCOORDY",
BAUM.ZCOORDXE "ZCOORDXE",
BAUM.ZCOORDYE "ZCOORDYE",
decode(sign(NEBENANLAGE.CVST),-1,'VST<0','')||
decode(sign(
NEBENANLAGE.CABLAENGE-NEBENANLAGE.CVST),-1,'VST<Länge','')||
decode(sign(NEBENANLAGE.CBST-NEBENANLAGE.CVST),-1,'VST>BST','')||
decode(sign(NEBENANLAGE.CBST),-1,'BST<0','')||
decode(sign(
NEBENANLAGE.CABLAENGE-NEBENANLAGE.CBST),-1,'BST<Länge','')
"WARNUNG"
FROM
- 500 000 rows, Main Table
BAUMD.TBBAEUME BAUM
-- 50 rows, most keys about 2%, one about 50% of BAUM
JOIN BAUMD.TSINSTITUTIONEN IU ON IU.CID = BAUM.CIDSMUNTERHALT
-- 50 rows, most keys about 2%, one about 50% of BAUM
JOIN BAUMD.TSINSTITUTIONEN IO ON IO.CID = BAUM.CIDSMORT
-- 500 rows, each key at 0 to 10% of BAUM
JOIN BAUMD.TZCODES CODE ON CODE.CID = BAUM.CIDCODE
-- 100 rows, most keys <2%, 2 key about 40% of BAUM
JOIN BAUMD.TSNUTZER NUTZER ON NUTZER.CID = BAUM.CIDNUTZER
-- actually about 5 rows, later about 20 rows
-- 1 key > 99%, each other 0.02% (now and later) of BAUM
JOIN BAUMD.TBNEBENANLAGEN NEBENANLAGE
ON NEBENANLAGE.CID = BAUM.CIDNEBENANLAGE
WHERE
- '0000000000' is the 99% value
(CIDNEBENANLAGE <> '0000000000')
- about 2% of BAUM
AND (
(NEBENANLAGE.cidsmort = 142108) OR
(NEBENANLAGE.cidsmunterhalt = 142108)
)
AND
- about 2% of BAUM
(NEBENANLAGE.CSTRASSENKLASSE = 'B' AND
NEBENANLAGE.CSTRASSENNR = '006' AND
NEBENANLAGE.CSTRASSENZUSATZ IS NULL )
ORDER BY
STRASSE,
VNK,
NNK,
VST
8 rows are selected
actual Explain Plan
PARENT_ID ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE IO_COST BYTE TEMP_SPACE
-1 0 SELECT STATEMENT 15 316
0 1 SORT ORDER BY 15 316
1 2 TABLE ACCESS BY INDEX ROWID TBBAEUME 3 192
2 3 NESTED LOOPS 13 316
3 4 MERGE JOIN CARTESIAN 10 124
4 5 MERGE JOIN CARTESIAN 8 102
5 6 MERGE JOIN CARTESIAN 6 84
6 7 MERGE JOIN CARTESIAN 4 65
7 8 TABLE ACCESS FULL TBNEBENANLAGEN 2 46
7 9 BUFFER SORT 2 1501
9 10 TABLE ACCESS FULL TSINSTITUTIONEN 2 1501
6 11 BUFFER SORT 4 1501
11 12 TABLE ACCESS FULL TSINSTITUTIONEN 2 1501
5 13 BUFFER SORT 6 2286
13 14 TABLE ACCESS FULL TSNUTZER 2 2286
4 15 BUFFER SORT 8 8514
15 16 TABLE ACCESS FULL TZCODES 2 8514
3 17 INDEX RANGE SCAN XTBBAEUME_TEST_NEBENANLAGEN4 NON-UNIQUE 2
USED INDEX
CREATE INDEX "BAUMD"."XTBBAEUME_TEST_NEBENANLAGEN4"
ON "BAUMD"."TBBAEUME" ("CIDSMUNTERHALT", "CIDSMORT",
"CIDCODE", "CIDNUTZER", "CISTGEFAELLT", "CIDNEBENANLAGE")
TABLESPACE "BAUMD_IDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Is there anyone who can help me?
Many thanks
Andreas Mosmann
--
wenn email, dann AndreasMosmann <bei> web <punkt> de
Received on Thu Mar 29 2007 - 03:40:30 CDT
