Thanks Jmodic & Sambavan, Please see the xplan
Please see one of the simple query and its explain plan below. Probably this would give an idea to suggest me something.
Thanks and Regards,
gtm
SELECT DISTINCT gender, count(*) FROM customer
WHERE zip BETWEEN '11700' AND '11799'
GROUP BY gender;
Time Taken : 7 min
G COUNT(*)
- ----------
F 499791
I 164926
M 499396
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4266 Card=3 Bytes=18
)
1 0 SORT (GROUP BY) (Cost=4266 Card=3 Bytes=18)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CUSTOMER' (Cos
t=4171 Card=12435 Bytes=74610)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP INDEX (RANGE SCAN) OF 'ZIP_IDX'
Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
156562 consistent gets
129468 physical reads
0 redo size
519 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed