SQL & PL/SQL

Hi,

I have a hierarchical query which takes 2 seconds to execute. I need to get this down to milli seconds.

The table has around 8000 records. The query will never return more than 20 or so records. There is only ever 2 levels to the query.

I am quite surprised at this because it is a very simple query no table joins etc and I would have though 8000 records was nothing for Oracle.

Select id, parent_id, col1, col2, col3, col4, col5, col6
from my_table
where id=500
start with parent_id is null
connect by prior id = parent_id;

I have even tried initializing the start with say with 0 and making it a not null column...... indexing the columns used in the start with + connect by

I have tried various indexing stratergies. Does anyone have any similar experience? I am using Oracle 9i.

Thanks in advance