0

GBQ lets perform the the following statement which is very good at improving my query efficiency:

DECLARE practices ARRAY<INT64>;

SET practices = (SELECT ARRAY(select distinct id from business));

select *
from business 
where business.id in UNNEST(practices)

How can I accomplish this same type of prefetch against an Oracle DB w/ Oracle SQL? I'd like to use one set of similar SQL statements to refactor existing Oracle SQL queries instead of inefficient CTE's.

P.S. I've looked at other solutions like How to declare variable and use it in the same Oracle SQL script? however, those solutions don't appear to be working in Oracle SQL for me and/or they are listed on separate execution lines against PL/SQL.

I.e. does not work:

DECLARE practices = 'My Practice Name';

select *
from business 
where business.name = '&practices'
MT0
  • 143,790
  • 11
  • 59
  • 117
Mark McGown
  • 975
  • 1
  • 10
  • 26
  • This feels like an XY problem. What is the actual problem you are trying to solve and how are you talking to the Oracle database. A solution for a series of query that you manually run in SQL*Plus is going to be very different to a solution that you run with a programming language (Java, PHP, C#, python). – MT0 Apr 09 '23 at 20:16
  • 1
    Also, doesn't your GBQ query set `practices` to be an array of all `id`s in the table `business` and then your second query selects from exactly the same table any rows that have an `id` in the array of all `id`s. Would it not be simpler to use `SELECT * FROM business` (or `SELECT * FROM business WHERE id IS NOT NULL`)? – MT0 Apr 09 '23 at 20:19
  • Sure, in one query statement I send to the Oracle DB, I want to prefetch an array I can use to filter a subsequent main query. – Mark McGown Apr 09 '23 at 20:36
  • I don't understand the problem(s) either. Are you merely looking for this syntax: `select * from business where id in (select id from business)`? As to variables: Oracle uses bind variables: `select * from business where business.name = :practices`. – Thorsten Kettner Apr 09 '23 at 21:04
  • Re: Nesting. The goal is to determine how to accomplish the prefetch as a minimal example - not trying to optimize this specific query. I just want to know how to do the same GBQ prefetch against an Oracle DB. I want know how yo make Oracle first grab a prefetch that I then issue a main query using. – Mark McGown Apr 09 '23 at 21:04

1 Answers1

2

For your simple example, don't fetch twice; just do it all in one query:

SELECT *
FROM   business 
WHERE  id IN (SELECT id FROM business)
  • Note 1: you do not need to use DISTINCT.
  • Note 2: you can simplify the query to SELECT * FROM business WHERE id IS NOT NULL

If you do want to put the values into an array (most of the time you probably don't want to do this and you are probably trying to prematurely optimise) then you can use a parameterised PL/SQL anonymous block and collect the values into a collection for use in a subsequent statement:

DECLARE
  v_ids SYS.ODCINUMBERLIST;
BEGIN
  SELECT DISTINCT id
  BULK COLLECT INTO v_ids
  FROM   business;

  OPEN :your_out_bind_variable FOR
    SELECT *
    FROM   business
    WHERE  id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));
END;
/

And use a bind variable to bind a cursor to the out parameter, which you can access from whatever language (PHP, Java, C#, etc.) you are using to access the database.

Mostly though, you would use a sub-query or a sub-query factoring clause and do it all in a single query (then you do not need to context-switch between SQL and PL/SQL or, worse, execute multiple statements from the client application and incur the overhead of network requests for each statement).

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks for this and the fiddle, very much appreciated on specifically the latter half of how a DECLARE statement should work. Right now, my specific Oracle DB does not recognize SYS.ODCINUMBERLIST; so I need to spend time figuring out how to declare this array type before I can test. – Mark McGown Apr 09 '23 at 21:30
  • @MarkMcGown You can create a type using `CREATE TYPE number_table IS TABLE OF NUMBER;` [fiddle](https://dbfiddle.uk/c3yeYLoC) – MT0 Apr 09 '23 at 21:33