title: Selecting Where ID Must Be In List
body: <p>I have a list of ids that I'd like to grab off a table. I also have a defined type which looks like the following</p>
  
  <pre><code>CREATE OR REPLACE TYPE "NUMBER_TABLE" as table of number;
  </code></pre>
  
  <p>So basically just an array of number.</p>
  
  <p>What I'd like to do is use that number_table to grab all the rows with a matching primary key off a table. In code, something like</p>
  
  <pre><code>select tbl.*
  from tbl_myTable tbl
  where tbl.id in (select * from table(number_table(12345)))
  </code></pre>
  
  <p>This works but it runs much slower than</p>
  
  <pre><code>select tbl.*
  from tbl_myTable tbl
  where tbl.id in (12345)
  </code></pre>
  
  <p>Looking at the explain plan reports, here's the explain plan for the "tbl.id in (12345)"</p>
  
  <p><a href="https://i.stack.imgur.com/mh2hl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mh2hl.png" alt="enter image description here"></a></p>
  
  <p>And here's the explan plan for the "tbl.id in (select * from table(number_table(12345)))"
  <a href="https://i.stack.imgur.com/LKMJw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LKMJw.png" alt="enter image description here"></a></p>
  
  <p>It seems to be causing gratuitous slow-down in some important areas. Is there anyway to use a list of ids for an index scan like this?</p>
  
  <p>Thanks.</p>