select a.a1,a.a2
from (select t1.c1 a1,t1.c2 a2 ,t2.c2 a3 from t1,t2 where t1.c1 = t2.c1) a
where a.a2 = a.a3;应该快不少，试试。
from (select t1.c1 a1,t1.c2 a2 ,t2.c2 a3 from t1,t2 where t1.c1 = t2.c1) a
where a.a2 = a.a3;应该快不少，试试。
调试欢乐多
要么建索引 或是用 连接之类的
最好看看你的SQL先 没理由会这么慢的
2.在执行查询语句,可以修改
SELECT * FROM T1 AS A WHERE EXISTS (SELECT * FROM T2 WHERE C1 = A.C1 AND C2 = A.C2)
我用我的sql语句去执行很慢。
POLOM(北岸WUS）和 tj_dns(愉快的登山者)，你们利用子查询去缩小查询范围和EXISTS 语句的功能我都试试过。一个字还是慢。
在百般无奈的情况下，我只能请教了我们的国外同行给出的答案让我很吃惊。那样的语法我重来没见
过，现在写出来希望对各位也有帮助，同时希望如果这个写过的朋友，能提提自己的理解。SELECT /*+ INDEX (T1 PK_T1) */ *
FROM T1,T2
WHERE T1.C1 = T2.C1
AND T1.C2 = T2.C2;注意上面的/**/不是注释。其实还是索引的问题。
You can use comments in a SQL statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement. A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The syntax below shows hints contained in both styles of comments that Oracle supports within a statement block. {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
where DELETE INSERT SELECT UPDATE
is a DELETE, INSERT, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.
+
is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter (no space is permitted).
hint
is one of the hints discussed in this section and in Oracle8i Tuning. The space between the plus sign and the hint is optional. If the comment contains multiple hints, separate the hints by at least one space.
text
is other commenting text that can be interspersed with the hints.
Table 2-15 lists hint syntax and descriptions. For more information on hints, see Oracle8i Tuning and Oracle8i Concepts. Table 2-15 Hint Syntax and Descriptions
Hint Syntax Description
Optimization Approaches and Goals
/*+ ALL_ROWS */
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
/*+ CHOOSE */
Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement.
/*+ FIRST_ROWS */
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).
/*+ RULE */
Explicitly chooses rule-based optimization for a statement block.
Access Methods
/*+ AND_EQUAL(table index) */
Explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
/*+ CLUSTER(table) */
Explicitly chooses a cluster scan to access the specified table.
/*+ FULL(table) */
Explicitly chooses a full table scan for the specified table.
/*+ HASH(table) */
Explicitly chooses a hash scan to access the specified table.
/*+ HASH_AJ(table) */
Transforms a NOT IN subquery into a hash anti-join to access the specified table.
/*+ HASH_SJ(table) */
Transforms a NOT IN subquery into a hash semi-join to access the specified table.
/*+ INDEX(table index) */
Explicitly chooses an index scan for the specified table.
/*+ INDEX_ASC(table index) */
Explicitly chooses an ascending-range index scan for the specified table.
/*+ INDEX_COMBINE(table index) */
If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate. If particular indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.
/*+ INDEX_DESC(table index) */
Explicitly chooses a descending-range index scan for the specified table.
/*+ INDEX_FFS(table index) */
Causes a fast full index scan to be performed rather than a full table scan.
/*+ MERGE_AJ(table) */
Transforms a NOT IN subquery into a merge anti-join to access the specified table.
/*+ MERGE_SJ(table) */
Transforms a correlated EXISTS subquery into a merge semi-join to access the specified table.
/*+ NO_EXPAND */
Prevents the optimizer from considering OR expansion for queries having OR or IN conditions in the WHERE clause.
/*+ NO_INDEX(table index) */
Instructs the optimizer not to cons
Disables query rewrite for the query block, overriding a TRUE setting of the QUERY_REWRITE_ENABLED parameter.
/*+ ORDERED_PREDICATES */
Forces the optimizer to preserve the order of predicate evaluation (except predicates used in index keys), as specified in the WHERE clause of SELECT statements.
/*+ REWRITE (view [,...]) */
Enforces query rewrite. If you specify a view list and the list contains an eligible materialized view, Oracle will use that view regardless of the cost. No views outside of the list are considered. If you do not specify a view list, Oracle will search for an eligible materialized view and always use it regardless of the cost.
/*+ ROWID(table) */
Explicitly chooses a table scan by rowid for the specified table.
/*+ USE_CONCAT */
Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator.
Join Orders
/*+ ORDERED */
Causes Oracle to join tables in the order in which they appear in the FROM clause.
/*+ STAR */
Forces the large table to be joined last using a nested-loops join on the index.
Join Operations
/*+ DRIVING_SITE(table) */
Forces query execution to be done at a different site from that selected by Oracle.
/*+ USE_HASH(table) */
Causes Oracle to join each specified table with another row source with a hash join.
/*+ USE_MERGE(table) */
Causes Oracle to join each specified table with another row source with a sort-merge join.
/*+ USE_NL(table) */
Causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table.
Parallel Execution
Note: Oracle ignores parallel hints on a temporary table. For more information on temporary tables, see "CREATE TABLE" and Oracle8i Concepts.
/*+ APPEND */ /*+ NOAPPEND */
Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.
/*+ NOPARALLEL(table) */
Disables parallel scanning of a table, even if the table was created with a PARALLEL clause. Restriction: You cannot parallelize a query involving a nested table.
/*+ PARALLEL(table) /*+ PARALLEL(table, integer) */
Lets you specify parallel execution of DML and queries on the table; integer specifies the desired degree of parallelism, which is the number of parallel threads that can be used for the operation. Each parallel thread may use one or two parallel execution servers. If you do not specify integer, Oracle computes a value using the PARALLEL_THREADS_PER_CPU parameter. If no parallel hint is specified, Oracle uses the existing degree of parallelism for the table. DELETE, INSERT, and UPDATE operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION ENABLE PARALLEL DML to enter this mode.)
/*+ PARALLEL_INDEX
Allows you to parallelize fast full index scans for partitioned and nonpartitioned indexes that have the PARALLEL attribute.
/*+ PQ_DISTRIBUTE (table, outer_distribution, inner_distribution) */
Specifies how rows of joined tables should be distributed between producer and consumer query servers. The four possible distribution methods are NONE, HASH, BROADCAST, and PARTITION. However, only a subset of the combinations of outer and inner distributions are valid. For the permitted combinations of distributions for the outer and inner join tables, see Oracle8i Tuning.
/*+ NOPARALLEL_INDEX */
Overrides a PARALLEL attribute setting on an index.
Other Hints
/*+ CACHE */
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed.
/*+ NOCACHE */
Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed.
/*+ MERGE(table) */
Causes Oracle to evaluate complex views or subqueries before the surrounding query.
/*+ NO_MERGE(table) */
Causes Oracle not to merge mergeable views.
/*+ PUSH_JOIN_PRED(table) */
Causes the optimizer to evaluate, on a cost basis, whether to push individual join predicates into the view.
/*+ NO_PUSH_JOIN_PRED(table) */
Prevents pushing of a join predicate into the view.
/*+ PUSH_SUBQ */
Causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
/*+ STAR_TRANSFORMATION */
Makes the optimizer use the best plan in which the transformation has been used.