?
Solved

SQL Tuning

Posted on 2008-11-18
3
Medium Priority
?
384 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:Techygal
  • 2
3 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22988421
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
 

Author Comment

by:Techygal
ID: 22988544
Well I am using Oracle 8.7.1.0.0 .  And the function is present..
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1500 total points
ID: 22988887
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question