Oracle Selecting Index Optimisation

I need to optimise this query by using indexing. I tried to index some of the columns, but it is not helping. Is there anyone have thoughts?

The query I need to optimise:

Select vintage, wine_no, wname, pctalc, grade, price, wine.vid, vname, wine.cid, cname
from vineyard, class, wine
where wine.vid = vineyard.vid
and wine.cid = class.cid
and wine.cid = 'SHIRAZ' and grade = 'A';

I tried to created the following indexes: ”’create index wine_vid_idx on wine(vid); create index wine_cid_idx on wine(cid); create index wine_grade_idx on wine(grade);“`

My execution plan for the original query is:

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    42 |  9114 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN                    |              |    42 |  9114 |    10   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |    42 |  6930 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CLASS    |     1 |    50 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | SYS_C0027457 |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS CLUSTER       | WINE    |    42 |  4830 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
---------------------------------------------------------------------------------------------
|   6 |   TABLE ACCESS FULL           | VINEYARD |   160 |  8320 |     8   (0)| 00:00:01 |

Answer

To start with: you are using an old join syntax (of the 1980s actually). This is how we would write the query today:

Select
   w.vintage, w.wine_no, w.wname, w.pctalc, w.grade, w.price, w.vid, v.vname, w.cid, c.cname
from wine w
join vineyard v on v.vid = w.vid
join class c on c.cid = w.cid
where w.cid = 'SHIRAZ' 
and w.grade = 'A';

Here a mere glimpse at the WHERE clause suffices to see that you are looking for wines matching a class and grade. So, have an index on the two columns. Order may matter, so provide two indexes. Extend this with the vineyard ID, so as to get quickly to the vineyard table.

As to class and vineyard, you should already have indexes on their IDs. You may want to add the one column for each table you are selecting, so the DBMS can get the values directly from the index.

create index idx01 on wine ( cid, grade, vid );
create index idx02 on wine ( grade, cid, vid );
create index idx03 on class ( cid, cname );
create index idx04 on vineyard ( vid, vname );

Use the execution plan to detect indexes that are not used (the query will only use either idx01 or idx02 or even none of these, not both) and drop them.