-2

I want to retrieve data from two tables.
The two tables are related with a foreign key reference. I've the following two ways to do this. But don't know which one is the better way.

  1. A stored function call from the query
  2. Using a join clause

Which will be the better way? I'm using the technologies: Oracle12c, Java, IBatis. So what is the best way to achieve this?

ARK
  • 35
  • 8
  • 2
    What would the function do? Make the same join behind the scenes? In general you should avoid embedding functions into queries unless there's a specific reason to do so, as they can quickly kill your performance depending on where you place them and how many rows of data are involved. That said, there are also specific use cases where they make sense. It's hard to know in your case without more information on what you're planning to do. – pmdba Apr 30 '20 at 15:31

1 Answers1

2

As join keeps everything at SQL level, it should be a better choice.

Using a function - which is PL/SQL - causes context switching (from SQL to PL/SQL and back to SQL to PL/SQL ...) which will take resources.

You won't notice anything if there's a small data set, but - as number of rows involved gets higher, execution time will probably suffer in the second option.

If you'll be testing it, run every option several times because of possible caching.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57