Connect by

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Sat, 21 May 2005 7:18 pm

I have a query that has a join and uses the connect by. Only with this connect by, it uses only the PK of a table and is full in the other. I'vê tried to create indexes, but nothing resolves.

The query is more or less this:
Code: Select all
select *
from tabelaA, tabelaB
where tabelaA.id = tabelaB.id
start with tabelaB.id = 1
connect by prior tabelaB.id = tabelaB.id_filho
has anyone seen this?

Thank You Marcia
marciabm
Location: São Caetano do Sul - SP

Poston Tue, 14 Jun 2005 11:16 am

Strange ...
Did a test here at SCOTT and also made FULL, even though I'm filtering by PK. see example below:
Code: Select all
SQL> COL ENAME FORMAT A25
SQL> COL PATH FORMAT A35
SQL>
SQL> select 
  2    rpad(' ',level*2)||' '|| ename   ENAME
  3  , SYS_CONNECT_BY_PATH(ename, '/')  PATH
  4  , level
  5  from SCOTT.emp
  6  WHERE EMPNO >7600
  7  connect by prior empno=mgr
  8  start with ename='KING'
  9  /

ENAME                     PATH                                    LEVEL
------------------------- ----------------------------------- ---------
   KING                   /KING                                       1
       SCOTT              /KING/JONES/SCOTT                           3
         ADAMS            /KING/JONES/SCOTT/ADAMS                     4
       FORD               /KING/JONES/FORD                            3
     BLAKE                /KING/BLAKE                                 2
       MARTIN             /KING/BLAKE/MARTIN                          3
       TURNER             /KING/BLAKE/TURNER                          3
       JAMES              /KING/BLAKE/JAMES                           3
     CLARK                /KING/CLARK                                 2
       MILLER             /KING/CLARK/MILLER                          3

10 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     CONNECT BY (WITH FILTERING)
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'EMP'
   5    3         TABLE ACCESS (BY USER ROWID) OF 'EMP'
   6    2       NESTED LOOPS
   7    6         BUFFER (SORT)
   8    7           CONNECT BY PUMP
   9    6         TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        422  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
IE ... the question remains ...:-(
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 05 Jul 2011 1:41 pm

How to separate the SYS_CONNECT_BY_PATH column results in columns?

P.e.

The return below:/KING/JONES/SCOTT/ADAMS separated into columns hier1 | hier2 | hier3 | hier4 | ...
KING |JONES |SCOTT |ADAMS thanking you in advance.
sp66d_rac6r
Location: Sorocaba-SP

Poston Tue, 05 Jul 2011 2:01 pm

I did a view like this a few days ago, but I used a function to separate:
Code: Select all
SELECT
  xg_pkg_qp_001.fnc_elemento(a.sep, 2,'|') grupo1
, xg_pkg_qp_001.fnc_elemento(a.sep, 3,'|') grupo2
, xg_pkg_qp_001.fnc_elemento(a.sep, 4,'|') grupo3
, xg_pkg_qp_001.fnc_elemento(a.sep2,2,'|') flex1
, xg_pkg_qp_001.fnc_elemento(a.sep2,3,'|') flex2
, xg_pkg_qp_001.fnc_elemento(a.sep2,4,'|') flex3
FROM
  (
  SELECT
    a.flex_value
  , a.parent_flex_value
  , LEVEL                                  lev
  , a.summary_flag
  , SYS_CONNECT_BY_PATH(description,  '|') sep
  , SYS_CONNECT_BY_PATH(a.flex_value, '|') sep2
  FROM apps.fnd_flex_value_children_v  a
  WHERE a.flex_value_set_id=1013810
  START WITH a.parent_flex_value='1000000'
  CONNECT BY PRIOR a.flex_value  = a.parent_flex_value
  )                               a
WHERE lev in (3)
ORDER BY 3,4,5
I used a function called element: viewtopic.php?t=1653
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


        Return to PL/SQL

        Who is online

        Users browsing this forum: No registered users and 9 guests