Re: Parallel Query Question

Steve,
Remember that a 'rowid' is a three part address - file, block, index into block directory. Index blocks have directories too, so it is meaningful (though newish) to talk about the rowid of an index entry.

The following is a sample of code (in a partitioned table on 8.0.5) where the table and the index are declared as parallel.

The partitioned table is partitioned by sales_dt, and its PK starts with sales_dt (I didn't do the index design).

select count(*)
from sales partition (wds_2000_008)
where

        sales_dt >= to_date('14-feb-2000','dd-mon-yyyy')
and     sales_dt < to_date('21-feb-2000','dd-mon-yyyy')

Plan:
SORT (aggregate)
  INDEX UNIQUE DSS_OWNER SALES_PK (fast full scan) PARALLEL_TO_SERIAL

Parallel Section of plan:
SELECT /*+ PIV_SSF */ COUNT(*)
FROM :I."SALES"."SALES_PK" PARTITION(42) A1 WHERE

        ROWID BETWEEN :B1 AND :B2
AND     A1."SALES_DT"<:B3
AND     A1."SALES_DT">=:B4

Note the funny ':I' which is not a bind variable, and seems to indicate that SALES.SALE_PK is {table_name}.{index_name} rather than {owner}.{table_name}.

There is a bug in 8.0 at least which means that this throws an error if SALES is a public synonym.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Steve Adams wrote in message <38b35673.508042256_at_news.eagles.bbs.net.au>...
>Hi Jonathan,
>
>I don't think this is right. Parallel table scans work by getting each
>slave to scan a rowid range using an SQL statement with the ROWID hint.
>Unless there is some equivalent hint to do a partial fast full scan, I
>don't see how a fast full scan could be parallelized. Am I missing
>something?
>
>Regards,
>Steve Adams
>http://www.ixora.com.au/
>http://www.oreilly.com/catalog/orinternals/
>http://www.christianity.net.au/
>
>
>On Sun, 20 Feb 2000 19:21:56 -0000, "Jonathan Lewis"
><jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>
>>If you use the hint INDEX_FFS along with
>>a parallel hint I think it is possible to get
>>a parallel scan on the index.
>>
>
Received on Wed Feb 23 2000 - 12:16:50 CST