OraFAQ Forum: Performance Tuning » Slow query with distinct

Home » RDBMS Server » Performance Tuning » Slow query with distinct (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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?

Report message to a moderator

Re: Slow query with distinct [message #509770 is a reply to message #509769]

Tue, 31 May 2011 17:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

post DDL (CREATE TABLE) for DIM_PRODUCT table.

Report message to a moderator

Re: Slow query with distinct [message #509771 is a reply to message #509770]

Tue, 31 May 2011 17:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

15:26:36 SQL> select count(*) from WRH$_LATCH_CHILDREN;

  COUNT(*)
----------
  14517956

15:28:32 SQL>

My DB can spin through 14 Million rows in about 2 minutes!
enable SQL_TRACE to see where time is being spent in your DB

Report message to a moderator

Re: Slow query with distinct [message #509796 is a reply to message #509771]

Wed, 01 June 2011 01:45

matlar
Messages: 30
Registered: May 2011
Location: Stockholm

Member

But distinct is different. Probably a sort is being done to remove duplicates. What version are You using?

Report message to a moderator

Re: Slow query with distinct [message #509800 is a reply to message #509796]

Wed, 01 June 2011 02:41

matlar
Messages: 30
Registered: May 2011
Location: Stockholm

Member

Sorry I missed it but I see now You use version 10G. Then it will probably be a hash unique. Wich is quicker but "not for free". As suggested in another posting, find out the execution plan.

Report message to a moderator

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

Report message to a moderator

Re: Slow query with distinct [message #509903 is a reply to message #509901]

Wed, 01 June 2011 09:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

yes, it takes time to scan many rows.
You can't change reality.

Report message to a moderator

Re: Slow query with distinct [message #509910 is a reply to message #509901]

Wed, 01 June 2011 10:15

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	index advises
Next Topic:	Advice on Index Creation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:14:26 CDT 2024