Lets say I have a immutable table(USERS) with data up to 30 billion like, table USERS with 26 columns say A, B, C, D ,,, X, Y, Z. Out of which A, B, C are my primary keys and I added the LOCAL index on P and Q column. CREATE LOCAL INDEX USERS_INDEX_P ON USERS (P); CREATE LOCAL INDEX USERS_INDEX_Q ON USERS (Q); lets say I have following few records in table like, A B C P Q Z a1 b1 c1 p1 q1 z1 a2 b2 c2 p2 q2 z2 a3 b3 c3 p3 q3 z3 Now following query shows expected results in millisecond due to index Query-1 ==> select * from USERS where P = 'p1'; // 1 result found Query-2 ==> select * from USERS where Q = 'q1'; // 1 result found But if I add condition on both the columns(local indexed) then it shows 0 results. select * from USERS where P = 'p1' and Q = 'q1'; // 0 result found even though there is record available in DB for this combination. Please let me know how can I cope up with this situation or any alternative available ? If I only index column P and then add second condition on column Q then it takes more time(10+ seconds).
... View more