0

I need help to make a query to be optimized. And suggest if there is any other way to accomplish the same by using any other query.

WITH 
q1 as 
(select eid, data_used||'-'||status as value1 
 from T1 inner join T2 ON (t1.eid = t2.eid) 
 where <conditions>),
q2 as
(select eid, data_used||'-'||status as value2 
 from T1 inner join T2 ON (t1.eid = t2.eid) 
 where <conditions different from q1 >),
q3 as
(select eid, data_used||'-'||status as value3  
 from T1 inner join T2 ON (t1.eid = t2.eid) 
 where <conditions different from q2>)
select q1.eid, q1.value1, q2.value2, q3.value3 
from q1, q2, q3 
where q1.eid = q2.eid 
and q2.eid=q3.eid;

The query will give output as follows:

enter image description here

I meant to say value1, value 2, value 3 can be different for a same eid according to the conditions in q1, q2 and q3.

Both tables T1 and T2 are audit tables hence eid repeats multiple times in both.

I'm really tired of optimizing and making this query shorter. I'm new to this. Hence I would like to know if there is any other way to get it done. I have created indexes on these tables and yet it is returning the data in 77 seconds. I need to reduce the time at least by 10 - 15 seconds. Current data volume is 100k records and it is going to grow as billions.

James Z
  • 12,209
  • 10
  • 24
  • 44
Ragnarok
  • 13
  • 3
  • 2
    There isn't enough information here for anybody to offer an informed answer. (Obviously people are free to guess but that's likely to be a waste of everybody's time.) Please read [this post on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325). Then **edit your question** to include details such as data volumes and skew, indexes, explain plans and so on. – APC Jan 14 '20 at 12:23
  • If you are still looking for a solution and can furnish the sample original data, I think there is a good chance a solution is out there – Hambone Jan 15 '20 at 15:16

1 Answers1

1

If I understand your query correctly, you have simply over-complicated an "and" query. So your query could be distilled to simply this:

select
  eid || status as value1,
  eid || status as value2,
  eid || status as value3
from
  T1
  join T2 on t1.eid = td.eid
where
  <Q1 conditions> and
  <Q2 conditions> and
  <Q3 conditions>

I might add "EID" is missing from your subqueries. I don't think your example will compile as-is.

And I realize that makes little sense -- to list the same values in three columns, but I believe that's what your query is doing because of the inner joins following all of the CTEs (assuming you add EID to the output).

I am completely speculating, but I think that might actually be what you were trying to do:

select
  case when <Q1 conditions> then eid || status end as value1,
  case when <Q2 conditions> then eid || status end as value2,
  case when <Q3 conditions> then eid || status end as value3
from
  T1
  join T2 on t1.eid = td.eid
where
  <Q1 conditions> or
  <Q2 conditions> or
  <Q3 conditions>

Which should run faster because it will do at most one full scan.

Again, I'm speculating, but if you can add some sample data and what you want your query output to be, that would help clarify.

Hambone
  • 15,600
  • 8
  • 46
  • 69
  • No, I don't imagine the first query will give you what you need at all. But from my best guess, that is what the query you listed is actually doing because of the inner joins. – Hambone Jan 14 '20 at 12:55
  • I have added sample output as well as I made some change in the query also. Can you please have a look. – Ragnarok Jan 14 '20 at 13:20
  • That helps... and it clarifies. I guess I thought EID was a PK, but that was a bad assumption. One more thing that would help a lot, can you show what the original data looks like in T1 and T2 that would yield this result? – Hambone Jan 14 '20 at 13:25
  • Both tables T1 and T2 are audit tables hence eid repeats in both. Multiple rows with same eid would be present. – Ragnarok Jan 14 '20 at 13:46
  • I'm at a loss because I don't know what the original input is that would yield that output... can you furnish that as well? If we know the input, logic and expected output, that will go a long way toward getting the answer. You question still only lists the desired output. – Hambone Jan 14 '20 at 22:30