This discussion is archived
7 Replies Latest reply: Dec 5, 2012 3:36 PM by rp0428 RSS

Performance issue - DISTINCT

skv Newbie
Currently Being Moderated
Hi,

I have below performance issue.

1. I have created one view three big tables (each table has around 300k rows)

     create view vw1 as select col1, col2, col3
     from tab1, tab2, tab3
     where <joining three tables>;

2. I am joining above view with one table (say tab10) which has around 30k rows.


Below query retruns no rows and gives result in milliseconds.

     select a.col1, b.col1, b.col2
     from tab01 a, vw1 b
     where a.col1 = 1 and
          a.col2 = b.col2;


But below query taking more than 3 minuts, if I add "DISTINCT"

     select DISTINCT a.col1, b.col1, b.col2
     from tab01 a, vw1 b
     where a.col1 = 1 and
          a.col2 = b.col2;

All joing columns have indexes and statistics are latest. I am working on Oracle 11g under Unix environment.

Please help.
  • 1. Re: Performance issue - DISTINCT
    Justin Cave Oracle ACE
    Currently Being Moderated
    What are the query plans?

    Why does the query return 0 rows? It's possible that Oracle is doing the sort that you request when you add a DISTINCT before applying whatever predicate eliminates all the rows. If you post the query plans, that should be clear. It seems odd, though, that you would have a predicate that eliminates all the rows from the result set...

    Justin
  • 2. Re: Performance issue - DISTINCT
    skv Newbie
    Currently Being Moderated
    Here is the plan. VW1 is the view and created on 5 tables, TAB1, TAB2, TAB3, TAB4 and TAB5. VW1 has been joined with TAB01 table.

    PLAN
    ====

    SELECT STATEMENT CHOOSECost: 589,872 Bytes: 639,331 Cardinality: 77                                                        
         22 HASH UNIQUE Cost: 589,872 Bytes: 639,331 Cardinality: 77                                                   
              21 MERGE JOIN Cost: 589,871 Bytes: 639,331 Cardinality: 77                                              
                   17 VIEW VIEW VW1 Cost: 589,868 Bytes: 3,026,654,130 Cardinality: 365,097                                         
                        16 SORT ORDER BY Cost: 589,868 Bytes: 123,037,689 Cardinality: 365,097                                    
                             15 NESTED LOOPS OUTER Cost: 573,427 Bytes: 123,037,689 Cardinality: 365,097                               
                                  12 HASH JOIN RIGHT OUTER Cost: 556,567 Bytes: 112,084,779 Cardinality: 365,097                          
                                       2 TABLE ACCESS BY INDEX ROWID TABLE TAB1 Cost: 27 Bytes: 123,522 Cardinality: 2,422                     
                                            1 INDEX FULL SCAN INDEX (UNIQUE) TAB1_PK Cost: 4 Cardinality: 2,422                
                                       11 NESTED LOOPS                     
                                            9 NESTED LOOPS Cost: 556,538 Bytes: 93,464,832 Cardinality: 365,097                
                                                 7 HASH JOIN Cost: 191,191 Bytes: 16,064,268 Cardinality: 365,097           
                                                      4 TABLE ACCESS BY INDEX ROWID TABLE TAB2 Cost: 5,375 Bytes: 6,571,746 Cardinality: 365,097      
                                                           3 INDEX FULL SCAN INDEX (UNIQUE) TAB2_PK Cost: 472 Cardinality: 365,097
                                                      6 TABLE ACCESS BY INDEX ROWID TABLE TAB3 Cost: 168,252 Bytes: 285,585,482 Cardinality: 10,984,057      
                                                           5 INDEX FULL SCAN INDEX TAB3_IDX Cost: 10,618 Cardinality: 10,984,057
                                                 8 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB4_PK Cost: 0 Cardinality: 1           
                                            10 TABLE ACCESS BY INDEX ROWID TABLE TAB4 Cost: 1 Bytes: 212 Cardinality: 1                
                                  14 TABLE ACCESS BY INDEX ROWID TABLE TAB5 Cost: 1 Bytes: 30 Cardinality: 1                          
                                       13 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB5_PK Cost: 0 Cardinality: 1                     
                   20 SORT JOIN Cost: 3 Bytes: 1,001 Cardinality: 77                                         
                        19 TABLE ACCESS BY INDEX ROWID TABLE TAB01 Cost: 2 Bytes: 1,001 Cardinality: 77                                    
                             18 INDEX RANGE SCAN INDEX TAB01_IDX Cost: 1 Cardinality: 77
  • 3. Re: Performance issue - DISTINCT
    Justin Cave Oracle ACE
    Currently Being Moderated
    It's a little difficult to follow that plan. Can you take a look at the entry from the forum FAQ on When your query takes too long ...? That walks through how to generate a more readable query plan. You'll also need to use the \
     tag before and after the plan to preserve white space and make the plan easier for us to read.
    
    Is this the plan for the fast query?  Or for the slow query?
    
    Why does the query return 0 rows?  As I said previously when I asked this question, that seems pretty unusual.
    
    Justin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 4. Re: Performance issue - DISTINCT
    skv Newbie
    Currently Being Moderated
    Thanks a lot Justin and I am very sorry for not providing information that you have asked.

    1. The plan I have provided is for long running query (as I said, result comes in fraction of a second without DISTINCT),

    2. Query is not returning rows for given filter criteria (i.e., a.col1 = 1).

    I am unable to keep white space in the plan. In the "Preview" tab, white space is getting removed even though I kept white space/tabs.
    I am not getting how to keep tags for the white space from the link that you have provided. I don't have access to the server for trace/tkprof.
  • 5. Re: Performance issue - DISTINCT
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are you using the \
     tag (6 characters, all lower case) before and after the text where you want to preserve the whitespace?
    
    Justin                                                                                                                                                                                                                                                                                        
  • 6. Re: Performance issue - DISTINCT
    skv Newbie
    Currently Being Moderated
    Now I got it Justin..Thanks. Can you please look into it and give suggestions.

    SELECT STATEMENT   [CHOOSE] Cost = 589872
      HASH UNIQUE
        MERGE JOIN
          VIEW  VW1
            SORT ORDER BY
              NESTED LOOPS OUTER
                HASH JOIN RIGHT OUTER
                  TABLE ACCESS BY INDEX ROWID TAB1 [ANALYZED]
                    INDEX FULL SCAN TAB1_PK [ANALYZED]
                  NESTED LOOPS
                    NESTED LOOPS
                      HASH JOIN
                        TABLE ACCESS BY INDEX ROWID TAB2 [ANALYZED]
                          INDEX FULL SCAN TAB2_PK [ANALYZED]
                        TABLE ACCESS BY INDEX ROWID TAB3 [ANALYZED]
                          INDEX FULL SCAN TAB3_IDX [ANALYZED]
                      INDEX UNIQUE SCAN TAB4_PK [ANALYZED]
                    TABLE ACCESS BY INDEX ROWID TAB4 [ANALYZED]
                TABLE ACCESS BY INDEX ROWID TAB5 [ANALYZED]
                  INDEX UNIQUE SCAN TAB5_PK [ANALYZED]
          SORT JOIN
            TABLE ACCESS BY INDEX ROWID TAB01 [ANALYZED]
              INDEX RANGE SCAN TAB01_IDX [ANALYZED]
  • 7. Re: Performance issue - DISTINCT
    rp0428 Guru
    Currently Being Moderated
    Thanks! That's a lot easier to read.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points