All：

目前索引结构：

SQL> select index_name,table_name,column_name from dba_ind_columns where lower(table_name) in ('feed_items','feed_schema','feed_string_attributes')

INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
PK_FEED_ITEMS FEED_ITEMS FEED_ITEM_ID
PK_FEED_SCHEMA FEED_SCHEMA FEED_SCHEMA_ID
PK_FEED_STRING_ATTRIBUTES FEED_STRING_ATTRIBUTES FEED_STRING_ATTRIBUTE_ID
UI_FEED_STRING_ATTRIBUTES_NBK FEED_STRING_ATTRIBUTES JOYO_ATTRIBUTE_ID
UI_FEED_STRING_ATTRIBUTES_NBK FEED_STRING_ATTRIBUTES ORDINAL_NUMBER
UI_FEED_STRING_ATTRIBUTES_NBK FEED_STRING_ATTRIBUTES FEED_ITEM_ID

6 rows selected.

执行计划：

select
feed.feed_item_id,feed.feed_schema_id feed_schema_id, sch.vendor_code, attr.attribute_value vendor_product_id
from
MATCHING_PROD_USER.feed_items feed,
MATCHING_PROD_USER.feed_schema sch,
MATCHING_PROD_USER.feed_string_attributes attr
where
feed.FEED_SCHEMA_ID=sch.feed_schema_id
and feed.FEED_ITEM_ID = attr.FEED_ITEM_ID
and attr.JOYO_ATTRIBUTE_ID=1
and feed.process_status_id =1
and rownum<50

Elapsed: 00:00:26.93

Execution Plan
----------------------------------------------------------
Plan hash value: 598078541

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 3626 | 115 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN | | 1627K| 114M| 115 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL | FEED_SCHEMA | 3 | 24 | 9 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 50 | 1650 | 105 (0)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | FEED_STRING_ATTRIBUTES | 51 | 1071 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| FEED_ITEMS | 1 | 12 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_FEED_ITEMS | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<50)
2 - access("FEED"."FEED_SCHEMA_ID"="SCH"."FEED_SCHEMA_ID")
5 - filter("ATTR"."JOYO_ATTRIBUTE_ID"=1)
6 - filter("FEED"."PROCESS_STATUS_ID"=1)
7 - access("FEED"."FEED_ITEM_ID"="ATTR"."FEED_ITEM_ID")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5525557 consistent gets
79720 physical reads
0 redo size
2503 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed

目前还不是很清楚各个表的结构，建议考虑索引的修改：

黑色的复合索引是否需要分开
在column feed_items.feed_schema_id 上增加索引

测试了下创建2个索引的效果，供参考：红色字体的表只有3条数据，全表扫描访问，没有考虑再次优化。consistent gets = 4907552 还是很高，估计可能和虚拟索引有关系。

SQL> create index idx_feed_string on FEED_STRING_ATTRIBUTES(FEED_ITEM_ID) nosegment;

Index created.

Elapsed: 00:00:00.10

SQL> create index idx_feed_items_schema_id on feed_items(feed_schema_id) nosegment;

Index created.

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

Elapsed: 00:00:00.12

49 rows selected.

Elapsed: 00:00:21.89

Execution Plan
----------------------------------------------------------
Plan hash value: 3268655428

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 2009 | 78 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | FEED_STRING_ATTRIBUTES | 1 | 21 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 50 | 2050 | 78 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 39 | 780 | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | FEED_SCHEMA | 3 | 24 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| FEED_ITEMS | 39 | 468 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_FEED_ITEMS_SCHEMA_ID | 50 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_FEED_STRING | 9 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<50)
2 - filter("ATTR"."JOYO_ATTRIBUTE_ID"=1)
6 - filter("FEED"."PROCESS_STATUS_ID"=1)
7 - access("FEED"."FEED_SCHEMA_ID"="SCH"."FEED_SCHEMA_ID")
8 - access("FEED"."FEED_ITEM_ID"="ATTR"."FEED_ITEM_ID")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4907552 consistent gets
47897 physical reads
0 redo size
2503 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed

/********************简单调试后****************************/

Hi：

我们可以试用下如下sql：

select /*+use_nl(feed) index(attr UI_FSA_NBK) index(feed I_FI_PROCSTATUSID)*/
feed.feed_item_id,feed.feed_schema_id feed_schema_id,sch.vendor_code,
attr.attribute_value vendor_product_id
from
MATCHING_PROD_USER.feed_items feed,
MATCHING_PROD_USER.feed_schema sch,
MATCHING_PROD_USER.feed_string_attributes attr
where
feed.FEED_SCHEMA_ID=sch.feed_schema_id
and feed.FEED_ITEM_ID = attr.FEED_ITEM_ID
and attr.JOYO_ATTRIBUTE_ID=1
and feed.process_status_id =1
and rownum<50

49 rows selected.

Elapsed: 00:00:00.30

Execution Plan
----------------------------------------------------------
Plan hash value: 1653673368

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 2009 | 122 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | FEED_STRING_ATTRIBUTES | 1 | 21 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 50 | 2050 | 122 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 39 | 780 | 10 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | FEED_SCHEMA | 3 | 24 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| FEED_ITEMS | 39 | 468 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_FI_PROCSTATUSID | 118 | | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UI_FSA_NBK | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<50)
6 - filter("FEED"."FEED_SCHEMA_ID"="SCH"."FEED_SCHEMA_ID")
7 - access("FEED"."PROCESS_STATUS_ID"=1)
8 - access("FEED"."FEED_ITEM_ID"="ATTR"."FEED_ITEM_ID" AND "ATTR"."JOYO_ATTRIBUTE_ID"=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6696 consistent gets
0 physical reads
0 redo size
2504 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed

MATCHING_PROD_USER.feed_items

目前相关表的索引结构：

SQL> select index_name,table_name,column_name from dba_ind_columns where lower(table_name) in ('feed_items','feed_schema','feed_string_attributes');

INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
PK_FEED_ITEMS FEED_ITEMS FEED_ITEM_ID
PK_FEED_SCHEMA FEED_SCHEMA FEED_SCHEMA_ID
PK_FEED_STRING_ATTRIBUTES FEED_STRING_ATTRIBUTES FEED_STRING_ATTRIBUTE_ID
I_FI_PROCSTATUSID FEED_ITEMS PROCESS_STATUS_ID
UI_FSA_NBK FEED_STRING_ATTRIBUTES ORDINAL_NUMBER
UI_FSA_NBK FEED_STRING_ATTRIBUTES FEED_ITEM_ID
UI_FSA_NBK FEED_STRING_ATTRIBUTES JOYO_ATTRIBUTE_ID

7 rows selected.

/*************************其他写法*************************/

再提供两种SQL写法，供参考！

49 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1983741984

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 2058 | 97 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | FEED_STRING_ATTRIBUTES | 2 | 44 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 50 | 2100 | 97 (0)| 00:00:02 |
|* 4 | HASH JOIN | | 31 | 620 | 9 (12)| 00:00:01 |
| 5 | TABLE ACCESS FULL | FEED_SCHEMA | 3 | 24 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| FEED_ITEMS | 93 | 1116 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_FI_PROCSTATUSID | 6884 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UI_FSA_NBK | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<50)
4 - access("FEED"."FEED_SCHEMA_ID"="SCH"."FEED_SCHEMA_ID")
7 - access("FEED"."PROCESS_STATUS_ID"=1)
8 - access("FEED"."FEED_ITEM_ID"="ATTR"."FEED_ITEM_ID" AND "ATTR"."JOYO_ATTRIBUTE_ID"=1)

Statistics
----------------------------------------------------------
223 recursive calls
0 db block gets
205 consistent gets
2 physical reads
0 redo size
2505 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
49 rows processed

select /*index(attr UI_FSA_NBK) index(feed I_FI_PROCSTATUSID)*/
feed.feed_item_id,feed.feed_schema_id feed_schema_id,sch.vendor_code,
attr.attribute_value vendor_product_id
from
MATCHING_PROD_USER.feed_items feed,
MATCHING_PROD_USER.feed_schema sch,
MATCHING_PROD_USER.feed_string_attributes attr
where
feed.FEED_SCHEMA_ID=sch.feed_schema_id
and feed.FEED_ITEM_ID = attr.FEED_ITEM_ID
and attr.JOYO_ATTRIBUTE_ID=1
and feed.process_status_id =1
and rownum<50

49 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1983741984

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 2058 | 97 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | FEED_STRING_ATTRIBUTES | 2 | 44 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 50 | 2100 | 97 (0)| 00:00:02 |
|* 4 | HASH JOIN | | 31 | 620 | 9 (12)| 00:00:01 |
| 5 | TABLE ACCESS FULL | FEED_SCHEMA | 3 | 24 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| FEED_ITEMS | 93 | 1116 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_FI_PROCSTATUSID | 6884 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UI_FSA_NBK | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<50)
4 - access("FEED"."FEED_SCHEMA_ID"="SCH"."FEED_SCHEMA_ID")
7 - access("FEED"."PROCESS_STATUS_ID"=1)
8 - access("FEED"."FEED_ITEM_ID"="ATTR"."FEED_ITEM_ID" AND "ATTR"."JOYO_ATTRIBUTE_ID"=1)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
165 consistent gets
0 physical reads
0 redo size
2505 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed

select /*+use_nl(feed) index(attr UI_FSA_NBK) index(feed I_FI_PROCSTATUSID)*/
feed.feed_item_id,feed.feed_schema_id feed_schema_id,sch.vendor_code,
attr.attribute_value vendor_product_id
from
MATCHING_PROD_USER.feed_items feed,
MATCHING_PROD_USER.feed_schema sch,
MATCHING_PROD_USER.feed_string_attributes attr
where
feed.FEED_SCHEMA_ID=sch.feed_schema_id
and feed.FEED_ITEM_ID = attr.FEED_ITEM_ID
and attr.JOYO_ATTRIBUTE_ID=1
and feed.process_status_id =1
and rownum<50

49 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1653673368

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 2058 | 98 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | FEED_STRING_ATTRIBUTES | 2 | 44 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 50 | 2100 | 98 (0)| 00:00:02 |
| 4 | NESTED LOOPS | | 31 | 620 | 9 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | FEED_SCHEMA | 3 | 24 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| FEED_ITEMS | 31 | 372 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_FI_PROCSTATUSID | 93 | | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UI_FSA_NBK | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<50)
6 - filter("FEED"."FEED_SCHEMA_ID"="SCH"."FEED_SCHEMA_ID")
7 - access("FEED"."PROCESS_STATUS_ID"=1)
8 - access("FEED"."FEED_ITEM_ID"="ATTR"."FEED_ITEM_ID" AND "ATTR"."JOYO_ATTRIBUTE_ID"=1)

Statistics
----------------------------------------------------------
113 recursive calls
0 db block gets
307 consistent gets
0 physical reads
3164 redo size
2504 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
49 rows processed