Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> DBA_OBJECTS using Distinct Vs Group By

DBA_OBJECTS using Distinct Vs Group By

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Wed, 21 Jun 2006 13:01:05 -0700 (PDT)
Message-ID: <20060621200106.47882.qmail@web52808.mail.yahoo.com> 





Any ideas why "GROUP BY" query is faster that
"DISTINCT" in this query?



SELECT OWNER FROM DBA_OBJECTS GROUP BY OWNER;


Good Timing (GROUP BY) Plan (runs in 9 secs):



| Id  | Operation                                     


        | Name       | Rows  | Bytes | Cost (%CPU)|

Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              


        |            |   457 | 22850 |  2062   (9)|

00:00:25 |
|   1 |  HASH GROUP BY                                


        |            |   457 | 22850 |  2062   (9)|

00:00:25 |
|*  2 |   FILTER                                      


        |            |       |       |            |   

      |
|*  3 |    HASH JOIN                                  


        |            |   506K|    24M|  2019   (7)|

00:00:25 |
|   4 |     TABLE ACCESS FULL                         


        | USER$      |   457 |  5484 |     7   (0)|

00:00:01 |
|*  5 |     TABLE ACCESS FULL                         


        | OBJ$       |   506K|    18M|  2006   (7)|

00:00:25 |
|   6 |    NESTED LOOPS                               


        |            |     1 |    26 |     1   (0)|

00:00:01 |
|*  7 |     INDEX RANGE SCAN                          


        | I_OBJAUTH1 |     2 |    26 |     1   (0)|

00:00:01 |
|*  8 |     FIXED TABLE FULL                          


        | X$KZSRO    |     1 |    13 |     0   (0)|

00:00:01 |
|*  9 |      FIXED TABLE FULL                         


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 10 |       FIXED TABLE FULL                        


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 11 |        FIXED TABLE FULL                       


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 12 |         FIXED TABLE FULL                      


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 13 |          FIXED TABLE FULL                     


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 14 |           FIXED TABLE FULL                    


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 15 |            FIXED TABLE FULL                   


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 16 |             FIXED TABLE FULL                  


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 17 |              FIXED TABLE FULL                 


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 18 |               FIXED TABLE FULL                


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 19 |                FIXED TABLE FULL               


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 20 |                 FIXED TABLE FULL              


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 21 |                  FIXED TABLE FULL             


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 22 |                   FIXED TABLE FULL            


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 23 |                    FIXED TABLE FULL           


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 24 |                     FIXED TABLE FULL          


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 25 |                      FIXED TABLE FULL         


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 26 |                       FIXED TABLE FULL        


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 27 |                        FIXED TABLE FULL       


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 28 |                         FIXED TABLE FULL      


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|* 29 |                          FIXED TABLE FULL     


        | X$KZSPR    |     1 |    26 |     0   (0)|

00:00:01 |
|  30 |                           VIEW                


        |            |     1 |    13 |     2   (0)|

00:00:01 |
|  31 |                            FAST DUAL          


        |            |     1 |       |     2   (0)|

00:00:01 |
|* 32 |                             TABLE ACCESS BY
INDEX ROWID| IND$       |     1 |     9 |     1   (0)|
00:00:01 |
|* 33 |                              INDEX UNIQUE SCAN


        | I_IND1     |     1 |       |     1   (0)|



00:00:01 |





SELECT DISTINCT OWNER FROM DBA_OBJECTS;


Bad Timing (DISTINCT) Plan:



| Id  | Operation                                     
       | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              
       |            |   457 | 22850 |  2025   (8)|
00:00:25 |
|   1 |  HASH UNIQUE                                  
       |            |   457 | 22850 |  2025   (8)|
00:00:25 |
|*  2 |   FILTER                                      
       |            |       |       |            |    
     |
|*  3 |    HASH JOIN                                  
       |            |   506K|    24M|  2019   (7)|
00:00:25 |
|   4 |     TABLE ACCESS FULL                         
       | USER$      |   457 |  5484 |     7   (0)|
00:00:01 |
|*  5 |     TABLE ACCESS FULL                         
       | OBJ$       |   506K|    18M|  2006   (7)|
00:00:25 |
|*  6 |    HASH JOIN                                  
       |            |     2 |    52 |     2  (50)|
00:00:01 |
|*  7 |     INDEX RANGE SCAN                          
       | I_OBJAUTH1 |     2 |    26 |     1   (0)|
00:00:01 |
|   8 |     FIXED TABLE FULL                          
       | X$KZSRO    |   100 |  1300 |     0   (0)|
00:00:01 |
|*  9 |     FIXED TABLE FULL                          
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 10 |      FIXED TABLE FULL                         
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 11 |       FIXED TABLE FULL                        
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 12 |        FIXED TABLE FULL                       
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 13 |         FIXED TABLE FULL                      
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 14 |          FIXED TABLE FULL                     
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 15 |           FIXED TABLE FULL                    
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 16 |            FIXED TABLE FULL                   
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 17 |             FIXED TABLE FULL                  
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 18 |              FIXED TABLE FULL                 
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 19 |               FIXED TABLE FULL                
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 20 |                FIXED TABLE FULL               
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 21 |                 FIXED TABLE FULL              
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 22 |                  FIXED TABLE FULL             
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 23 |                   FIXED TABLE FULL            
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 24 |                    FIXED TABLE FULL           
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 25 |                     FIXED TABLE FULL          
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 26 |                      FIXED TABLE FULL         
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 27 |                       FIXED TABLE FULL        
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 28 |                        FIXED TABLE FULL       
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|* 29 |                         FIXED TABLE FULL      
       | X$KZSPR    |     1 |    26 |     0   (0)|
00:00:01 |
|  30 |                          VIEW                 
       |            |     1 |    13 |     2   (0)|
00:00:01 |
|  31 |                           FAST DUAL           
       |            |     1 |       |     2   (0)|
00:00:01 |
|* 32 |                            TABLE ACCESS BY
INDEX ROWID| IND$       |     1 |     9 |     1   (0)|
00:00:01 |
|* 33 |                             INDEX UNIQUE SCAN 
       | I_IND1     |     1 |       |     1   (0)|


00:00:01 |







Do You Yahoo!?


Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

--
http://www.freelists.org/webpage/oracle-l


Received on Wed Jun 21 2006 - 15:01:05 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US