Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 To display fast Data

Author  Topic 

praveen.paspulate
Starting Member

14 Posts
Posted - 2008-10-15 : 06:48:31
select a.t$ttyp Trns_Type, a.t$docn Doc_No, a.t$ninv Inv,
a.t$ifbp Bp, a.t$isup, a.t$refr Refe, a.t$amnt Amount,
a.t$ccur currency, a.t$stap Status, a.t$balc Bal,
a.t$user,a.t$docd Dates from baan.ttfacp200168 a
where a.t$isup not in(select b.t$isup from baan.ttfacp200168 b group by b.t$isup having count(b.t$isup)<=1) and a.t$docd between '01-MAR-2008' and '30-MAR-2008' and a.t$isup <> ' '

The above query retrieve the duplicate records of table baan.ttfacp200168. But in this table it has 10 lakhs records, so it takes time about 2 hrs to display the data.

Can anyone please advise the best query to display fast data in Oracle.

Thanks
Praveen

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2008-10-15 : 07:03:24
Hi,

Sorry this is a MS SQL server forum. We can't help you.

I guess you could try :: www.orafaq.com/forum but I've never used it.

Regards,

-------------
Charlie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-15 : 07:18:10
[code]select a.[t$ttyp] as Trns_Type,
a.[t$docn] AS Doc_No,
a.[t$ninv] AS Inv,
a.[t$ifbp] as Bp,
a.[t$isup],
a.[t$refr] as Refe,
a.[t$amnt] AS Amount,
a.[t$ccur] as currency,
a.[t$stap] as Status,
a.[t$balc] AS Bal,
a.t$user,
a.[t$docd] AS Dates
from baan.ttfacp200168 AS a
inner join (
select [t$isup]
from baan.ttfacp200168
group by [t$isup]
having count(*) > 1
) AS b on b.[t$isup] = a.[t$isup]
where a.[t$docd] between '01-MAR-2008' and '30-MAR-2008'[/code]

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-15 : 07:19:45
Instead of filter out the records with no duplicates, just include those with duplicates.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2008-10-15 : 07:25:04
Am I missing something? Was this actually a transact question?

-------------
Charlie
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2008-10-15 : 07:26:15
That query does look a bit odd, though. I can't see any obvious reason why it would be finding the t$isup values that are unique and then getting everything that isn't one of those.

Don't know anything about query optimization in Oracle, but you might be better off with a join rather than an IN subquery.

Untested:

select a.t$ttyp Trns_Type, a.t$docn Doc_No, a.t$ninv Inv,
  a.t$ifbp Bp, a.t$isup, a.t$refr Refe, a.t$amnt Amount,
  a.t$ccur currency, a.t$stap Status, a.t$balc Bal,
  a.t$user,a.t$docd Dates
from baan.ttfacp200168 a
inner join (
    select b.t$isup
    from baan.ttfacp200168 b
    group by b.t$isup
    having count(b.t$isup) > 1
  ) b
  on a.t$isup = b.t$isup
where a.t$docd between '01-MAR-2008' and '30-MAR-2008'
  and a.t$isup <> ' '


Edit: I'm just too slow!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-15 : 07:26:32
Display fast data must be a request for performance suggestions.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2008-10-15 : 07:35:17
quote:
Originally posted by Peso

Display fast data must be a request for performance suggestions.


E 12°55'05.63"
N 56°04'39.26"


Makes sense, I thought it might have been some oracle specific sql extension or similar. Like the Fast files in 11g

-------------
Charlie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-15 : 08:13:19
quote:
An attractive feature of Oracle Database 11g is Oracle Fast Files. With Oracle Fast Files, the system is capable of storing large objects (LOBs) such as images, large text objects, or advanced data types, including XML, medical imaging, and three-dimensional objects - within the database. Oracle Fast Files offers database applications performance fully comparable to file systems. By storing a wider range of enterprise information and retrieving it quickly and easily, enterprises can know more about their business and adapt more rapidly.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

praveen.paspulate
Starting Member

14 Posts
Posted - 2008-10-16 : 07:22:12
hi,

Thanks a lot using with INNER join the query is working.

Thanks & regards
praveen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-16 : 08:25:55
How much time do the query take now?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

EugeneLim11
Posting Yak Master

167 Posts
Posted - 2008-10-17 : 01:11:12
Reminds of performance problems with query that (seems to) takes forever when we do a query in a table got over 3 million records with multiple joins. I solved the darn problem by creating indexes on the columns that I am retrieving data in, getting rid of nested queries where possible, and using views and selecting from views (with smaller preselected data set). :) performance increased from 15-20 mins to less than 30 secs. :) Amazing how fast queries can make a HUGE difference in retrieving data from database

check out my blog at http://www.aqauriumlore.blogspot.com
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site