Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY for distinct items (9.0.4.0)
SQL QUERY for distinct items [message #332319] Tue, 08 July 2008 03:41 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,
I wrote a query as follows .
select item from items where item in
(select item from item_skj is,item_sku iku ,datestat dt
where is.cost=iku.cost
and is.dates=dt.datess+1)
and supplier  is  null;

It displays all the items.But I need to display distinct items.If i add "Distinct " keyword it's taking 20 mins. So please let me know to increase the performance of the query.

Thank you

[Updated on: Tue, 08 July 2008 03:45]

Report message to a moderator

Re: SQL QUERY for distinct items [message #332320 is a reply to message #332319] Tue, 08 July 2008 03:45 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please post your test case.

1) Create table and insert statements.
2) Output you require

Regards,
Rajat

[Updated on: Tue, 08 July 2008 03:46]

Report message to a moderator

Re: SQL QUERY for distinct items [message #332322 is a reply to message #332319] Tue, 08 July 2008 03:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How long does this query take to run:
SELECT count(*)
FROM   (select item 
        from   items 
        where  item in (select item 
                        from   item_skj is
                              ,item_sku iku 
                              ,datestat dt
                        where  is.cost=iku.cost
                        and    is.dates=dt.datess+1)
         and    supplier is null)

I suspect that your query takes nearly 20 minutes to return all the rows, but that you're getting the first rows back in much less time than that.
Re: SQL QUERY for distinct items [message #332323 is a reply to message #332319] Tue, 08 July 2008 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you need distinct value, you have to use "distinct", so nothing to do this way.

Check your sort parameters (pga_aggregate_target, sort_area_size).

Regards
Michel
Re: SQL QUERY for distinct items [message #332326 is a reply to message #332320] Tue, 08 July 2008 03:53 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member


ITEM    COST     DATESS 
___________________________
5678432  2300     jun1808
5678432  4300     jun0908
5678432  3278     jui0808
123456
123456
3211222
3211222
123214
32453
...
Like ths am gettomg 3000 rows.


I want to display distinct records.
Re: SQL QUERY for distinct items [message #332330 is a reply to message #332326] Tue, 08 July 2008 03:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
All the records you showed us there ARE distinct.

Have you timed that query I posted above?
Re: SQL QUERY for distinct items [message #332338 is a reply to message #332330] Tue, 08 July 2008 04:16 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,
It tooks 23.47 mins for execution using your query.
Re: SQL QUERY for distinct items [message #332340 is a reply to message #332338] Tue, 08 July 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the problem is NOT in the distinct.
How much it takes without it?

Regards
Michel
Re: SQL QUERY for distinct items [message #332341 is a reply to message #332340] Tue, 08 July 2008 04:20 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

It is less than 4 mins.

Thank you
Re: SQL QUERY for distinct items [message #332343 is a reply to message #332330] Tue, 08 July 2008 04:26 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

before using your query am getting around 300 rows.after executing your query a getting 75,700 records.

Thank you.
Re: SQL QUERY for distinct items [message #332349 is a reply to message #332343] Tue, 08 July 2008 05:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The query did not do an update, so the number of records retrieved before running the query should match the number of records retrieved after running the query, unless something else changed.
Re: SQL QUERY for distinct items [message #332352 is a reply to message #332338] Tue, 08 July 2008 05:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There we are - when you run the query without the DISTINCt, you're just measuring how long it takes to return the first set of data - this is not how long it takes to fetch all the data.

The COUNT(*) query wll return nothing until it's fetched all the data back, which seems to be taking about 20 minutes.

Can you post an Explain Plan for the query, and tell us what indexes you've got on the tables, and what columns these indexes are on (USER_IND_COLUMNS is the view for that) and we'll see if we can speed the whole thing up a bit.
Previous Topic: copy or move table
Next Topic: Maximum size of a record in kb
Goto Forum:
  


Current Time: Mon Dec 05 12:42:13 CST 2016

Total time taken to generate the page: 0.15317 seconds