0

This is a non-urgent question about Oracle (~11g) query optimisation. It is not urgent because the query is currently performing well within requirements, but I should like to know if it is advisable to optimise my query anyway.

The query joins a table of at most a few thousand records via foreign keys to 4 others, so there is just one matching row in the other tables, which have about 30 to 300 records. It selects almost all (c. 40) columns of the main table and 3 columns from the other tables. The DB is accessed using embedded SQL in a source file pre-processed with sed to adapt it to one of two platforms: (normal) Oracle on AIX or Oracle Rdb on VMS. In both cases the query runs on the same machine as the DB, Rdb tends to perfom slightly better.

The point about which I have doubts, is that in order to implement optional selection criteria I have a condition :pid = 0 OR t.pid = :pid and two like t.name like :name, where :pid and :name are host variables and :name is set to "%" when this criterion is not needed and otherwise the actual name.

I have written it like this to keep the code simple, namely to avoid the complications of dynamic SQL and the redundancies of writing several variants of what is (textually) quite a large query – either of those options would probably increase maintenance costs. Since the DB is not large (and is expected to stay much the same size) and performance is well within bounds, I currently feel justified.

Is this approach reckless, sensibly pragmatic or somewhere in between?

PJTraill
  • 1,353
  • 12
  • 30
  • 1
    As SO is about programming, you may find more support for this over at [dba.se] – crthompson May 06 '15 at 19:10
  • 2
    @paqogomez isn't sql about programming? – Kirill Leontev May 06 '15 at 19:41
  • Are you concerned about scalability? Or is the current data representative of the actual application? – Gordon Linoff May 06 '15 at 19:42
  • @beherenow I wasnt trying to suggest that OP *wont* get help here, just that they may get more help at dba. As for sql being about programming.. yes.. but I dont see any sql in that post. Thus, it seems to be all speculation.. as the answers are indicating. – crthompson May 06 '15 at 19:50
  • @Gordon: The data are definitely representative. Even doubling would be unexpected. – PJTraill May 06 '15 at 20:29
  • @pagogomez: Admittedly there only scraps of (embedded) SQL, but I’m definitely coming at it from the angle of what SQL to write rather than how to manage the physical DB. – PJTraill May 06 '15 at 20:32

1 Answers1

1

I think it's difficult to answer without knowing what the query optimiser is doing under the circumstances that might be cause for concern.

You might like to see how ":pid = 0 OR t.pid = :pid" is optimised when :pid equals 0 and when it is not, and the same for "t.name like '%'".

If :pid truly is a bind variable then it may not matter, but what I'd be concerned with is a situation where the execution plan is defined 99% of the time by the query being optimised with :pid having a value different to "0", and 1% of the time with :pid having a value of something else. It's by no means unheard of for one of the two to perform well, and the other to perform catastrophically badly.

I would definitely be concerned enough to check on what the optimiser does when you execute "t.name like 'abc'", as LIKE tends to not use indexes. Possibly the query optimiser transforms it to "t.name = 'abc'", possibly not.

Sounds like a decent bit of low-priority investigation, if you have time for it.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • `:pid` and `:name` are indeed variables passed from the (C) programme into the embedded SQL query. I’ll keep your comments in mind for when I have time or a problem, but I’ll need to learn more about how to check Oracle query strategies first. – PJTraill May 06 '15 at 20:37
  • Why does it matter if `:pid` is a bind variable? In practice it is more like zero 90% of the time than vice versa. – PJTraill May 06 '15 at 20:47
  • 1
    Have a look here at section 11.1.3 bind variable peeking: http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF94588. You have an extreme case of particular values implying 100% row selectivity. – David Aldridge May 06 '15 at 21:02
  • P.S. While your answer is definitely useful, I am afraid I have not accepted it as a complete answer, which I think would state the conditions under which problems would arise. If I ever get round to this, I might be able to do that myself, at least partly, though maybe the workings of the QO are too opaque to ever give a definitive answer. – PJTraill May 15 '15 at 11:27