| How to get X to Y rows without using "distinct" or "group by" [message #39684] |
Fri, 02 August 2002 02:37  |
T.Manikandan
Messages: 6
Registered: April 2002
|
Junior Member |
|
|
Hi,
I have a table emp as below :
col1 col2 col3
AA A 1
AA B 2
BB C 3
BB D 4
BB E 5
CC F 6
This table may contain several million records.
I need to get a result as rows 1-200,201-250,.. or even
250 - 200 (that is in reverse order) with distinct of col1 and with some where clauses.
Actually i constructed a query like this:
"select col1 from (select distinct col1,rownum as n from emp where col2='B' and col3='5') where n between 200 and 300" .
In this query i faced 2 problems.
1.As i am using distinct option in this query, it took too much time to get the result set in java.
2. As i need to get records in reverse order also (ie 250-200. 250th record should be at the top and 200th record should be at bottom ), i was unable to construct that.
Note that this table is already in order by of col1 and i need records from 1-200 or 200-250 only.Using this, i constructed the query without using "distinct" option. (ie) for rows 200 -300
"select col1 from (select col1,rownum as n from emp where col2='B' and col3='5') where n > 200" .
As a result of this i got the resultset very quickly and in java program itself i compared the previous index results with the current index and if they are not eqaul, i added the results to the "ArrayList" in java.This is in a while loop and it continues upto the required size of "ArrayList".
Using the above procedure i gained some more improvements in the query processing.In this also, i don't know how to get the reverse order.
I think if we construct the above procedure in a "sql procedure", then we can gain some more improvements.
My questions :
1. How to write a sql procedure for the above thing?
2. How to get the reverse order?
Any help is appreciated..
bye,
Manikandan.
|
|
|
|
| Re: How to get X to Y rows without using "distinct" or "group by" [message #39700 is a reply to message #39684] |
Fri, 02 August 2002 09:59  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Since you are including rownum in your inline view, the DISTINCT is unnecessary because rownum will already make each row distinct/unique. However, if based on your data, col1 can have the same value for multiple rows for a specific value of col2/col3:
col1 col2 col3
1 B 5
1 B 5
2 B 5
then you will need a DISTINCT at some point.
As far as the descending order, that is easy to handle. This example assumes we do NOT need a DISTINCT clause (all values of col1 are already distinct for a specific col2/col3 pair).
select col1
from (select col1, rownum rn
from emp
where col2 = 'B'
and col3 = '5')
where rn between 200 and 250
order by rn desc;
One issue here is that for 'x to y' type queries, you need to make sure that the inline view is returning the result in the same order everytime. That means an order by clause in the inline view, but you would have to determine what that should be.
If you need the DISTINCT, the query could be something like:
select col1
from (select col1, rownum rn
from (select distinct col1
from emp
where col2 = 'B'
and col3 = '5'))
where rn between 200 and 250
order by rn desc;
Writing a procedure to return the results will not improve performance, but I generally like to keep DML statements out of the client app and in the database. Here is a link for how to return a result set from a procedure:
http://osi.oracle.com/~tkyte/ResultSets/index.html
|
|
|
|
| Re: How to get X to Y rows without using "distinct" or "group by" [message #39711 is a reply to message #39684] |
Sat, 03 August 2002 03:28 |
T.Manikandan
Messages: 6
Registered: April 2002
|
Junior Member |
|
|
Dear Todd,
Thanks a lot for your kind reply and it helped us
a lot to solve the problem.
I have one more doubt.
I want to create a materialized view for 2 tables.
Table1 :
col1 col2
AA 11
AA 88
BB 11
CC 22
Table2 :
col1 col2
AA 44
DD 77
EE 00
I used the query as : "create materialized view testview as select distinct col1 from table1 union select distinct col1 from table2"
After this testview contains :
col1
AA
BB
CC
AA
DD
EE
My requirement view should be like this :
col1 col2
AA 00
BB 11
CC 22
DD 44
EE 77
88
For this how can i write the query.
bye,
Manikandan.
|
|
|
|
| Re: How to get X to Y rows without using "distinct" or "group by" [message #39715 is a reply to message #39684] |
Sun, 04 August 2002 20:29 |
T.Manikandan
Messages: 6
Registered: April 2002
|
Junior Member |
|
|
Dear Todd,
Both the col1 and col2 values are derived from the 2 tables as distinct column value from their corresponding columns.
For this i used the query as : "create materialized view testview as select distinct col1,col2 from table1 union select distinct col1,col2 from table2".
But this causes duplication of entries in the columns of view.
My requirement is that both the columns in the testview should contain distinct values and it should not contain any null values.
In the same manner, i need to construct another view
which should be as follows :
consider a another table
table3 :
col1 col2 col3
AA 11 t
AA 77 y
RR 00 i
my view should be contain 3 columns.First column of the view should contain the distinct values from the Ist column of table1,table2,table3. similarly second column of the view should contain the distinct values from the IInd column of table1,table2 and table3.
But the third column of the view should contain the distinct values from the IIIrd column of table3 only.
Thanks a lot for your kind reply,
Manikandan.
|
|
|
|
| Re: How to get X to Y rows without using "distinct" or "group by" [message #39725 is a reply to message #39684] |
Mon, 05 August 2002 07:51 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The reason I don't understand your desired result set is because you gave this example (data for only AA shown):
Table1:
AA 11
AA 88
Table2:
AA 44
Result:
AA 00
I had thought that your result should be like:
Your SQL for the materialized view looks fine. If you are seeing duplicates in the result, then I would suspect that there are some trailing spaces on some of your data. The UNION operator in the query would have removed the duplicates if they were indeed exactly equal.
|
|
|
|
| Re: How to get X to Y rows without using "distinct" or "group by" [message #39727 is a reply to message #39684] |
Mon, 05 August 2002 08:22 |
T.Manikandan
Messages: 6
Registered: April 2002
|
Junior Member |
|
|
Dear TOdd,
In your result set , first column consists of 3 AA's.
But I need only one AA in the first column.
In the same manner how to create the materialized view for the the 3rd table.
Table3
AA 11 t
BB 22 y
CC 33 u
THe view should be like this (It should use all the 3 tables):
Materialized View:
AA 11 t
BB 22 y
CC 33 u
88
44
In this the 3rd column, should contain the distinct data's from 3rd table only..
Thanks for your reply,
bye,
Manikandan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
