|
|
|
|
| Re: Problem with query having order by , distinct clause [message #409336 is a reply to message #409249] |
Mon, 22 June 2009 03:37   |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
I have a problem with queries having order by, distinct clause.
While its executing its taking lot of time. With DBMS_PROFILER identified the queries taking long time.
The table is having approximately 70 million rows.
Problem -1
select * from table_name order by col1;
select distinct col1,col2 from table_name;
Here i am having 2 solutions request to let me know whether i am right if not suggest me right solution.
Solution1:
Max parallel servers is 8.
select /* + parallel(table_name, */ * from table_name order by col1;
select /* + parallel(table_name, */ distinct col1, col2 from table_name ;
Solution-2:
select /* + first_rows */ * from table_name order by col1;
select /* + first_rows */ distinct col1, col2 from table_name ;
Problem-2
---------
I am having a query with where condition on columns.
Select * from table_name where col1='value1' and col2!='value2';
Index created on col1 and col2.
As we no that not equal won't use index as it is a composite index it should use the lead column. but its not using the index.
Should i forcibly use index with hint or suggest me better solution.
Thanks in advance
|
|
|
|
|
|
| Re: Problem with query having order by , distinct clause [message #413880 is a reply to message #413712] |
Sat, 18 July 2009 00:28 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
select * from table_name order by col1;
If you don't already have one, create an index on COL1. It will use the index to sort.
select distinct col1,col2 from table_name;
Create an index on (COL1,COL2) it should use the index to sort in order to get a distinct list.
Select * from table_name where col1='value1' and col2!='value2';
Put an index on (COL1,COL2). Yes it cannot SCAN on COL2, but it can filter non-matching rows without looking up the table. If it still won't use the index, make sure statistics are calculated with DBMS_STATS.GATHER_TABLE_STATS(), and try an /*+INDEX(table_name)*/ hint if necessary. But if Oracle does not choose to use the index without the hint, you may find that the index is slower.
Ross Leishman
|
|
|
|
| Re: Problem with query having order by , distinct clause [message #413921 is a reply to message #413880] |
Sun, 19 July 2009 00:39 |
saptarshibasu
Messages: 15
Registered: July 2009
|
Junior Member |
|
|
If you know for sure that the required values of col1 are clustered in a fewer blocks in the table, you may consider using the index hint and check the performance.
But, if the required values are spread across most of the blocks in the table, full scan would be faster.
The optimizer is correct in most cases, but sometimes the way data is populated in the table may cause the optimizer ignore indexes and go for full scan even when index path is faster.
-Saptarshi Basu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
*/ * from table_name order by col1;
