SQL tuning tip
- From: Prabhu Krishnaswamy <prabhu_adam@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2012 14:16:06 +0000
Lists,
We have a simple query and takes 300 seconds to run which drives us crazy to
know where the bottleneck is...
Here is the scenario:
Dimension A - Has 1000 rows
Dimension B has 5000 rows
Fact F has 30 million records
Dimension A & B has BITMAP indexes on Key column
SELECT DIM_A.KEY1, DIM_B.KEY2, COUNT(F.KEY1)
FROM DIMENSION A, DIMENSION B, FACT F
WHERE A.KEY1 IN (VAL 1, VAL2 , VAL3, VAL4)
AND A.KEY1 = F.KEY1
AND B.KEY2 = F.KEY2
Will the following work?
1) USE_NL hint instead of USE_HASH hint
2) Whether dropping and recreating the same table (including all partitions)?
3) Any new feature/concept that might help?
Any insight is highly appreciable.
Thank you
Prabhu
--
//www.freelists.org/webpage/oracle-l
Other related posts:
