通过询问项目组人员，得知，该表收集受监听系统时间点数据，更做相应更新操作，列名不变，查询语句中条件语句列RESOURCEID是唯一的，建议项目组添加索引，索引信息如下
SQL> select owner,index_name,index_type,table_name from dba_indexes where table_name='ORACLETABLEAVAILABLE';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ ------------------------------ --------------------------- ------------------------------
XXXDBA P_ORACLETABLEAVAILABLE NORMAL ORACLETABLEAVAILABLE
SQL> select dbms_metadata.get_ddl('INDEX','P_ORACLETABLEAVAILABLE','XXXDBA') from dual;
DBMS_METADATA.GET_DDL('INDEX','P_ORACLETABLEAVAILABLE','XXXDBA')
--------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX "XXXDBA"."P_ORACLETABLEAVAILABLE" ON "XXXDBA"."ORACLETABLEAVAILABLE" ("RESOURCEID", "TIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PERFORMANCE"
再次查看执行计划，这速度，杠杠的
SQL> explain plan for SELECT V0900,H,L,A,to_char(HTIME,'yyyy-mm-dd hh24:mi:ss'),to_char(LTIME,'yyyy-mm-dd hh24:mi:ss')
2 FROM XXXDBA.ORACLETABLEAVAILABLE
3 WHERE RESOURCEID = :1 AND TIME = to_date(:2,'yyyy-mm-dd')
4 ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1279632247
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ORACLETABLEAVAILABLE | 1 | 65 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | P_ORACLETABLEAVAILABLE | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RESOURCEID"=:1 AND "TIME"=TO_DATE(:2,'yyyy-mm-dd'))
14 rows selected.