Query performance issue

[R1972]

Hi,

select * from tableA a where
1 >= (select count(distinct edate) from tableA b
where b.no_property = a.no_property
and b.company = a.company
and b.edate > a.edate
)

The above query table have around 10million rows. I need to get info of last 2 max edate for each no_property and company of tableA.
This query took me around 4 hrs and not completed successfully.

Pl. give your suggestions on the above query.

Thanks in advance.

Raj

 

[bastienk]

what is the purpose of the query?

select no_property, company, edate 
from tableA 
where edate in (select edate from tableA where rowID>3)

The above assumes that edate is the same for every entry


Bastien

Cat, the other other white meat

 

[parbhani]

Hi there ,

How about ,

select * from
( select a.* ,
RANK()
OVER ( PARTATION BY a.no_property, a.company
ORDER BY a.edate DESC ) "RANK"
from tableA a
)
where rank <= 2 ;

Basically RANK is a function provided by Oracle, so hopefully that should work and also with reasonable performance .

Please convey if that worked and in how much time ??

Cheers

 

[R1972]

Hi parbhani,

Thanks, actually your query took only around 30 min. The table contains more than 30 million rows... Much more better than b4.

Thanks again,

I've another set of query...hope you can help in this...

The following query gets the recent one eg..


No. dt_start dt_end
1 17/03/04 null or highdated--need this row
1 18/03/04 18/03/04
1 11/02/02 16/03/04



SELECT /*+rule*/ * FROM tableA A, tableB B
where
a.cd_company_system = b.cd_company_system
and a.no_property = b.no_property
and nvl(A.dt_end, sysdate) in
(select max(nvl(aa.dt_end, sysdate)) from tableA aa
where aa.cd_company_system = a.cd_company_system
and aa.no_property = a.no_property
)
and a.dt_start in
(select max(nvl(ab.dt_start, sysdate)) from tableA ab
where ab.cd_company_system = a.cd_company_system
and ab.no_property = a.no_property
and nvl(ab.dt_end, sysdate) = nvl(a.dt_end, sysdate)

 

[parbhani]

Hi there ,

To optimize the second query you mentioned, its very important to have some understanding of nature of data.

1) Is there index on cd_company_system and/or no_property ?
2) Generally, how many rows will be retrived for a given cd_company_system and no_property combination ?
3) Possibly there may NOT be an index on dt_start and dt_end, so , this query will take time as there will be a full table scan on the joined data and the sub-queries will be run for each row on them.
4) If you are on Oracle 9i, the hint /*+rule */ looks UNnessesary
5) If a cd_company_system and no_property combination return considerably less number of rows ( even few hundreds ), just analyze the tables and indexes and try running the query

Cheers

 

[R1972]

HI,

There is an index on cd_company_system and no_property
and also created functional index on dt_end and normal index on dt_start...

There are around million rows in that table...and around 900,000 rows using the where clause

This query not taking much time like the other query that you rectified it but this query taking around 20 min...Is it possible to do less than 20 min ?

Thanks,
Raj