SQL Tuning

Hi,
I am using the below mentioned SQL. Its taking too much of time Run.. COuld anybody help me in Tuning this SQL.
This is used to fecth the navigation of a PAge.. i have mentioned the Indexes also.. NEED Help
************************************************************************************************
SELECT
DISTINCT RTRIM(REVERSE (SYS_CONNECT_BY_PATH(REVERSE (TRANSLATE(PORTAL_LABEL,'>',' ')), ' > ')), ' > ')  PATH
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
AND PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT' START WITH PORTAL_URI_SEG2 = VCHR_EXPRESS
CONNECT BY PRIOR
PORTAL_PRNTOBJNAME = PORTAL_OBJNAME
***********************************************************************************************

PORTAL_NAME      Asc      1
PORTAL_REFTYPE      Asc      2
PORTAL_URL_CHKSUM      Asc      3
PORTAL_NAME      Asc      1
PORTAL_REFTYPE      Asc      2
PORTAL_PRNTOBJNAME      Asc      3
PORTAL_SEQ_NUM      Asc      4
PORTAL_LABEL      Asc      5
VERSION      Asc      1
PORTAL_LINK_PORTAL      Asc      1
PORTAL_LINKOBJNAME      Asc      2
PORTAL_NAME      Asc      3
PORTAL_REFTYPE      Asc      4
PORTAL_URI_SEG2      Asc      1
PORTAL_URI_SEG1      Asc      2
PORTAL_URI_SEG3      Asc      3
PORTAL_URI_SEG4      Asc      4
PORTAL_NAME      Asc      1
PORTAL_REFTYPE      Asc      2
PORTAL_OBJNAME      Asc      3
TechygalAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
I assume you mean 8.1.7,  there is no 8.7  version of the db.

you should double check your version   (select version from v$instance)
that function is not supposed to exist.

Even if you somehow have it, you shouldn't rely on it as it's undocumented.

If, it turns out you are actually running on 9i or above where sys_connect_by_path is documented
or if you really want to risk using it in 8i then look at what your query returns without the connect by.

How many rows is that?  Should you have a "start with" clause  if not you can end up with cases like this.

Assuming 1 is a parent of 2 which is a parent of 3 you'll get results like this...

1
2
3
2
3
3
0
 
sdstuberCommented:
the sys_connect_by_path function doesn't exist in 8i,  so the problem shouldn't be so much about performance as it simply won't work at all
0
 
TechygalAuthor Commented:
Well I am using Oracle 8.7.1.0.0 .  And the function is present..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.