Why does my query do full scan on table ...?

Hi,

Can you please post the query plan you get for this query. It will also be helpful to know how many total rows are in the XPROCESS_TPL table. In addition, please make sure to gather stats on the application table (dbms_stats.gather_table_stats()) and semantic network (sem_perf.gather_stats() as sysdba).

Thanks,
Matt

The query plan is the execution plan generated by the query optimizer. One way of viewing the query plan is using explain plan through SQL*Plus:

SQL> explain plan for

select empno, ename, dname, loc

from emp e, dept d

where e.deptno = d.deptno;

  2    3    4

Explained.

Elapsed: 00:00:00.05

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic,parallel,partition,predicate'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2219294842

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|*  1 |  HASH JOIN         |      |

|   2 |   TABLE ACCESS FULL| DEPT |

|   3 |   TABLE ACCESS FULL| EMP  |

-----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")

15 rows selected.

Elapsed: 00:00:00.47

SQL>

If you run the query as a SELECT instead of a DELETE, how long does it take and how many rows are returned?

SELECT COUNT(*)

FROM XPROCESS_TPL  t

WHERE t.triple.rdf_s_id

IN

( SELECT r.value_id FROM mdsys.rdf_value$ r

  WHERE  r.value_type='UR'   AND  r.vname_suffix LIKE  'ProcessAggregate_Lens Fab%' )

Ok. I see.

I think the query plan is reasonable. 120,000 rows is a bit too much for index-based nested loop join, so hash join is probably the best approach in this case. Let's try with a smaller result for the inner query:

SELECT COUNT(*)

FROM XPROCESS_TPL  t

WHERE t.triple.rdf_s_id

IN

( SELECT r.value_id FROM mdsys.rdf_value$ r

  WHERE  r.value_type='UR'   AND  r.vname_suffix LIKE  'ProcessAggregate_Lens Fab%'

                AND rownum <= 100)

Thanks. Please try increasingly larger values for the inner query and see where it blows up (e.g. 1K, 10K, 25K, etc.), and note any query plan changes for slow vs fast queries.

It's also a good idea to check the speed of a full table scan on your application table to see if there are any obvious performance problems there:

select max(t.triple.rdf_s_id), max(t.triple.rdf_p_id), max(t.triple.rdf_c_id)

from XPROCESS_TPL t;

I get about 25 seconds for a 138M row application table on an older desktop machine.