henrywho

you are wrong, the performance is significantly improved once the index properly applied
xeon0541 發表於 2015-10-22 23:09

Don't be afraid of full table scan.  Look at and look into the actual SQL execution plans.  You will find that there are cases that you refer the database doing full table scans instead of repeated index scans.

xeon0541

select max(patient_id) from patient_history
                                 where record_date <= to_date(:p_cur_date, 'ddmmyyyy')
                                 and patient_id = :pid);

the execution plan for above  sql
Sort(AGGREGATE)
   FIRST ROW
      INDEX (RANGE SCAN(MIN/MAX)
               access  predicates
                        AND
                              PATEINT_ID=TO_NUMBER(:PID)
                               PRECORD_DATE<=TO_DATE(:P_CUR_DATE,'DDMMYYYY')                           

The index of PRECORD_DATE column is used.
Because the optimizer of database don't know your business logic, it always used  wrong index or performing full scan table. That's why SQL performance tuning is needed.

henrywho

select max(patient_id) from patient_history
                                 where record_date
xeon0541 發表於 2015-10-23 10:49

Try not to focus on the sub-query only .....

snoopy11hk

Don't be afraid of full table scan.  Look at and look into the actual SQL execution plans.  You wi ...
henrywho 發表於 2015-10-23 08:12

see if u need to scan through small portion or large portion of data (0.1% or 20%+)
in this case should be full table scan faster than nested loop

and

analytics will be faster (1 full table scan)
vs
1 full table scan + (1+) nested loop join

xeon0541

Try not to focus on the sub-query only .....
henrywho 發表於 2015-10-23 19:15

select max(patient_id) from patient_history
                                 where record_date <= to_date(:p_cur_date, 'ddmmyyyy')
                                 and patient_id = :pid);

    this is not sub query ,just simple sql only.

henrywho

this is not sub query ,just simple sql only.
xeon0541 發表於 2015-10-23 20:51

We are talking about queries like:

select a.* from table a, (select codeid, MAX(date) as date from table group by codeid) b where a.codeid = b.codeid and a.date = b.date

xeon0541

you mentioned "MySQL 同 Oracle 可以寫 analytic function, 只需 full-table scan 一次"  I just follow the reply. moreover your sql don't need perform full table scan.

henrywho

moreover your sql don't need perform full table scan
xeon0541 發表於 2015-10-23 21:30

what is the execution plan?  

snoopy11hk

you mentioned "MySQL 同 Oracle 可以寫 analytic function, 只需 full-table scan 一次"  I just follow t ...
xeon0541 發表於 2015-10-23 21:30

    ur index seems not good enough
should using id instead if it is id selection