q
Date: 28 Jan 1995 15:55:47 GMT
Message-ID: <3gdpe3$c4i_at_eccdb1.pms.ford.com>
X-Newsreader: TIN [version 1.1 PL6]
I got few performace related questions
I got one very big table BUILDPART (around 5 million records ). for simplicity assume it contain three attributes
empno number not null PRIMARY KEY
mgrno number
empname varchar2
emploc varchar2
user wants to see the data in hirarchial order i.e with in each mgr (parent) user wants to see the data in order of empname && records with the same empname is to appear once.
IN this table mgrno is 0 for most of the records. if write the query like
select mgrno,empno,empname
from emp_mgr
where
emplocation in ('x','y','d')
and few_other_conditions
connect by prior empno = mgrno
group by mgrno,empno,empname
1 I don't get the data in order within each mgrno(parent) 2 optmizer uses the connect by as a filter and use index on mgrno
which is as bad as doing a full table scan of for each record
It doesn't use any other index on any attrbute in where condition.
3
If i use group by condition i don't get the data in hierarchial order of
empno within each mgrno
write now i am using user_exits for manipulating the data but for sorting i have to fetch around 100000 records from database ,sort it & then display 20 records at a time on forms3.0 screen.
Is there any way i can do it by just using sql. Received on Sat Jan 28 1995 - 16:55:47 CET