| Performance Tuning [message #39627] |
Mon, 29 July 2002 07:15  |
Manikandan
Messages: 7
Registered: October 2000
|
Junior Member |
|
|
Hi,
I have a table with 6 lakhs rows with 10 columns.
I want to select a particular column with some criteria. I need to use distinct and order by column.
For this i tested and found that it took 1 minute for me to get all the values.My column is varchar2(3999) [[as it contains large text]] and hence i am unable to index it(as indexing is allowed only for the columns with less than 255).
For improving the performance i created another
table with "distinct" and "order by" option and then i used distinct option and found that performance is somewhat improved.
But still i need good performance.
Let me explain the problem with some examples :
My table is Sample with columns col1,col2,col3.It contains 6 lakhs entries.
Sample table : col1 col2 col3.
My query is : select distinct (col1 ) from sample
where col2 = 'A' and col2='B' order by col1;
This query tooks 1 min and i created a another table sample1 as follows :
create table sample1 as select distinct col1,col2,col3 from sample order by col1;
Then i tried to query the sample1 table as :
select distinct col1 from sample1 where col2='A' and col3='B'.
Thr' this, i eliminated the "order by" option only because i need distinct option.
My Questions :
1. Is there any way to index the column with more than 255 varchar2 length.
2. Is the above method is correct or anyother idea?
Any help is appreciated.
bye,
mani
|
|
|
|
|
|
| Re: Performance Tuning [message #39631 is a reply to message #39628] |
Mon, 29 July 2002 21:33  |
Manikandan
Messages: 7
Registered: October 2000
|
Junior Member |
|
|
Hi,
Thanks for your response Mahesh.
I have one idea about optimizing the query for the second table sample1. But i don't know how to implement this, as i am new to oracle.
Actually i am taking the distinct of col1 in the table sample1 which is already in "order by col1".
So, instead of using alias we can just compare the
previous row col1 value with the current and if they
are not equal, we can output the result.Otherwise we
can go to next row and this must be in a loop.
I prefer this logic, because at once i need only 100 rows from that table and that may be from 1-100 or 100-200 or 200-300. As i have no experience in implementing this type of logic in the query/SQL, any
help is appreciated.
Thanks,
mani
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
