Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance Issue using min() and max() in one SQL statement

9898Sep 24 2002
I have a simple query that selects min() and max() from one column in a table in one sql statment.
The table has about 9 Million rows and the selected column has a non unique index. The query takes 10 secs. When i select min() and max() in separate statements, each takes only 10 msecs:

This statement takes 10 secs:

select min(date_key) , max(date_key)
from CAPS_KPIC_BG_Fact_0_A
where date_key != TO_DATE(('1900-1-1' ),( 'YYYY-MM-DD'))


This statement takes 10 msecs:

select min(date_key)
from MYTABLE
where date_key != TO_DATE(('1900-1-1' ),( 'YYYY-MM-DD'))
union all
select max(date_key) from MYTABLE


Because the first statement is part of an autmatic generated SQL of an application, i can't change it and i have to optimize the data model. How can i speed up the first statement?


Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 23 2002
Added on Sep 24 2002
5 comments
1,468 views