Pfthb

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.