I can't beleive that nobody knows about this problem. I need any answer. Even if answer will be something like- SAP DB can't do it better now.
Dmitry Melekhov wrote:
Hello!
Some time ago Valery Zhuchenko posted the same answer.
But, because there is no replies I think that his message is not easy to understand.
So, I'll try to explain.
1. I created simple table:
create table test_table (f1 char(20), f2 char(20))
with following index:
create index test_index on test_table ( f2 asc)
I loaded some test data to table and
then I try do select from this table:
explain select f2 from test_table where f1='1'
TEST TEST_TABLE TEST_INDEX EQUAL CONDITION FOR INDEX 39
TEST RESULT IS NOT COPIED , COSTVALUE IS 3
All is OK. Optimizer uses index.
2. Then I try to select MAX(f2):
SELECT F1, MAX(F2) AS F2 FROM TEST_TABLE GROUP BY F1
TEST TEST_TABLE TEST_INDEX EQUAL CONDITION FOR INDEX 39
TEST RESULT IS COPIED , COSTVALUE IS 7
All is OK, optimizer uses index.
3. Then I create view:
create view test_view as select f1,max(f2) from test_table group by f1
and here optimizer fails: explain select f2 from test_outer_view where f1='1'
TEST TEST_TABLE TABLE SCAN 83
INTERNAL TEMPORARY RESULT TABLE SCAN 500
TEST RESULT IS COPIED , COSTVALUE IS 1298
As you see, optimizer do full table scan first and only after this it do MAX().
This is, certanly, makes SAP DB far (1000 times and more) slower than Oracle
(3 seconds, compared to 4 mins on the same data).
Is there any way to force optimizer to use index?
Thank you!
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
