At 05:53 AM 20/01/2009, you wrote:

>_tidx_128_30a INDEX ON TableauExtract(Object Source)
>
>So, here is the first question:
>
>-----
>When I connect to the extract and drag [Object Source] to rows, we run:
>
>Query 1: Trying to get the domain of [Object Source] by GROUP BY
> SELECT ("TableauExtract"."Object Source") AS "none:Object Source:nk"
> FROM "TableauExtract"
> GROUP BY 1
> </QUERY>
> <PLAN>
> PLAN (TableauExtract ORDER _tidx_128_30a)
> </PLAN>
> [Time] Running the query took 4.2705 sec.
> [Time] Getting the records took 0.0002 sec.
>
>It takes roughly 4 seconds to table scan this data source so it
>appears that this is doing a table scan based on time versus
>retrieving the unique keys from the index.

No. What it's doing here is constructing the whole set, which is a derived field (derived from the expression AS "none:Object Source:nk") and sorting that. In so doing, it recalculates the whole set and uses the index to assist in sorting that.

>That makes sense to me when
>we include a measure, such as in the following, since computing the
>measure will require touching all the records. But in the above, we
>are only operating on the key of the index ­ why touch the data pages?

Because the output of the *retrieval* is expression output, not indexed data. Sorts operate on that output. Here you are giving the query a double-whammy.

>So the bottom line is: What is the fastest way to determine the
>members of a column in a local database? Is there any way (e.g. by
>fiddling with transaction settings) to avoid a table scan?

Do queries that can be optimized by indexes. Amongst others, that means not trying to do sorts (including DISTINCTs) on the output of expressions.

./heLen