Doug,
I noticed that a portion of my query was removed by the UT engine. Probably because it delimited it with angle brackets. I should have known better. The query should have been
SELECT * FROM app1 WITH INDEX(indexname) WHERE name_licensee LIKE 'tab%'
Substitute indexname with the name of the index on the name_licensee table.
Would you ask your friend to rerun the test if he left the INDEX() operator emtpy?
Very strange...
I'm very surprised by these results. I'd swear that the first query is being solved with a table scan. The second is using an index. Why does the optimizer decided to use the index.
>Results from 1st query:
>Table 'appl'. Scan count 1, logical reads 13158, physical reads 0,
>read-ahead reads 11697.
>
>Results from 2nd query: (much faster performance, BTW)
>Table 'appl'. Scan count 1, logical reads 522, physical reads 16, read-ahead
>reads 2.
Please ask your friend to run this:
SET SHOWPLAN_TEXT ON
SELECT * FROM appl WHERE name_licensee LIKE 'tab%'
SET SHOWPLAN_TEXT OFF
-Mike