Home » SQL & PL/SQL » SQL & PL/SQL » Complex Sql
Complex Sql [message #242744] Mon, 04 June 2007 23:35 Go to next message
tyagi.pramod@gmail.com
Messages: 1
Registered: June 2007
Junior Member
Please help me to understand what this sql is doing,.

SELECT * 
  FROM (WITH locationnode_map AS (SELECT l_oid
                                       , TO_NUMBER(SUBSTR(scbp,1,INSTR(scbp,'.')-1)) p_parent_oid 
                                    FROM (SELECT pn.oid l_oid,LTRIM(SYS_CONNECT_BY_PATH(pn.oid,'.'),'.') ||'.' scbp 
                                            FROM locationnode pn 
                                           WHERE pn.locationlevel_oid = 552 
                                           START WITH pn.OID in (5000) 
                                           CONNECT BY PRIOR pn.OID = pn.parent_oid
                                         ) 
                                   ORDER BY 2
                                 )         
SELECT /*+ index (sl idx_timenode) */ sl.timenode_oid t_oid
     , SUM(on_order_cost)    result
     , sl.product_oid        p_oid
     , location.p_parent_oid l_oid 
  FROM location         l
     , locationnode_map location
     , slma             sl 
 WHERE sl.timenode_oid in (37, 38) 
   AND sl.product_oid   = 3000310 
   AND sl.location_oid  = l.oid 
   AND location.l_oid   = l.locationnode_oid 
 GROUP BY sl.product_oid
        , location.p_parent_oid
        , sl.timenode_oid
      )

Re: Complex Sql [message #242811 is a reply to message #242744] Tue, 05 June 2007 03:47 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Sorry, I don't think anyone will be able to help without some background about the application and the data model.
Re: Complex Sql [message #243062 is a reply to message #242744] Tue, 05 June 2007 19:57 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Any time that I am trying to figure out what a select statement is doing, I start with the innermost sub-query, then work my way to the outermost query. I don't know what your level of understanding is of things like hierarchical queries and sys_connect_by_path and subquery factoring clauses, so I don't know if the following will be sufficient, but you can research the details in the online documentation.

The innermost sub-query is a hierarchical query that uses sys_connect_by_path:

SELECT pn.oid l_oid,LTRIM(SYS_CONNECT_BY_PATH(pn.oid,'.'),'.') ||'.' scbp 
FROM locationnode pn 
WHERE pn.locationlevel_oid = 552 
START WITH pn.OID in (5000) 
CONNECT BY PRIOR pn.OID = pn.parent_oid


This tells it to go to the locationnode table and, for the rows where the locationlevel_oid is 552, start with the row where oid is 5000 and retrieve subsequent rows where the parent_oid of each row is equal to the oid of the previous row. So, you might get rows something like:

oid          parent_oid
5000         4999
5001         5000
5002         5001


Then it tells it to use sys_connect_by_path on the oid column using a period as a delimiter to get the hierarchical order in one row, so given the above data, you would get something like:

.4999.5000
.4999.5000.5001
.4999.5000.5001.5002

It then uses LTRIM to remove the leading period, and concatenates an ending period, so you get something like:

4999.5000.
4999.5000.5001.
4999.5000.5001.5002.

The next query, wrapped around the innermost sub-query uses:

SUBSTR(scbp,1,INSTR(scbp,'.')-1)

It takes the substring from the first character to the character before the first period, so given the prior sample data, you now get 4999 for each row. This is getting the same thing as connect_by_root, but doing it the hard way, so you have to wonder why.

It then uses to_number to convert the result from character datatype to number datatype.

It then uses "order by 2" to order by this 2nd column of that query. Ordering within this sub-query appears to be a useless step, that does not guarantee the ultimate order of the outer query and may slow the query down.

It then uses a subquery factoring clause, also called a with clause, to assign the table alias of locationnode_map to what has been retrieved so far. The subquery factoring is designed for situations where the same subquery will be used more than once, which it isn't here, so it might just as well use one inline view instead.

It then joins that virtual table to some other tables, with some join and filter conditions, an aggregate (sum) function, and an index hint:

SELECT /*+ index (sl idx_timenode) */ sl.timenode_oid t_oid
     , SUM(on_order_cost)    result
     , sl.product_oid        p_oid
     , location.p_parent_oid l_oid 
  FROM location         l
     , locationnode_map location
     , slma             sl 
 WHERE sl.timenode_oid in (37, 38) 
   AND sl.product_oid   = 3000310 
   AND sl.location_oid  = l.oid 
   AND location.l_oid   = l.locationnode_oid 
 GROUP BY sl.product_oid
        , location.p_parent_oid
        , sl.timenode_oid


That entire result is then wrapped in the outermost query that does nothing more than select * from it, which is usually pointless. However, if you are using a pre-Oracle 9i SQL*Plus to run the query on a 9i database, it may be necessary, in order to use all of the 9i features.

The entire query could be simplified like so:

SELECT /*+ index (sl idx_timenode) */ sl.timenode_oid t_oid
     , SUM(on_order_cost)    result
     , sl.product_oid        p_oid
     , location.p_parent_oid l_oid 
  FROM location         l
     , ( SELECT pn.oid l_oid 
              , CONNECT_BY_ROOT pn.oid scbp 
           FROM locationnode pn 
          WHERE pn.locationlevel_oid = 552 
          START WITH pn.OID = 5000  
        CONNECT BY PRIOR pn.OID = pn.parent_oid
       ) location
     , slma             sl 
 WHERE sl.timenode_oid in (37, 38) 
   AND sl.product_oid   = 3000310 
   AND sl.location_oid  = l.oid 
   AND location.l_oid   = l.locationnode_oid 
 GROUP BY sl.product_oid
        , location.p_parent_oid
        , sl.timenode_oid

Previous Topic: How to save resultset of procedure into a table ?
Next Topic: merge does not use index
Goto Forum:
  


Current Time: Tue Dec 06 04:44:35 CST 2016

Total time taken to generate the page: 0.17101 seconds