SQL tuning tip

From: Prabhu Krishnaswamy <prabhu_adam_at_hotmail.com>
Date: Wed, 17 Oct 2012 14:16:06 +0000
Message-ID: <BAY173-W34B83AD7507C4580A8BFA385770_at_phx.gbl>

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

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

Received on Wed Oct 17 2012 - 16:16:06 CEST