Slow query with distinct [message #509769] |
Tue, 31 May 2011 17:14 |
uicmxz
Messages: 48 Registered: July 2006
|
Member |
|
|
I need to return distinct description to application. Simple SQL statement has been run:
Select distinct cl_desc from dim_product
This SQL has been run 7 min in production environment, because the product table has 31 million records and cl_desc has NULL values. I tried to create bitmap index on cl_desc column, but Oracle can't use the index to determine if there are null-values in column cl_desc. What are the possible solutions for this issue? How to speed up the query and return distinct values?
|
|
|
|
|
|
|
Re: Slow query with distinct [message #509901 is a reply to message #509800] |
Wed, 01 June 2011 09:48 |
uicmxz
Messages: 48 Registered: July 2006
|
Member |
|
|
Yes. It is a hash unique. Here is explain plan:
Plan
SELECT STATEMENT ALL_ROW
SCost: 526,903 Bytes: 166,050 Cardinality: 6,642 CPU Cost: 59,086,041,845 IO Cost: 523,940 Time: 7,377
2 HASH UNIQUE Cost: 526,903 Bytes: 166,050 Cardinality: 6,642 CPU Cost: 59,086,041,845 IO Cost: 523,940 Time: 7,377
1 TABLE ACCESS FULL TABLE DSS_OWNER.DIM_PRODUCT Cost: 525,165 Bytes: 772,492,200 Cardinality: 30,899,688 CPU Cost: 24,427,340,707 IO Cost: 523,940 Time: 7,353
|
|
|
|
|
Re: Slow query with distinct [message #509917 is a reply to message #509910] |
Wed, 01 June 2011 10:27 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
One though is that FTS goes from Block #1 to HWM.
If last real data resides well below HWM, then SHRINK or COALESCE might reduce elapsed time by some measurable percentage.
Then again, it might have no impact.
|
|
|