Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Implementing START WITH, CONNECT BY Features

 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to write code that works on Oracle and other databases - MSSQL for now.

I need to scan thru a huge tree structure represented in table - self referencing table. For each node scans all the way up the hierarchy until the root node and do something on each node traversed.

Now the problem. The table is huge, I cannot afford to issue many jdbc SELECT's. Using Oracle, there is START WITH and CONNECT BY to get a hierarchy by 1 SELECT so that I can work on the in-memory image of the ResultSet. But, for MSSQL, I don't think there is a similar clause. Does it means I need to issue SELECT everytime I traverse up one node in the hierarchy?

Is there a better strategy if there is no START WITH, CONNECT BY support?
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alec,
As far as I know, Oracle is the only database with tree-traversing ability (via the "START WITH" and "CONNECT BY" clauses).

In any case, I don't believe that standard SQL includes such capability, so if any other databases have something equivalent to Oracle's "START WITH" and "CONNECT BY" clauses, it too would be proprietary.

So if you were looking for some database independent JDBC code, it looks like you may be up sh*t creek (as Jake Blues said to the Penguin)

Good Luck,
Avi.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Alec Lee:
The table is huge, I cannot afford to issue many jdbc SELECT's.

You may want to revisit this assumption. Does the table have an index on what your are searching for? How database intensive is the query? How much data does it return?

I bring this up because dealing with millions of rows isn't an inherent problem. It's all in how you deal with them.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic