Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: Optimizing a query

    Hi there,

    Is there a way to optimize this query??

    DELETE FROM prop
    WHERE v_id IN (SELECT version.v_id
    FROM obj o1, obj o2, version
    WHERE o1.o_id = 10
    AND o2.o_pth || '/' LIKE o1.o_pth || '/%'
    AND (version.o_id = o2.o_id OR version.o_id = o1.o_id)
    )
    AND pt_id <> 1
    AND pt_id <> 2
    AND pt_id <> 3;

    The o_id and pt_ids values are all variables.

    The explain plan:

    Operation Object CPU_COST IO_COST OPTIMIZER
    DELETE STATEMENT () 1050 CHOOSE
    &nbsp;&nbsp;DELETE () PROP
    &nbsp;&nbsp;&nbsp;&nbsp;HASH JOIN (SEMI) 1050
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (FAST FULL SCAN) PK_PROP 306 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VIEW () VW_NSO_1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CO NCATENATION ()
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (UNIQUE SCAN) PK_OBJ 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (RANGE SCAN) IX_VERSION 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;INDEX (FAST FULL SCAN) IX_OBJ_O_PTH ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (UNIQUE SCAN) PK_OBJ 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (FULL SCAN) IX_VERSION 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;INDEX (UNIQUE SCAN) PK_OBJ ANALYZED

    Thanks in advance

    Cheers

    Kim Tiedemann

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what columns are in this index?
    PK_PROP

    also, you could try:
    AND pt_id NOT IN (1,2,3)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    11
    Thanks for your reply...

    PK_PROP is the primary key of the prop table and consists only of p_id.

    No - I have not tried using not in () but it seems to me, that the problem is more related to the WHERE v_id IN () part of the statement and not the pt_id checks


    Cheers

  4. #4
    Join Date
    Jan 2004
    Posts
    11
    Im really wondering why it doesn't use the index I have made on the prop table for the v_id column (IX_PROP_V_ID)... It has been analyzed but the optimizer doesn't choose to use it...

    Kim

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Optimizing a query

    Does
    WHERE o2.o_pth || '/' LIKE o1.o_pth || '/%'

    mean that o1.o_pth starts with o2.o_pth?
    If yes, you can do

    WHERE INSTR(o1.o_pt,o2.o_pth) = 1


    You can try the inner query as below (did not check the syntax & performance, check it out, if it helps)

    Code:
    ---------approach1-----------
    SELECT version.v_id
    FROM version, 
         (select o2.o_id o_id_2, o1.o_id o_id_1 
         from obj o1, obj o2 
         where o1.o_id = 10 
         AND INSTR(o1.o_pth,o2.o_pth) = 1) T1
    WHERE version.o_id = T1.o_id_1 OR version.o_id = T1.o_id_2
    
    ---------approach1-----------
    select v_id from version where o_id=10
    UNION
    select v_id from version where o_id in (select o2.o_id 
                                            from obj o2, (select * from obj where o_id=10) o1 
                                            where instr(o1.o_pth,o2.o_pth)=1                                        
                                            )
    Originally posted by tiede
    Hi there,

    Is there a way to optimize this query??

    DELETE FROM prop
    WHERE v_id IN (SELECT version.v_id
    FROM obj o1, obj o2, version
    WHERE o1.o_id = 10
    AND o2.o_pth || '/' LIKE o1.o_pth || '/%'
    AND (version.o_id = o2.o_id OR version.o_id = o1.o_id)
    )
    AND pt_id <> 1
    AND pt_id <> 2
    AND pt_id <> 3;

    The o_id and pt_ids values are all variables.

    The explain plan:

    Operation Object CPU_COST IO_COST OPTIMIZER
    DELETE STATEMENT () 1050 CHOOSE
    &nbsp;&nbsp;DELETE () PROP
    &nbsp;&nbsp;&nbsp;&nbsp;HASH JOIN (SEMI) 1050
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (FAST FULL SCAN) PK_PROP 306 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VIEW () VW_NSO_1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CO NCATENATION ()
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (UNIQUE SCAN) PK_OBJ 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (RANGE SCAN) IX_VERSION 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;INDEX (FAST FULL SCAN) IX_OBJ_O_PTH ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;NESTED LOOPS () 2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (UNIQUE SCAN) PK_OBJ 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INDEX (FULL SCAN) IX_VERSION 1 ANALYZED
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;INDEX (UNIQUE SCAN) PK_OBJ ANALYZED

    Thanks in advance

    Cheers

    Kim Tiedemann
    Oracle can do wonders !

  6. #6
    Join Date
    Jan 2004
    Posts
    11
    Hi there...

    The first approach really helped and reduced the query time a lot (from approximately 20 sec to 1 sec)

    Thanks for your help

    Cheers

    Kim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •