We help IT Professionals succeed at work.

the query is taking lot of time, how can i tune it

pardeshirahul
on
table access is full and it  goes in infinite loop
xx.sql
xxpq-zip-range.sql
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What is the SQL?

I assume this is related to your other question that you seem to have incorrectly closed.

As pointed out by the other Expert, sdstuber, the SQL I posted has an issue with larger amounts of data.

His should run perfectly fine.  He even asked why you chose the answer you did.

Try the SQL he posted:
http://www.experts-exchange.com/Database/Oracle/10.x/Q_27424203.html?#a37058669

Author

Commented:
select /*+ INDEX(xxpq_xone) */ to_char(min_zip+level-1,'000') new_zip, zone
from xx
connect by level <= max_zip-min_zip+1
order by 1

Author

Commented:
the query runs fine for less number of records but running infinitely for the records in table xx
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I think you are running into the issue posted in the other question with my SQL.

See the SQL referenced in the link above from your previous question posted by sdstuber and see if that will run faster for you.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
using same logic as posted in previous question....


SELECT COLUMN_VALUE new_zip, zone
  FROM xx,
       TABLE(SELECT     COLLECT(LEVEL + min_zip - 1)
                   FROM DUAL
             CONNECT BY LEVEL + min_zip - 1 <= max_zip);

Author

Commented:
problem resolved
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
problem resolved  - is this because your OTHER question is answered and this one is just a duplicate?
if so, close the other question and request delete of this one.


or, if this one has something special about it and has been answered?  If so, close this question and please return to the other one so it can be resolved.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
when I say "close" a question  I mean you (since you're the asker) select the answer (or answers) that worked, assign points and a grade.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.