7 Replies Latest reply: Dec 5, 2012 5:36 PM by rp0428 RSS

    Performance issue - DISTINCT


      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
          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...

          • 2. Re: Performance issue - DISTINCT
            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.


            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
              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.
              • 4. Re: Performance issue - DISTINCT
                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
                  Are you using the \
                   tag (6 characters, all lower case) before and after the text where you want to preserve the whitespace?
                  • 6. Re: Performance issue - DISTINCT
                    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
                      Thanks! That's a lot easier to read.