Table : ABC
Index on Table : YTIME, XTIME, XID

 
Selecting Data from Multiple Partition:

SELECT COUNT(1) AS CNT FROM 
              (
                          SELECT * FROM  ABC PARTITION (ABC_T_0811)
                          UNION ALL
                          SELECT * FROM  ABC PARTITION (ABC_T_0812)
                          UNION ALL
                         SELECT * FROM  ABC PARTITION (ABC_T_0901)
                          )X
                   WHERE X.YTIME BETWEEN TO_DATE('10-01-2009 06:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                                          AND TO_DATE('10-01-2009 06:10:00', 'DD-MM-YYYY HH24:MI:SS')
                 );

Time : 10 Min

Selecting Data from single Partition:

SELECT COUNT(1) AS CNT FROM 
                         (
                          SELECT * FROM  ABC PARTITION (ABC_T_0811)
                                                 
                          )X
                   WHERE X.YTIME BETWEEN TO_DATE('10-01-2009 06:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                                          AND TO_DATE('10-01-2009 06:10:00', 'DD-MM-YYYY HH24:MI:SS')
                 );

Time : 3 Min