Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: O9i: general index question

Re: O9i: general index question

From: Andreas Mosmann <mosmann_at_expires-31-03-2007.news-group.org>
Date: Thu, 29 Mar 2007 10:40:30 +0200
Message-ID: <1175157630.21@user.newsoffice.de>


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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US