CODEWITHSUNDEEP
oracle-databasequery-optimizationdistinct
dijxtra
dijxtra

Reputation: 2751

Forcing Oracle to do distinct last

I have a quite complicated view (using several layers of views across several database links) which takes a second to return all of it's rows. But, when I ask for distinct rows, it takes considerably more time. I stopped waiting after 4 minutes.

To make my self as clear as possible:

select a, b from compicated_view; -- takes 1 sec (returns 6 rows)
select distinct a, b from compicated_view; -- takes at least 4 minutes

I find that pretty weird, but hey, that's how it is. I guess Oracle messed something up when planing that query. Now, is there a way to force Oracle to first finish the select without distinct, and then do a "select distinct *" on the results? I looked into optimizer hints, but I can't find anything about hinting the order in which distinct is applied (this is first time I'm optimizing a query, obviously :-/).

I'm using Oracle SQl Developer on Oracle 10g EE.

Upvotes: 2

Views: 393

Answers (2)

SKY
SKY

Reputation: 83

Since you haven't posted details... try the following:

SELECT DISTINCT A,B
FROM
(SELECT A,B FROM COMPLICATED_VIEW);

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

Try:

SELECT DISTINCT A,B FROM (
   SELECT A,B FROM COMPLICATED_VIEW 
   WHERE rownum > 0 );

this forces to materialize the subquery and prevents from view merging/predicate pushing, and likely from changing the original plan of the view.

You may also try NO_MERGE hint: 

SELECT /*+ NO_MERGE(alias) */ 
     DISTINCT a,b
FROM ( 
   SELECT a,b FROM COMPLICATED_VIEW 
) alias

Upvotes: 3

Related Questions