how to tune this statement

[vision]

Can any one guide to improve the performance of this statement

START WITH tree_parent = p_parent
CONNECT BY PRIOR tree_nodeid = tree_parent

Thanks in advance.

[slimdave]

Based on this almost total lack of information? Not a chance.

[OracleDoc]

Based on this almost total lack of information? Not a chance.

Dave, are you new??
Of course this fine Gentleman can make that statement run faster. Everyone knows if you add the following to your init.ora all your statements will run faster.

go_fast=true

There's also a hidden parameter that not a lot of people know about called

 _auto_tune_all_sqlstatements=true

[reydp]

vision,
maybe you have to read those STICKY thread posted by marist and tarry first b4 anything else.

[dbasan]

go_fast=true

Read long before of go_fast=3D true!

There's also a hidden parameter that not a lot of people know about called

 _auto_tune_all_sqlstatements=true

Are they version specific.
Can you brief more on these parameters?

[slimdave]

Dave, are you new??
Of course this fine Gentleman can make that statement run faster. Everyone knows if you add the following to your init.ora all your statements will run faster.

go_fast=true

There's also a hidden parameter that not a lot of people know about called

 _auto_tune_all_sqlstatements=true

Quiet, you fool! Are you mad, revealing these secrets?

[dbasan]

Some where i read:

DO NOT use undocumented parameters that begin with underscores.

If you do, you may damage your database.

Many of these parameters cause side effects that if you are not aware of them, could seriously damage your database.

Until a parameter is documented, it is unsafe. Use at your own risk.

Quiet, you fool! Are you mad, revealing these secrets?

Its no longer secret its been Googled.

anybody wanna use read the RED.
Know them, test them and then use at your own risk.

[tamilselvan]

Can any one guide to improve the performance of this statement

SELECT LEVEL AS tra_serial, tree_parent, tree_nodeid,
tree_id, tree_number, itm_crsref, itm_altdesc,
itm_lookupnum, itm_procat, itm_inact, itm_serial,
itm_type, itm_itemno, itm_traflg, itm_descrp, itm_id
FROM itmcat, sys_tree
WHERE itm_inact = 'A'
AND itm_id = tree_nodeid
AND tree_handle = 0
AND LEVEL < 6
START WITH tree_parent = p_parent
CONNECT BY PRIOR tree_nodeid = tree_parent
ORDER SIBLINGS BY tree_number

In general, to tune hierarchy query, an index on "CONNECT BY PRIOR" column is recommended, if the table is big.

Do you have an index on tree_nodeid column?

Can you post the execution plan?

Tamil

[manasa]

Tamil,

Does go_fast=true and _auto_tune_all_sqlstatements=true will work? and what are the side effects.

Undocumented parameters can be known only from you Guru's.

Enlighten the hungry souls...........Slimdave/Oradoc/and others

[slimdave]

Sorry, they are a hoax.