临时表的奇怪问题？希望各位ORACLE高手能出手相救！！！

[yzsind]

在对临时表进行Anaylse统计分析时不会产生统计结果，以至ORACLE执行时不会分析临时表的统计信息。如有一个物理表A记录有100000行，有一临时表B当前记录有5行，A表和B表对FIELD1字段都建有索引，对两个表进行JOIN 时总会出现物理表A全表扫描的现象，然后再用索引扫描临时表B，因此每次执行效率都比较低。具体SQL如下：
select * from a,b where a.field1=b.field1
不知道有没有什么办法使ORACLE执行时先进行临时表B的全表扫描，再进行物理表A的索引扫描。

[hdkkk]

家hint ，/*+index....*/

[hdkkk]

/*+ INDEX ( table [index [index]...] ) */

[lucipeter]

也就是说对A表进行强制使用index 来避免 Full scan

select /*+ index(a,a_index_1) */ * from a,b where a.field1=b.field1

[Drate]

用索引是一个好办法

Evaluating Oracle index access methods

Oracle9i offers a variety of indexing methods including b-tree, bitmapped, and function-based indexes. Regardless of the index structure, an Oracle index can be thought of as a pair bond of a symbolic key, paired with a ROWID.

The goal of Oracle index access is to gather the ROWIDs required to quickly retrieve the desired rows from the table. Within Oracle, we see the following types of index access.

Index Range Scan

The index range scan is one of the most common access methods. During an index range scan, Oracle accesses adjacent index entries and then uses the ROWID values in the index to retrieve the table rows.

An example of an index range scan would be the following query.

select
employee_name
from
employee
where
home_city = ‘Rocky Ford’;

In practice, many Oracle SQL tuning professionals will resequence the table rows into the same physical order as the primary index. This technique can reduce disk I/O on index range scans by several orders of magnitude. For details, see "Turning the Tables on Disk I/O" in the January/February 2000 issue of Oracle Magazine online.

Fast Full-index Scan

Index full scans are sometimes called fast full-index scans, which were introduced in Oracle 7.3. There are some SQL queries that can be resolved by reading the index without touching the table data. For example, the following query does not need to access the table rows, and the index alone can satisfy the query.

select distinct
color,
count(*)
from
automobiles
group by
color;

Oracle enhanced the fast full-index scan to make it behave similar to a full-table scan. Just as Oracle has implemented the initialization parameter db_file_multiblock_read_count for full-table scans, Oracle allows this parameter to take effect when retrieving rows for a fast full-index scan. Since the whole index is accessed, Oracle allows multi-block reads.

There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.

All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.

The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism.

You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

You can also force a fast full-index scan by specifying the index_ffs hint, and this is commonly combined with the parallel_index hint to improve performance. For example, the following query forces the use of a fast full-index scan with parallelism:

select distinct /*+ index_ffs(c,pk_auto) parallel_index(automobile, pk_auto)
color,
count(*)
from
automobiles
group by
color;

It is not always intuitive whether a fast full-index scan is the fastest way to service a query, because of all of the variables involved. Hence, most expert SQL tuners will time any query that meets the fast full-index scan criteria and see if the response time improves.

If you like Oracle tuning, you might enjoy my latest book “Creating a Self-tuning Oracle Database” by Rampant TechPress. It’s only $9.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

www.rampant.cc/book_oracle9i_sga.htm

我想上面的文章可能对你有点帮助 。。

[yzsind]

我试了一下，可以，但不知道在删除语句中如何解决，具体SQL如下
delete from a where a.hbs_bh in (select hbs_bh from b where a.hbs_bh=b.hbs_bh)

[LGQDUCKY]

还是要用上所引，才能提高效率。