Home » RDBMS Server » Performance Tuning » Changes to the query (Oracle 10.2.0.1.0)
Changes to the query [message #390381] Fri, 06 March 2009 03:20 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi,
I have a code as shown below:

SELECT level_num,
       parent_row_num, 
       leaf_node, 
       amount 
FROM   table t1 
WHERE  id_num = 101066 
       AND ((t1.level_num = 3) 
             OR EXISTS (SELECT NULL 
                        FROM   table t2 
                        WHERE  t2.id_num = t1.id_num 
                               AND t2.parent_row_num = t1.row_num)) 
CONNECT BY PRIOR row_num = parent_row_num 
                 AND id_num = 101066 
START WITH level_num = 1 
           AND id_num = 101066 


Can anyone pls help me knowing whether i can further tune the query. The paln looks as shown below:

Execution Plan
----------------------------------------------------------
Plan hash value: 475934742

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     2 |    42 |     2   (0)| 00:00:01 |
|*  1 |  FILTER                        |                |       |       |            |          |
|*  2 |   CONNECT BY WITH FILTERING    |                |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | TABLE 		|       |       |            |          |
|*  4 |     INDEX RANGE SCAN           | IDT_TABLE      |     3 |    24 |     1   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                |                |       |       |            |          |
|   6 |     BUFFER SORT                |                |       |       |            |          |
|   7 |      CONNECT BY PUMP           |                |       |       |            |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| TABLE 		|     2 |    42 |     2   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | IDT_TABLE      |     2 |       |     1   (0)| 00:00:01 |
|* 10 |    TABLE ACCESS FULL           | TABLE 		|     2 |    42 |     2   (0)| 00:00:01 |
|* 11 |   INDEX RANGE SCAN             | IDT_TABLE      |     1 |     8 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID_NUM"=101066 AND ("T1"."LEVEL_NUM"=3 OR  EXISTS (SELECT 0 FROM
              "TABLE" "T2" WHERE "T2"."PARENT_ROW_NUM"=:B1 AND "T2"."ID_NUM"=:B2)))
   2 - filter("LEVEL_NUM"=1 AND "ID_NUM"=101066)
   4 - access("ID_NUM"=101066 AND "LEVEL_NUM"=1)
       filter("LEVEL_NUM"=1)
   9 - access("ID_NUM"=101066 AND "PARENT_ROW_NUM"=NULL)
  10 - access("ID_NUM"=101066)
  11 - access("T2"."ID_NUM"=:B1 AND "T2"."PARENT_ROW_NUM"=:B2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        820  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

Thanks,
Senthil

[Updated on: Fri, 06 March 2009 03:24]

Report message to a moderator

Re: Changes to the query [message #390513 is a reply to message #390381] Fri, 06 March 2009 16:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Trying to read your query, it looks as though you are interested in branch nodes in the tree plus leaf nodes that have level_num = 3

The CONNECT BY goes to a lot of effort to ensure that you have a connected tree. If you are sure that there are no orphans in the tree (i.e. every node is accessible via parent-child joins starting from level_num = 1), then there is probably a much simpler way.
SELECT level_num,
       parent_row_num, 
       leaf_node, 
       amount 
FROM   table t1 
WHERE (id_num, row_num) IN (
    SELECT id_num, row_num
    FROM   table
    WHERE  level_num = 3
    AND    id_num = 101066
    UNION ALL
    SELECT id_num, parent_row_num
    FROM   table
    WHERE  id_num = 101066
)


Ross Leishman
Re: Changes to the query [message #390527 is a reply to message #390513] Fri, 06 March 2009 23:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
also consider constructing indexes in order to achieve index only access. This will cause the steps (table access by rowid) to go away. It sometimes can cause siginificant improvement, sometimes not.

Kevin
Re: Changes to the query [message #390563 is a reply to message #390381] Sat, 07 March 2009 09:03 Go to previous message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
From the execution plan, the time is 00:00:01. How long does this sql can be done in really run?
Regards,
Alex
Previous Topic: PLSQL Query Tuning Suggestion/Feedback needed
Next Topic: Oracle Performance Tuning of a view
Goto Forum:
  


Current Time: Sun Dec 04 10:22:24 CST 2016

Total time taken to generate the page: 0.07298 seconds