0

I have the SQL query shown here, indexes are present on the join columns. If I removed those table from my SQL query so it turns fast:

select * 
from student st, studentheader sh, studentversion sv
where st.rollnumber = sh.rollnumber
  and sh.rollnumber = sv.rollnumber
  and sh.latestversionid = sv.versionid;

Below are the indexes created on my tables.

  • unique index on studentheader (rollnumber, latestversionid)
  • non-unique index on studentheader (sh.latestversionid)
  • unique index on studentversion (sv.rollnumber,sv.versionid)

Studentversion.version column has many versions for a roll number.

APC
  • 144,005
  • 19
  • 170
  • 281
supriya sethiya
  • 19
  • 1
  • 3
  • 4
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**almost 30 years** ago) and its use is discouraged – marc_s May 24 '21 at 05:34
  • I have many other tables in my query, so i can't use new joins, aprat from this could you please tell me what can be done to increase the performance. as it is taking some 3 minutes to run. – supriya sethiya May 24 '21 at 05:45
  • 1
    The **proper ANSI JOIN** syntax can handle **any number** of tables...... that's a lame "excuse" ... – marc_s May 24 '21 at 06:42
  • 1
    Totally agree with marc_s comments... given that, please add to your question the output from running explain plan for your query – NickW May 24 '21 at 07:25
  • ok, i have modified my query to use joins, but that is not resolving the performance of query. – supriya sethiya May 24 '21 at 07:25
  • Did you analyze the query plan and what exactly is taking much time? – astentx May 24 '21 at 07:58
  • 1
    Some people are unreasonably prescriptive about join syntax. It doesn't affect performance at all. However, most SQL is written with the explicit INNER JOIN notation and that makes it easier for other people to read, which matters on public sites like this. – APC May 24 '21 at 08:51
  • The **simplest** way to increase your performance is to **(1)** ditch the ` SELECT *` and select only those columns you **really need**, and **(2)** do the same with rows - don't fetch **ALL** rows, but instead add a reasonable `WHERE` condition to limit the number of rows to those that you **really, really need** .... – marc_s May 24 '21 at 10:03
  • @astentx actually sv.versionid column has many versions saved against rollnumber. like from 1to 7, and index is created on column sv.versionid and sv.rollnumber. which might be taking time. – supriya sethiya May 24 '21 at 10:10
  • @marc_s your solution is ok, but my problem is not with * sign, even if i give one column name so it is not resolving my issue. – supriya sethiya May 24 '21 at 10:18
  • There are a lot of things which affect the performance of a query. So it is impossible to answer your question just be looking at the SELECT statement and saying, *"do this"*. Please read [this post](https://stackoverflow.com/a/34975420/146325) about asking Oracle tuning questions: it will help you understand the information you need to provide before we can answer your question. – APC May 24 '21 at 10:32

0 Answers0