Re: How to get 8i to perform as well as 9i

marothwell_at_yahoo.com (Michael R) wrote in message news:<b2b3ca26.0407271016.255c2f9c_at_posting.google.com>... <snip>
> 8i:
>
> SELECT STATEMENT
> HASH JOIN
> INDEX (FAST FULL SCAN), CATEGORY_IDX (SPI)
> HASH JOIN
> INDEX (FULL SCAN), PART_TYPE_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), KEYWORD_IDX (SPI)
> HASH JOIN (OUTER)
> HASH JOIN
> INDEX (FAST FULL SCAN), SPI_PRODUCT_PARENT_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), PRODUCT_SPI_PRODUCT_PK (SPI)
> HASH JOIN
> TABLE ACCESS (FULL), PART (SPI)
> MERGE JOIN
> SORT (JOIN)
> HASH JOIN
> INDEX (FAST FULL SCAN), PRODUCT_RELEASE_IDX
> (SPI)
> TABLE ACCESS (FULL), STRUCTURE (SPI)
> SORT (JOIN)
> INDEX (FAST FULL SCAN), STRUCTURE_CATEGORY_PK
> (SPI)
> INDEX (FAST FULL SCAN), DESCRIPTION_IDX (SPI)
>
>
> 9i:
>
> SELECT STATEMENT
> HASH JOIN
> INDEX (FAST FULL SCAN), SPI_PRODUCT_PARENT_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), PRODUCT_SPI_PRODUCT_PK (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), CATEGORY_IDX (SPI)
> HASH JOIN
> INDEX (FAST FULL SCAN), KEYWORD_IDX (SPI)
> HASH JOIN (OUTER)
> HASH JOIN
> HASH JOIN
> TABLE ACCESS (FULL), PRODUCT (SPI)
> HASH JOIN
> HASH JOIN
> INDEX (FULL SCAN), PART_TYPE_IDX (SPI)
> TABLE ACCESS (FULL), PART (SPI)
> TABLE ACCESS (FULL), STRUCTURE (SPI)
> INDEX (FAST FULL SCAN), STRUCTURE_CATEGORY_PK (SPI)
> INDEX (FAST FULL SCAN), DESCRIPTION_IDX (SPI)
>

my reading says that 8i SORTs twice, 9i not at all. can't say how to get rid of them, but generally sort kicks the crap out of a machine.

robert Received on Wed Jul 28 2004 - 14:14:40 CDT