OraFAQ Forum: Performance Tuning » Optimizing Select from VIEW

Home » RDBMS Server » Performance Tuning » Optimizing Select from VIEW (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Optimizing Select from VIEW [message #603229]

Thu, 12 December 2013 07:25

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi,

I want to select distinct records from my view (this view contains more than 4 million records).
The problem is when i select without any distinct it performs smooth like in 4-5 secs but when i use distinct it takes more than 12 minutes.

Note: View is having more than 12 columns but i am selecting only 5.

e.g.

select col1, col2, col3, col4, col5 from my_view where col1 = :my_value

Please suggest.

Report message to a moderator

Re: Optimizing Select from VIEW [message #603239 is a reply to message #603229]

Thu, 12 December 2013 08:37

ind9
Messages: 65
Registered: January 2009

Member

Please provide explain plan for your query.

Report message to a moderator

Re: Optimizing Select from VIEW [message #603240 is a reply to message #603229]

Thu, 12 December 2013 08:46

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

A explain plan is allready asked for. Maybe you can also supply some DDL so we can see what we are looking at.

Report message to a moderator

Re: Optimizing Select from VIEW [message #603389 is a reply to message #603229]

Sat, 14 December 2013 15:30

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I suspect there are two issues in play here:

1. Your initial statement that the query only takes 4-5 seconds without the distinct is wrong. I suggest you consider that your statement without a distinct (and thus without any group operation (order by/hash)) returns the first set of rows (20 maybe) in 4-5 seconds but it has not given you 4 million rows in 4-5 seconds (unless you are on a massively parallel EXADATA box).

Do this instead:

create table my_table
nologging
as
select ...
from my_view
where col1 = :my_value
/

This will force the query to return all rows and thus tell you for sure how long the query is actually taking without the DISTINCT.

2. Then do this

create table my_distinct_table
nologging
as
select distinct ...
from ...
where ...
/

This will retrieve all the rows, then distinct what you want. It will give you some idea of how much more time the DISTINCT operation adds to overall processing time.

After doing the above report back the following, including the number of rows resulting in each table.

Kevin

Report message to a moderator

Previous Topic:	SQL Tuning Advisor
Next Topic:	Performance Monitoring .

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 02 02:50:08 CDT 2023