Oracle performance Issue

Question

Need help query performance.

I have a table A joining to a view and it is taking 7 seconds to get the results. But when i do select query on view i get the results in 1 seconds. I have created the indexes on the table A. But there is no improvements in the query.

SELECT 
  ITEM_ID, BARCODE, CONTENT_TYPE_CODE, DEPARTMENT, DESCRIPTION, ITEM_NUMBER, FROM_DATE,
  TO_DATE, CONTACT_NAME, FILE_LOCATION, FILE_LOCATION_UPPER, SOURCE_LOCATION,
  DESTRUCTION_DATE, SOURCE, LABEL_NAME, ARTIST_NAME, TITLE, SELECTION_NUM, REP_IDENTIFIER,
  CHECKED_OUT
FROM View B,
     table A  
where B.item_id=A.itemid 
  and status='VALID' 
  AND session_id IN ('naveen13122016095800') 
ORDER BY item_id,barcode;

CREATE TABLE A
(
  ITEMID       NUMBER,
  USER_NAME    VARCHAR2(25 BYTE),
  CREATE_DATE  DATE,
  SESSION_ID   VARCHAR2(240 BYTE),
  STATUS       VARCHAR2(20 BYTE)
)

CREATE UNIQUE INDEX A_IDX1 ON A(ITEMID);
CREATE INDEX A_IDX2 ON A(SESSION_ID);
CREATE INDEX A_IDX3 ON A(STATUS);'

Also include execution plan for the queries agaist both table and view. — atokpas, Dec 14 '16 at 07:23
CREATE TABLE A ( ITEMID NUMBER, USER_NAME VARCHAR2(25 BYTE), CREATE_DATE DATE, SESSION_ID VARCHAR2(240 BYTE), STATUS VARCHAR2(20 BYTE) ) — Mahi, Dec 14 '16 at 08:12
I have created unique index on itemid and non unique indexes on session_id and status. — Mahi, Dec 14 '16 at 08:13
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 500 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| A| 10 | 500 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- — Mahi, Dec 14 '16 at 08:18
Please edit question, add there definition of view, description of table and indexex. Query view is done in 1 sec, all records or first 100? — Kacper, Dec 14 '16 at 08:22

Thorsten Kettner · Answer 1 · 2016-12-14T09:54:31.057

So querying the view joined to a table is slower than querying the view alone? This is not surprising, is it?

Anyway, it doesn't make much sense to create separate indexes on the fields. The DBMS will pick one index (if any) to access the table. You can try a composed index:

CREATE UNIQUE INDEX A_IDX4 ON A(status, session_id, itemid);

But the DBMS will still only use this index when it sees an advantage in this over simply reading the full table. That means, if the DBMS expects to have to read a big amount of records anyway, it won't indirectly access them via the index.

At last two remarks concerning your query:

Don't use those out-dated comma-separated joins. They are less readable and more prone to errors than explicit ANSI joins (FROM View B JOIN table A ON B.item_id = A.itemid).
Use qualifiers for all columns when working with more than one table or view in your query (and A.status='VALID' ...).

UPDATE: I see now, that you are not selecting any columns from the table, so why join it at all? It seems you are merely looking up whether a record exists in the table, so use EXISTS or IN accordingly. (This may not make it faster, but a lot more readable at least.)

SELECT 
  ITEM_ID, BARCODE, CONTENT_TYPE_CODE, DEPARTMENT, DESCRIPTION, ITEM_NUMBER, FROM_DATE,
  TO_DATE, CONTACT_NAME, FILE_LOCATION, FILE_LOCATION_UPPER, SOURCE_LOCATION,
  DESTRUCTION_DATE, SOURCE, LABEL_NAME, ARTIST_NAME, TITLE, SELECTION_NUM, REP_IDENTIFIER,
  CHECKED_OUT
FROM View
WHERE itemid IN
(
  SELECT itemid
  FROM A
  WHERE status = 'VALID' 
  AND session_id IN ('naveen13122016095800')
)
ORDER BY item_id, barcode;

Oracle performance Issue

1 Answers1