一个棘手的Oracle Hint相关的问题。

Question

SELECT  count(*)
  FROM   (
                          SELECT   a.product_id
                                        ,a.member_id
                                        ,b.attr_value
                            FROM    lance_temp3 a,
                                         lance_temp2 b
                         WHERE    a.product_id = b.product_id
              ) m
WHERE   (LOWER(' '||m.ATTR_VALUE||' ') like '% oem %'
       OR    LOWER(' '||m.ATTR_VALUE||' ') like '% custom %';
上面是我的查询语句。lance_temp2表中数据量非常大，因此在进行like匹配的时候非常慢。我为了减少时间，首先与lance_temp3表进行关联，再进行筛选。可现在问题是，该语句的执行计划如下。由计划可以看出，仍然是先对lance_temp2进行筛选，再与lance_temp3表进行关联。请问各位大侠，用什么样的hint，才能让它先关联，再筛选。--------------------------------------------------------------------------------
---| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time
  |--------------------------------------------------------------------------------
---|   0 | SELECT STATEMENT    |             |     1 |   156 |     9  (12)| 00:00:01 ||   1 |  SORT AGGREGATE     |             |     1 |   156 |            |||*  2 |     HASH JOIN         |             |     1 |   156 |     9  (12)| 00:00:01 ||*  3 |        TABLE ACCESS FULL| LANCE_TEMP2 |     1 |   143 |     4   (0)| 00:00:01 ||    4 |        TABLE ACCESS FULL| LANCE_TEMP3 |     9 |   117 |     4   (0)| 00:00:01 |Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A"."PRODUCT_ID"="B"."PRODUCT_ID")
   3 - filter(LOWER(' '||"B"."ATTR_VALUE"||' ') LIKE '% oem %' OR LOWER('
              '||"B"."ATTR_VALUE"||' ') LIKE '% custom %' OR LOWER('

Answer 1

--leading用来指定驱动表，如下，指定lance_temp3为驱动表
SELECT COUNT(*)
  FROM (SELECT/*+leading(a)*/ a.product_id, a.member_id, b.attr_value
          FROM lance_temp3 a, lance_temp2 b
         WHERE a.product_id = b.product_id) m
 WHERE (LOWER(' ' || m.ATTR_VALUE || ' ') LIKE '% oem %' OR LOWER(' ' || m.ATTR_VALUE || ' ') LIKE '% custom %';

Answer 2

多谢楼上。可以试了一下，除了join的位置变了，还是先filter，再join的。没用啊

Answer 3

怎么先关联再筛选?那数据量不是更大?你把WHERE条件放到里层去,还有你的的OR,感觉快不到哪里去

Answer 4

我觉得是不是oracle认为，先做filter 后join比
先join 后filter  要好，所以才这样的。还有，你那个oem 和custom 是不是固定的如果是，可不可以换成instr 函数索引试试？？？
个人意见，，期待大侠！！！

Answer 5

回复3楼：另一个表的数量很少，因此关联后，其实记录量会少很多，这时再来like匹配，时间就少很多了

Answer 6

改写一下,欺骗优化器:
SELECT count(*)
  FROM (
  SELECT a.product_id
  ,a.member_id
  ,b.attr_value
  FROM lance_temp3 a,
  lance_temp2 b
  WHERE a.product_id = b.product_id
  ) m
WHERE (LOWER(' '||m.ATTR_VALUE || (case when 1=0 then m.product_id end)||' ') like '% oem %'
  OR LOWER(' '||m.ATTR_VALUE||(case when 1=0 then m.product_id end)||' ') like '% custom %';

Answer 7

1、lance_temp2表的product_id字段建索引没有？
2、lance_temp3表共多少行数据？

Answer 8

先关联再filter速度应该不会比先filter再关联要快，表关联的代价一般来说比单表like要大。LZ可以做个实验，先关联出数据存到临时表中，然后对临时表进行过滤，看看速度是不是比你的这个sql要快。