title: How to Less cardinality and cost of my sql
body: <p><a href="https://i.stack.imgur.com/VxT3B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VxT3B.png" alt="enter image description here" /></a>
  Above is result i just want to low cardinality cost just.<br />
  I have <strong>Multiple subquery</strong> i want to less <strong>cardinality cost</strong> what should i do. i have tried to do it with with clause but it will not set according with my requirements query execute fine but cardinality and cost is much higher that's why i want to split cardinality</p>
  <pre><code>select distinct
  C.CLASS_ID,
  C.TEACHER_ID,
  
  C.SHIFT_ID,
  C.BRANCH_ID,
  T.TEACHER_NAME,
  ns.nazim_id,
  c.class_uid,
  (Select count(*) AS total_class from student where upper(class_id)=upper(C.CLASS_ID) and is_active_flg='Y'
  and status='STUDYING'
  and upper(class_days) like '%'||TO_CHAR(to_date(:P28_DATE),'DY')||'%') as Total_Students,
  
  (select count(*) from student
  where class_id=C.CLASS_ID and is_active_flg='Y') as Active_Students,
  
  
  (select count(*) from student
  where class_id=C.CLASS_ID and is_active_flg='N') as InActive_Students,
  
  (Select count(*) as Marked from student_class_attend where upper(class_id)=C.CLASS_ID
  and attend_date =:P28_DATE) as Attend_Marked,
  
  (select count(*)
  from student
  where class_id=C.CLASS_ID and is_active_flg='Y' and gr_number not in (select gr_number from student_class_attend where attend_date=:P28_DATE)
  ) as Attend_UnMarked,
  
  (select count(*) from student_class_attend
  where class_id=C.CLASS_ID and attend_date=:P28_DATE and submit_flg='Y') as attend_submitted,
  
  (select count(*) from student_class_attend
  where class_id=C.CLASS_ID and attend_date=:P28_DATE and submit_flg='N') as attend_pending
  
  
  --qc.unconfirmed
  from CLASS C
  --join class_time ct on c.class_uid = ct.class_uid
  LEFT JOIN student_class_attend SC ON sc.class_id=c.class_id
  join nazim_shift ns on decode(:V_GENDER,'BOTH',c.gender,ns.gender) = c.gender
  and instr(ns.department_id||':',c.department_id||':')&gt;0
  and instr(ns.branch_id||':',c.branch_id||':')&gt;0
  and instr(ns.shift_id||':',c.shift_id||':')&gt;0
  and ns.nazim_login_id = lower(trim(:APP_USER))
  --join nazim_shift_time nst on ns.nazim_shift_uid = nst.nazim_shift_uid
  --and nst.class_time=ct.class_time
  left join TEACHER T ON C.TEACHER_ID = T.TEACHER_ID and t.active_flg = 'Y'
  where c.active_flg = 'Y'
  order by c.class_id
  ;
  </code></pre>