Oracle Selecting Index Optimisation

Question

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 1

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.

Answer 2

Indexing is a frequently effective measure to optimize queries, however, you need to do further steps. Text-based searches tend to be slower in general, so it's highly advisable to modify your class table, so it will have a numeric primary key and to avoid storing texts such as SHIRAZ in your wine table, but rather a numeric foreign key to the class table and store the text SHIRAZ exactly once, for its class record, which would be referenced from the wine table via a numeric value. Also, you should do similarly for grade. If you do not have a grade table yet, create one, with numeric primary key and a field to store values, like A.

Finally, your query is calculating a Descartes multiplication, which, as we know from set theory, does a match for each coordinates in the problem topology. Also, as we know from relational algebra, your where clause will run for all points in your three dimensional (vineyard, class, wine) problem-space. As far as I know, if you would refactor your query to use joins, it should become much quicker, because there are some optimizations for joins to avoid computing all points in your topology.

Let's refactor your current query:

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

Let's refactor this query to be compatible with your schema after the structural changes I have suggested:

Select vintage, wine_no, wname, pctalc, grade, price, wine.vid, vname, wine.cid, cname
from wine
join class
on wine.class_id = class.id and class.cid = 'SHIRAZ'
join grade
on wine.grade_id = grade.id and grade.value = 'A'
join vineyard
on wine.vid = vineyard.vid;

Also, since it's safe to assume that there are only a few grades and vineyards, we can change the order of introducing the tables into the query:

Select vintage, wine_no, wname, pctalc, grade, price, wine.vid, vname, wine.cid, cname
from grade
join wine
on wine.grade_id = grade.id and grade.value = 'A'
join class
on wine.class_id = class.id and class.cid = 'SHIRAZ'
join vineyard
on wine.vid = vineyard.vid;

There are further steps to do if this is still not enough, let me know if further steps are needed.