| Hierarchial Query [message #21294] |
Thu, 25 July 2002 00:25  |
Rajesh Joshi
Messages: 12
Registered: July 2002
|
Junior Member |
|
|
I have the following query which i am executing on 2 different databases via a java program.
SELECT A.TAXONOMYID, A.PARENTID, B.COUNTTYPEID, B.PARTICIPANTID, B.COUNT
FROM
(SELECT TAXONOMYID, PARENTID FROM
EQUIPMENTTAXONOMYNODE
START WITH PARENTID IS NULL
CONNECT BY PRIOR TAXONOMYID = PARENTID ) A,
TAXONOMYCOUNT B
WHERE A.TAXONOMYID = B.TAXONOMYID(+)
The relevant tables (EQUIPMENTTAXONOMYNODE,TAXONOMYCOUNT)
have the same data.Only 1 extra row is present in the TAXONOMYCOUNT Table in 1 of the databases.
I am getting a null pointer exception for one of the databases.
On analysing the output on sql*plus for both the databases, i observed the order in which data is being fetched is different .
In 1 database it is ordered by taxonomyid and in the other database(the one which gives an error) it is in random order.
On adding the "order by 1" clause in the query , i ran the program again in the database which gave error and
the program started working fine.
Since it is a hierarchial query , i am assuming the order in which data is fetched is crucial.
My question is :
Is there any database parameter which decides how data is fetched from the tables ?(if query does not have order by clause).
Any help on this will be highly appreciated.!!
|
|
|
|
| Re: Hierarchial Query [message #21303 is a reply to message #21294] |
Thu, 25 July 2002 07:13  |
hi
Messages: 15
Registered: February 2002
|
Junior Member |
|
|
|
Yes, data is fetched is on different criteria. Since both are 2 different DB's, each DB can have its own cost based, rule based optimizations in querying. Also some tables maybe analyzed. Check out.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
