Home » RDBMS Server » Performance Tuning » Problem with query having order by , distinct clause
Problem with query having order by , distinct clause [message #409249] Sun, 21 June 2009 02:53 Go to next message
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,Cool */ * from table_name order by col1;


select /* + parallel(table_name,Cool */ 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.

Any help really appreciated.

Thanks in advance
Re: Problem with query having order by , distinct clause [message #409262 is a reply to message #409249] Sun, 21 June 2009 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With 150+ posts you have no excuse to not post accordingly to forum guide. Please do so.

Regards
Michel
Re: Problem with query having order by , distinct clause [message #409336 is a reply to message #409249] Mon, 22 June 2009 03:37 Go to previous messageGo to next message
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 #413712 is a reply to message #409336] Fri, 17 July 2009 04:53 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Are the stats up to date?
How many rows are there in the table?
Could you please provide the explain plan output for the sqls?

Regards,
Prachi



Re: Problem with query having order by , distinct clause [message #413880 is a reply to message #413712] Sat, 18 July 2009 00:28 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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
Previous Topic: Select with no conditions taking long time, too many blocks, pctused
Next Topic: Storage parameters..
Goto Forum:
  


Current Time: Tue Dec 06 06:50:47 CST 2016

Total time taken to generate the page: 0.19603 seconds