0

I have a generic query builder that adds an arbitrary number of filters. I am getting poor performance on one of those filters (filter b) that requires going through two tables.

SELECT * 
FROM   (SELECT "TABLE_1".* 
        FROM   "TABLE_1" 
               -- filter a: 1 table deep (fast)
               inner join (SELECT "SHARED_ID"
                           FROM   "TABLE_4"
                           WHERE "TABLE_4"."COLUMN_A" LIKE '%123%'
                          ) "TABLE_4"
                      ON "TABLE_1"."SHARED_ID" = "TABLE_4"."SHARED_ID"
               -- filter b: 2 tables deep (slow)
               inner join (SELECT "SHARED_ID" 
                           FROM   "TABLE_2" 
                                  inner join (SELECT "ID" 
                                              FROM   "TABLE_3" 
                                              WHERE  NAME LIKE '%Abc%') 
                                             "TABLE_3" 
                                          ON "TABLE_2"."TABLE_3_ID" = 
                          "TABLE_3"."ID") "TABLE_2" 
                       ON "TABLE_1"."SHARED_ID" = "TABLE_2"."SHARED_ID")
WHERE  ROWNUM <= 20
TheAschr
  • 899
  • 2
  • 6
  • 19
  • 3
    What's the query plan say? If there are a lot of records in `TABLE_3` and no indexes then you'll get poor performance. – Nick.Mc Jun 11 '19 at 23:04
  • Can you add fields to these tables? If so you could populate a new nameExists column via trigger that populates a 1 or 0 (Yes or No) value by checking for the %Abc% condition,as records are added to the table (with a 1 time load for existing records). Then in filter b you can get rid of fuzzy search and go with ...WHERE nameExists = 1. – alexherm Jun 11 '19 at 23:05
  • @Nick.McDermaid There are ~65500 rows and no indexing. – TheAschr Jun 11 '19 at 23:19
  • @alexherm unfortunately any model changes will take a year+ to be approved. – TheAschr Jun 11 '19 at 23:19
  • What do you mean by fast and slow? What timings do get and do you want? A table with 65k rows and no indexing should still be fast for batch processing. Do you need this query for an online query, batch, OLTP, or OLAP? Please post the execution plan. – The Impaler Jun 11 '19 at 23:40
  • Although I think Oracle's optimizer should be able to handle this, but what happens if you get rid of all those unnecessary derived tables and join to the tables directly? something like: http://dpaste.com/1709AXR –  Jun 12 '19 at 02:06
  • 1
    If there is "no indexing", then you should at least have indexes on the join columns. But without the execution plan this is really impossible to answer (and obfuscated table names with useless double quotes don't make this easier to read) –  Jun 12 '19 at 02:07
  • You cannot optimize a query. You can only optimize a plan. To actually be able to optimize this, you'll have to show the execution plan. Also showing the tables in question might help. – eaolson Jun 12 '19 at 03:31
  • See [here](https://stackoverflow.com/a/34975420/4808122) how to post the execution plan of the query. Check if your table statistics are up to date... – Marmite Bomber Jun 12 '19 at 05:03
  • It's difficult to say without explaim plan. "WHERE NAME LIKE '%Abc%'" doesn't allow you to use index probably (if it index exists) – Tomasz Jun 12 '19 at 07:12

0 Answers0