Sam,

I have Cost Based Optimizer. I tried to rearrange WHERE
clause, but explain plan doesn't change. I can not use
where rownum < xx to limit #Ofrows from big table, because
I have to get all rows and sort in required order, before
selecting rows to display on web. I tried to run this
query for 20 sessions, and maximumu uptime on solaris was 3.7. For 50 sessions It went upto 7.4. So, I think with
lots of users my m/c is going to crash. V$SQLAREA has following statistics.

DISK_READS BUFFER_GETS ROWS_PROCESSED EXECUTIONS FIRST_LOAD_TIME
---------- ----------- -------------- ---------- -------------------
DISK_READS/EXECUTIONS BUFFER_GETS/EXECUTIONS ROWS_PROCESSED/EXECUTIONS
--------------------- ---------------------- -------------------------
211794 1175421 4075 163 2001-03-20/12:23:51
1299.34969 7211.17178 25

Do you think I have very high Disk reads and buffer gets?
================================================
tamilselvan:
I have indexes on both parent_id and category_id in
TCATEGORY table. Also, there is index on list_id and
category_id column of TLISTING.

=========================

thanks for your help...

np70