I remembered having some troubles some years ago with NOT NULL, table scans,
and MS SQL server 6.5, so I made a little test. I created a table 'TEST',
with columns NAME (varchar, primary key)and AGE ( int, indexed to index I1),
and put some values in. Now, if I do "explain select * from test where
name='max'" , result is OK,
EQUAL CONDITION FOR KEY COLUMN
(USED KEY COLUMN)
RESULT IS COPIED , COSTVALUE IS
However, when I try "explain select * from test where age=15", result is :
TABLE SCAN
RESULT IS NOT COPIED , COSTVALUE IS
The only possible explanation for this I've found in documentation ( topic
Optimizer-> Search strategy-> Sequential search ) is :
<begin quote>If the possible non-sequential search strategies would be more
costly than the sequential search, the table is processed sequentially. <end
quote>
Does that mean that, even if an index exists and the search criteria
qualifies for using the index, it can be ignored ? How is this decision
taken ? When the index isn't ignored ?
All best,
Dan
If you try to fail, and succeed, which have you done?
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, July 12, 2003 0:41 AM
Subject: More questions on query optimization
> Please forgive the repost, but does anyone have any ideas on this? Just
an
> explanation would be very appreciated!
>
> Many thanks,
>
> David
>
> ---------------------- Forwarded by David Hay/Lex/Lexmark on 11/07/2003
> 05:40 PM ---------------------------
>
>
> David Hay
> 03/07/2003 02:46 PM
>
> To: [EMAIL PROTECTED]
> cc:
>
> Subject: More questions on query optimization (Document link: David
> Hay)
>
> Hi,
>
> Still playing around with performance etc. with my queries, and ran into
an
> interesting scenario which surprised me...
>
> I have a query that joins three tables, a metrics table that holds all the
> reporting info - upto 100,000 records - along with a forms table and
kiosks
> table that have approx 1000 and 100 records respectively.
>
> When I did an explain on my original join:
>
> explain SELECT Forms.Title, Kiosks.Name, Metrics.Copies FROM Metrics left
> JOIN Kiosks ON Metrics.Kiosk_Id = Kiosks.Id INNER JOIN Forms ON
> Metrics.Form_Id = Forms.Id WHERE ExecTime IS NOT NULL AND Copies IS NOT
> NULL ORDER BY Forms.UpperTitle, Kiosks.UpperName
>
> KIOSK METRICS TABLE SCAN
1021
> KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
> KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE
INDEXED COL. 3
> ID (USED INDEX COLUMN)
> KIOSK RESULT IS COPIED ,
COSTVALUE IS 3452
>
>
> Although I have everything indexed I can think of on the metrics table, I
> cannot prevent the table scan. Currently I have an index of kiosk_id,
> form_id, ExecTime and Copies.
>
> It appears that the optimizer is not performing the WHERE clause on the
> Metrics table before it does the join, which doesn't make sense to me.
>
> When I force this with the following query:
>
> explain SELECT Forms.Title, Kiosks.Name, t1.Copies FROM (select kiosk_id,
> form_id, Copies from Metrics WHERE ExecTime IS NOT NULL AND Copies IS NOT
> NULL ) t1 left JOIN Kiosks ON t1.Kiosk_Id = Kiosks.Id INNER JOIN Forms ON
> t1.Form_Id = Forms.Id ORDER BY Forms.UpperTitle, Kiosks.UpperName
>
> I get a better cost value, but I still can't get rid of the table scan:
>
> KIOSK METRICS TABLE SCAN
947
> INTERNAL TEMPORARY RESULT TABLE SCAN
500
> KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
> KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF
MULTIPLE INDEXED COL. 3
> ID (USED INDEX
COLUMN)
> KIOSK RESULT IS
COPIED , COSTVALUE IS 1152
>
>
> Like I've said before, I feel like I am shooting somewhat in the dark, and
> just playing with various combinations. I never thought a subselect would
> be better than a regular join!
>
> Please can anyone point me in the right direction to get rid of the
metrics
> table scan? Or point out any other ways to optimize this better?
>
> Many thanks,
>
> David
>
>
>
>
>
>
>
>
>
>
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
