Optimising the Query

Greetings,

No of records in TABLE1 = 46697622
No of records in TABLE2 = 9433275
No of records in TABLE3 = 9576297

SELECT      TAB1.P_TDATE,
	   FLOOR(TAB1.STIME/100) T_TIME,
	   SUM (CASE WHEN TAB1.LIND <> 1
			 AND  (TAB2.CNAME NOT IN ('PN','AR')
	     		 OR          (NVL(TAB3.OMNI,0) <> 7)
		                 OR          (TAB1.TIF <> 3))
	 	       THEN 1
		       ELSE 0
	        END ) ORDPOSTMKT,
	   SUM (CASE WHEN (TAB2.CNAME IN ('PN','AR')
			            OR        (TAB3.OMNI = 7)
			           OR         (TAB1.TIF = 3))
		       THEN 1
		       ELSE 0
	   END ) ORDOARS
FROM     TABLE1 TAB1
               LEFT OUTER JOIN (
	  	                  ( SELECT   ID,
P_TDATE,
				   SYMBOL,
				   REFNUMBER,
				   CNAME,
				   ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
		 	      FROM	    TABLE2
			    )  TAB2
		 JOIN         (SELECT  ID,
			                P_TDATE,
				SYMBOL,
				OMNI,
				REFNUMBER,
				ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
			  FROM    TABLE3
			 ) TAB3
		 ON         (
                                           TAB2.P_TDATE             =
TAB3.P_TDATE	      	                 AND   TAB2.SYMBOL              =
TAB3.SYMBOL
         	                 AND   TAB2.ID                         =
TAB3.ID
		 AND   TAB2.REFNUMBER       = TAB3.REFNUMBER
		 AND   TAB3.RANK                  = 1
		 AND   TAB2.RANK                  = 1)
		               )
              ON    (TAB1.P_TDATE                               =
TAB3.P_TDATE
              AND    TAB1.SYMBOL                               =
TAB3.SYMBOL
              AND    TAB1.ID                                          =
TAB3.ID)
WHERE            TAB1.OIND                                      <> 0
 AND                 TAB1.OTYPE                                  IN
('MR','GMR')
GROUP BY      TAB1.P_TDATE,
	        FLOOR(TAB1.STIME/100)
ORDER BY      T_TIME

Currently the query is taking 2+ hrs to execute.

The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN with TABLE1.
I know I am not giving much details abt each step and conditions used here but the query is
running fine and just wanted to know whether the same query can be written
in a more efficient manner.

Any help would be appreciated.

TIA DB version Info:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production Received on Sun Nov 05 2006 - 11:31:06 CST