SQL & PL/SQL :: Hierarchical Query With Many-to-many Dependencies?
Apr 22, 2010
I have a hierarchical data structure where a child can have many parents, and a parent can have many childs.See the attached file hierarchy_iliustration.jpg. This example has 4 hierarchy levels, in real problem there can be unlimited number of levels.I want to write a SQL query that lists all indirectly dependent child nodes for a given parent node.
Test structure for example attached:
CREATE TABLE TEST.NODE_T
(
ID NUMBER PRIMARY KEY,
TEXTAS VARCHAR2(254)
);
I want to display result with respective levels. for example p21 ,p30 are coming under first level . p22,p25 ,P31,P32are 2nd level. p23,p24,p26,p27 are 3rd level p28,p29 are FOURTH level item_id's.
Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.
How to count siblings in a hierarchical query? I'm trying to get a listing of employees
SELECT LEVEL, last_name||', '||first_name AS Manager, count(employee_id) FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id GROUP BY level
This returns 4 levels. I'm wanting to add up the number of siblings under the level 2 instead of listing them all.
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows: Lvl KeyParKey hasCh 1k101 2k34k10 2k22k11 3k56k220 3k109k221 4k61 k1090 3k67k220 2k24k10 1k200 1k301 2k13k30 2k52k30 2k35k30 2k13k30 1k401 2k11k40
I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i error i am getting is connect by nocycle prior account_code=parent_code * ERROR at line 4: ORA-00920: invalid relational operator
Table
Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19)) insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000') insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000') insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')
select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE) connect_by_iscycle from chart_of_account start with Substr(account_code,13,7)='0000000' connect by nocycle prior account_code=parent_code
SELECT v.key$ FROM VERSION_TABLE v, DOCUMENT_TABLE d, CLASS_TABLE z WHERE v.documentKey = d.key$ AND d.classKey = z.key$ AND z.key$ IN (SELECT zz.key$ FROM CLASS_TABLE zz START WITH zz.name = 'esDTTemplate' CONNECT BY PRIOR zz.key$ = zz.parentKey) AND v.ESGROUP = 'SearchOperatorsMapping' ORDER BY d.name
Now I noticed that the subquery is never used to seed the join: indexes - if any - are used. Otherwise a full table scan is performed.In the example - if ESGROUP is indexed, then it's chosen to start the join evaluation. If not, a full table scan is performed.Is there any way to suggest to the optimizer to use the subquery in case there are no indexes - as a fallback ?
In the above example where VERSION_TABLE contains nearly two million records, the no index solution takes 60 secs. vs. less than 1 sec. in the index case.Wrapping the hierarchical query in a inline view leads to same result.
PS: the execution plan (without index) is: ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 9 (100)| | | 1 | SORT ORDER BY | | 1 | 171 | 9 (23)| 00:00:01 | |* 2 | HASH JOIN SEMI | | 1 | 171 | 8 (13)| 00:00:01 |
I am having to pull out all the dependencies for a column as below.
When I select a column in a table, it should list
1. All Views that use this column and table 2. All Stored procs that use this column and table 3. All Functions that use this column and table 4. All Pl/SQL scripts that use this column and table.
Is there a easy way of getting this information from the Oracle dba tables?
Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.
Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.
find out the list of referenced objects which are dependent on INVALID objects.
I wrote the below query to get the referenced objects which depends on invalid objects.
select owner,name,type, referenced_owner,referenced_name, referenced_type, referenced_link_name , dependency_type from dba_dependencies where type not in ('JAVA CLASS') AND referenced_type NOT IN('JAVA CLASS') AND (NAME, TYPE) IN ( select object_name, OBJECT_TYPE from dba_objects WHERE STATUS='INVALID')
And the output is something like below -
Sr. No OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE 1PUBLICVEHICLE_INSPECTION_REQUESTSYNONYMINSBIN$Uu99fysmRj6Ppn2QppCTWg==$0TABLEHARD 2PUBLICPRODUCT_HIERARCHY_MAP_TEMPSYNONYMCONFSYSBIN$ndGddLcKSDWRwsn5g91Rcg==$0TABLEHARD 3PUBLICACPKG_SUB_RECEIPTINGSYNONYMINSACPKG_SUB_RECEIPTINGPACKAGEHARD
[code].....
Our requirement is to drop all the invalid objects. But we need to know whether it effecting to any other valid objects?Do any valid objects gets INVALID after dropping of the INVALID objects. We need to know the hierarchy of it.
In a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
>select level ,empno,ename,mgr from emp connect by prior empno=mgr start with mgr is null;
Output:- > LEVEL EMPNO ENAME MGR ----- ---------- ---------- ---------- 1 7839 KING 2 7566 JONES 7839 3 7788 SCOTT 7566 4 7876 ADAMS 7788 3 7902 FORD 7566 2 7698 BLAKE 7839 3 7499 ALLEN 7698 3 7521 WARD 7698 3 7654 MARTIN 7698 3 7844 TURNER 7698 3 7900 JAMES 7698
LEVEL EMPNO ENAME MGR ----- ---------- ---------- ---------- 2 7782 CLARK 7839 3 7934 MILLER 7782
Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.
create table test_circular_data(c1 varchar2(10),c2 varchar2(10)); insert into test_circular_data values ('c1','l2'); insert into test_circular_data values ('c1','l3'); insert into test_circular_data values ('c3','l3'); insert into test_circular_data values ('c4','l3');
commit;
There is a circular relation between columns c1 and c2, so what I'm trying to retrieve is something like that :
c1--> l2 --> l3 --> c3 --> c4
The steps to get that result is :
1.- c1 related to l2 : c1-->l2 2.- c1 related to l3 : c1-->l2-->l3 3.- l3 in the list and related to c3 : c1-->l2-->l3-->c3 4.- l3 in the list and related to c4 : c1-->l2-->l3-->c3-->c4
I have a table which contains the connectivity details. It is telecom data. The table contains the relation between the point features(Ex: Terminal) and linear features(Ex: Cable)
I have a table (Parent - Child).There is a requirement to maintain this table, thats the hierarchy of the oraganisation.So, every quater they will be updating the table. They will be importing the data through an excel and in that excel there are 3 action items,
For Update:What all validation can come for an updation of an hierarchical data in general.Like = how to derive the level value at database side when the id is updated to some other level.= How to maintain the relation. A -> B -> D ( A is the grand parent here).A -> Ceg: if B is updated as parent node of A, then we should throw error (cyclic data). Any more validations for hierarchical data
I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.
So each Level(L1 ..L4) has zero or many child levels which further has more levels.With out using PL/SQL how can we write a Select query to give me a distinct of all children, all the way to the lowest level (L4).Example: give me all the children where L1 = 3.Result: 31, 32, 33, 311, 322, 333, 3111, 3222, 3333Is it possible to write such a query or am I asking too much logic out of a select and should go with PL/SQL.
I am using oracle forms 9i. I have to create a Hierarchical Tree and attach checkbox at its node,so that user can select which tree he want in its further processing.
