I created two similar tables. I created a unique clusterd index on
both tables, but the order of the columns are different. One index has
the date first where the other index has the date last. I'm searching
the tables using the date...

++++++

unique clustered index

date
first_id
second_id

I get this in the plan:

Positioning by key.
Keys are:

++++++

unique clustered index

first_id
second_id
date

I get this in the query plan:

Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:

I noticed that I get the "Index contains all needed columns. Base
table will not be read." which is better performance-wise then not
getting it.

When creating indexes how do you guarantee not reading the base table.
It seems like the first case did have to read the base table?

Can you post the SQL and two query plans in full? Then we might be able to help further...

On Oct 26, 9:46*am, 1dg618 <1dg... (AT) gmail (DOT) com> wrote:

1. In order to answer fully, yes, we need the CREATE TABLE/INDEX
statements, and the SQL used.

2. If this is from your other question, then I will assume:
- both tables are APL
- both tables are ~700 million rows
- the only difference in the CI: columns are the same but the order is
as specified in your post
- the SQL used is identical, except for the table name.

The answer is:
- you cannot guarantee, but you can imply; then hint; then force ASE
to use the index only, and skip reading the "base table"
- but the fact the it refers to the "base table" implies that it is
using an NCI or the table is not APL. (see, we do need the CREATE
TABLEstatement.) ASE generally refers to the "base table as an
euphemism for the heap, not the CI.
- so, from the small info we have, it looks like you have a NCI on one
table and not the other (or one table is APL, the other is not).
Obviously when running tests like this, you need to ensure that you
have the same indices, etc, except for the specific difference that
you are testing for.
- it is always desirable to service the query from an index (any index
that can). This is called a Covered Query. The requirement of course
is, that all the sargs used must be columns in that one index, and no
data columns are requested. Tht is basically all you have to do.
- if you *know* you have an index that can service the query and avoid
the "base table", sure, you can instruct it to use the index via
FROM mytable (INEX index_name)

Regards
Derek

CREATE TABLE first
(
one_id INT NOT NULL,
two_id INT NOT NULL,
date DATETIME NOT NULL,
junk_id INT NOT NULL,
CONSTRAINT first_pkc_1
PRIMARY KEY CLUSTERED ( one_id, two_id, date, junk_id ) on
'default'
)
LOCK allpages ON 'default' partition BY roundrobin 1
GO

CREATE TABLE second
(
junk_id INT NOT NULL,
junk_desc varchar(255) NULL,
CONSTRAINT second_pkc_1
PRIMARY KEY CLUSTERED ( junk_id ) on 'default'
)
LOCK allpages ON 'default' partition BY roundrobin 1
GO

select
a.one_id,
a.two_id,
a.date,
b.junk_id
from
first a
left join second b on a.junk_id = b.junk_id
where
a.date >= '20091020'
and
a.date <= '20091120'
and
a.one_id = 120326
and
a.two_id = 1

QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
5 operator(s) under root

Total estimated I/O cost for statement 1 (at line 1): 77052.


The first table is filled with about 300 million records and the
second table is filled with about 2 million records.

According to the show plan, we it is using work tables and what do
work tables mean?

"Using Worktable2 for internal storage." and "Using Worktable1 for
internal storage."

Is there anything I can do to get rid of the work tables?

This is a completely different question. You have also posted this
new question in the other thread. I have answered there. What remains
for this thread is a minor clarification:

I previously stated:

And your QP is:

Although the CI B-Tree and leaf-level entries (which are the data
rows) are located in a single data structure, they are in separate
areas. I will assume the second is the APL table you have provided
partial DDL for. Here the QP is saying, it will read the B-Tree only,
and not read the leaf-level.

Regards
Derek