CODEWITHSUNDEEP
oracle-databaseperformancequery-performancesql-tuning
Mahi
Mahi

Reputation: 73

Oracle performance Issue

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);'

Upvotes: 0

Views: 76

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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;

Upvotes: 1

Related Questions