Vfrdtyky

Sorry, my description sucks, but the problem is easy to explain. I have a massive table (over 2bn rows) called BigTable which is partitioned by date that looks like this:

ID    Object   Date

---   -------  ----------

1     A         2018-10-01

1     B         2018-10-01 

1     C         2018-10-01

1     D         2018-10-01

2     M         2018-10-01

2     N         2018-10-01

2     O         2018-10-01 

3     X         2018-10-01

3     B         2018-10-01

I need a SQL that returns ALL rows for the ID where an Object = "B" and the date = '2018-10-01'
Therefore the result would be:

ID    Object   Date

---   -------  ----------

1     A         2018-10-01

1     B         2018-10-01 

1     C         2018-10-01

1     D         2018-10-01

3     X         2018-10-01

3     B         2018-10-01

The SQL is easy enough, just join the table to itself:

SELECT t2.id,t2.object, t2.date

FROM BigTable T1

join BigTable T2 on t2.id = t1.id

   AND t2.transaction_date = '01-OCT-2018'

   AND t2.object = 'B'

where t1.date = '01-OCT-2018'

This however runs for HOURS. Subquery does not make it any better.
What is the most efficient way to return all rows for an ID that contains an object = 'B'?

I assume you have good indexing in place... No guarantees this will help, but have you tried a semi-join:

select

  t1.*

from BigTable T1

where

  t1.transaction_date = '01-OCT-2018' and

  exists (

    select null

    from BigTable T2

    where

      t2.transaction_date = t1.transaction_date and

      t2.transaction_date = '01-OCT-2018'

      t2.object = 'B'

)

The advantage of the semi-join is that once it finds a match it should "stop looking."

The join on transaction date may seem superfluous, but try it both ways.

You can try window functions:

SELECT t.id, t.object, t.date

FROM (SELECT t.*,

             SUM(CASE WHEN t.object = 'B' THEN 1 ELSE 0 END) OVER (PARTITION BY t.id, t.transaction_date) as cnt

      FROM BigTable t

      WHERE t.transaction_date = DATE '2018-10-01'

     ) t

WHERE cnt > 0;

One possibility is that you misunderstand the partitioning, and the entire contents of the table are being read in your query.

You should avoid using date strings directly while comparing it with dates. The reason being implicit conversion may not always use the available partitions on date. The documentation for Data type conversion states that

Algorithms for implicit conversion are subject to change across
software releases and among Oracle products. Behavior of explicit
conversions is more predictable.

If implicit data type conversion occurs in an index expression, then
Oracle Database might not use the index because it is defined for the
pre-conversion data type. This can have a negative impact on
performance.

Thus, it is preferable to use the ANSI standard date literals

SELECT t2.id,t2.object, t2.date

FROM BigTable T1

join BigTable T2 on t2.id = t1.id

   AND t2.transaction_date = DATE '2018-10-01'

   AND t2.object = 'B'

where t1.date = DATE '2018-10-01'

Also, as already suggested in comments, create a LOCAL INDEX on (date, id, transaction_date, object) to further improve the speed of the scan using the index along with partition pruning.