Treewalk?

[JDoyle]

I just finished my OCP tuning exam and there was a question about a "Treewalk" in there. What the *&^%$#%$ is a treewalk?
A couple of the answers had something to do with the "start with.......connect by" clause.

[marist89]

In general, a "treewalk" is a method in which to visit all nodes of a binary tree in hierarchical order. Think of it as a "Family Tree" where Grandpa is at the top, Grandpa's children are at the next level, and the grandchildren are at the leaf level.

In the Oracle world, you are definitely looking at a "Connect By" clause in your query. Take for example the typical query for getting information out of a plan_table:
system@dev815nt.us> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)

select lpad(' ',2*(level - 1)) || level || '.' || nvl(position,0) ||
' ' || operation || ' ' || options || ' ' || object_name || ' ' ||
object_type || ' ' || decode(id,0,statement_id || 'Cost = ' || Position)
|| ' ' || object_node "Query Plan"
from plan_table
start with id=0 and statement_id = '&Statement_id'
connect by prior id = parent_id
and statement_id = '&Statement_id'

In this statement, there is a parent-child relationship between statement_id and parent_id. The query says "give me all the children and all the children's children, etc., and start at the top."

The "start with id" portion says "start at this point" (the top).

The "connect by prior" tells Oracle how the parents & children relate.

[Kishore]

Hi

Can you suggest me any good books to have fair idea on how the relation work and all that.