Home » RDBMS Server » Performance Tuning » Optimizing Select from VIEW (Oracle 10G)
Optimizing Select from VIEW [message #603229] Thu, 12 December 2013 07:25 Go to next message
Messages: 33
Registered: December 2013

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.

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

Please suggest.
Re: Optimizing Select from VIEW [message #603239 is a reply to message #603229] Thu, 12 December 2013 08:37 Go to previous messageGo to next message
Messages: 65
Registered: January 2009
Please provide explain plan for your query.
Re: Optimizing Select from VIEW [message #603240 is a reply to message #603229] Thu, 12 December 2013 08:46 Go to previous messageGo to next message
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.
Re: Optimizing Select from VIEW [message #603389 is a reply to message #603229] Sat, 14 December 2013 15:30 Go to previous message
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
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
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.

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

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