Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query not using index

Laurent SchneiderSep 19 2008 — edited Sep 22 2008
Hi,
I am wondering if there is a way to make my query use an index without specifying the INDEX hint.

Thanks
Laurent

PS: I am using 9.2.0.8
SQL> create table lsc_t1(a number primary key, b number);

Table created.

SQL> create table lsc_t2(a number primary key, b number);

Table created.

SQL> create table lsc_t3(a number primary key, b number);

Table created.

SQL> insert into lsc_t1(a,b) select rownum, dbms_random.value from dual connect by level<10000;

9999 rows created.

SQL> insert into lsc_t2(a,b) select rownum, dbms_random.value from dual connect by level<1000;

999 rows created.

SQL> insert into lsc_t3(a,b) select rownum, dbms_random.value from dual connect by level<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> begin
  2    dbms_stats.gather_table_stats(user,'LSC_T1');
  3    dbms_stats.gather_table_stats(user,'LSC_T2');
  4    dbms_stats.gather_table_stats(user,'LSC_T3');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select --+ INDEX(lsc_t1)
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

         A          B
---------- ----------
       140 .138460932
       161 .928661302
       281 .830010122
       440 .071851157
       443 .355640404
       516 .791276412
       598 .508219846
       600 .158621147
       643 .748363911
       747 .777346635
       836 .390349029
       869 .292453677
       874 .914353702
       881 .930301092

14 rows selected.

Elapsed: 00:00:00.03
SQL> select
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

         A          B
---------- ----------
       140 .138460932
       161 .928661302
       281 .830010122
       440 .071851157
       443 .355640404
       516 .791276412
       598 .508219846
       600 .158621147
       643 .748363911
       747 .777346635
       836 .390349029
       869 .292453677
       874 .914353702
       881 .930301092

14 rows selected.

Elapsed: 00:00:00.26
SQL> set timi off autot trace exp
SQL> select --+ INDEX(lsc_t1)
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=17 Bytes=476)
   1    0   NESTED LOOPS (Cost=19 Card=17 Bytes=476)
   2    1     VIEW OF 'VW_NSO_1' (Cost=2 Card=17 Bytes=68)
   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=8 Bytes=192)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24)
   8    7       INDEX (UNIQUE SCAN) OF 'SYS_C001186489' (UNIQUE)



SQL> select
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=17 Bytes=476)
   1    0   NESTED LOOPS (Cost=6 Card=17 Bytes=476)
   2    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=6 Card=9999 Bytes=239976)
   3    1     VIEW OF 'VW_NSO_1'
   4    3       SORT (UNIQUE)
   5    4         UNION-ALL (PARTITION)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24)
   7    6             INDEX (UNIQUE SCAN) OF 'SYS_C001186490' (UNIQUE) (Cost=1 Card=1)
   8    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24)
   9    8             INDEX (UNIQUE SCAN) OF 'SYS_C001186491' (UNIQUE) (Cost=1 Card=1)
This post has been answered by Randolf Geist on Sep 22 2008
Jump to Answer

Comments

odie_63

I have to display output which is grouped based on month and its corresponding array values as json_ojbect.
Then you have to actually aggregate JSON objects per month in a JSON array : use aggregate function JSON_ARRAYAGG.
Final step is to consolidate all months in a single object, that's an aggregation too : use JSON_OBJECTAGG.
For example (just showing the last query step) :

SELECT json_objectagg(
     cal_mon
     value json_arrayagg(
         json_object(
          'date' value to_char(cal_dates,'DD-MON-YYYY')
         , 'val' value nvl(appointment_id,0)
         )
         order by cal_dates -- if necessary
        ) 
    ) Final_output
FROM get_json
GROUP BY cal_mon

You may also need to add some RETURNING CLOB clause here and there if the output is too large.

Paulzip

Maybe something like this (might need tweaking).


with
  cal_data as (
     select     first_date + level - 1   as cal_dates
     from       (select next_day(trunc(sysdate, 'MONTH') - 15, 'SUNDAY')  as first_date
                      , next_day(last_day(sysdate) - 1, 'SATURDAY')       as last_date
                 from   dual)
     connect by level <= last_date + 1 - first_date
  )
, data as (
    select c.*, a.*, to_number(to_char(cal_dates, 'dd')) day_of_week, to_char(cal_dates, 'fmMonth') month_name, to_number(to_char(cal_dates, 'MM')) month_num
    from   cal_data  c
    left join patient_appointments a on a.appointment_date >= c.cal_dates and a.appointment_date < c.cal_dates + 1
  ) 
select json_objectagg(
         month_name
       , json_arrayagg(
           json_object (
             'date' value day_of_week
           , 'val'  value nvl(appointment_id, 0)
           )
           order by day_of_week 
         )
       ) 
from data
group by month_num, month_name 
order by month_num
;

{
  "May" : [
    {
      "date" : 22,
      "val" : 0
    },
    {
      "date" : 23,
      "val" : 0
    },
    {
      "date" : 24,
      "val" : 0
    },
    {
      "date" : 25,
      "val" : 0
    },
    {
      "date" : 26,
      "val" : 0
    },
    {
      "date" : 27,
      "val" : 0
    },
    {
      "date" : 28,
      "val" : 0
    },
    {
      "date" : 29,
      "val" : 0
    },
    {
      "date" : 30,
      "val" : 0
    },
    {
      "date" : 31,
      "val" : 0
    }
  ],
  "June" : [
    {
      "date" : 1,
      "val" : 0
    },
    {
      "date" : 2,
      "val" : 0
    },
    {
      "date" : 3,
      "val" : 0
    },
    {
      "date" : 4,
      "val" : 0
    },
    {
      "date" : 5,
      "val" : 0
    },
    {
      "date" : 6,
      "val" : 0
    },
    {
      "date" : 7,
      "val" : 1
    },
    {
      "date" : 8,
      "val" : 0
    },
    {
      "date" : 9,
      "val" : 2
    },
    {
      "date" : 10,
      "val" : 3
    },
    {
      "date" : 11,
      "val" : 0
    },
    {
      "date" : 12,
      "val" : 0
    },
    {
      "date" : 13,
      "val" : 0
    },
    {
      "date" : 14,
      "val" : 0
    },
    {
      "date" : 15,
      "val" : 0
    },
    {
      "date" : 16,
      "val" : 0
    },
    {
      "date" : 17,
      "val" : 0
    },
    {
      "date" : 18,
      "val" : 0
    },
    {
      "date" : 19,
      "val" : 0
    },
    {
      "date" : 20,
      "val" : 0
    },
    {
      "date" : 21,
      "val" : 0
    },
    {
      "date" : 22,
      "val" : 0
    },
    {
      "date" : 23,
      "val" : 0
    },
    {
      "date" : 24,
      "val" : 0
    },
    {
      "date" : 25,
      "val" : 0
    },
    {
      "date" : 26,
      "val" : 0
    },
    {
      "date" : 27,
      "val" : 0
    },
    {
      "date" : 28,
      "val" : 0
    },
    {
      "date" : 29,
      "val" : 0
    },
    {
      "date" : 30,
      "val" : 0
    }
  ],
  "July" : [
    {
      "date" : 1,
      "val" : 0
    },
    {
      "date" : 2,
      "val" : 0
    }
  ]
}

 
Solomon Yakobson
Answer
WITH CAL_DATA AS (
                  SELECT  FIRST_DATE + LEVEL -1 AS CAL_DATES
                    FROM  (
                           SELECT  NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY') AS FIRST_DATE,
                                   NEXT_DAY(LAST_DAY(SYSDATE) - 1,'SATURDAY') AS LAST_DATE
                              FROM DUAL
                          )
                    CONNECT BY LEVEL <= LAST_DATE + 1 - FIRST_DATE
                 ),
     GET_JSON AS (
                  SELECT  TO_CHAR(C.CAL_DATES,'MON') CAL_MON,
                          C.CAL_DATES,
                          A.*
                    FROM      CAL_DATA C
                          LEFT JOIN
                              PATIENT_APPOINTMENTS A
                            ON (TRUNC(C.CAL_DATES) = TRUNC(A.APPOINTMENT_DATE))
                 )
SELECT  JSON_SERIALIZE(
                       JSON_OBJECT(
                                   KEY CAL_MON
                                   VALUE JSON_ARRAYAGG(
                                                    JSON_OBJECT(
                                                                KEY 'date'
                                                                VALUE TO_CHAR(CAL_DATES,'DD-MON-YYYY'),
                                                                KEY 'val'
                                                                VALUE NVL(APPOINTMENT_ID,0)
                                                               )
                                                    ORDER BY CAL_DATES
                                                   )
                                  )
                       RETURNING CLOB
                       PRETTY
                      ) Final_output
  FROM  GET_JSON
  GROUP BY CAL_MON
  ORDER BY CAL_MON
/

FINAL_OUTPUT
------------------------------------------------------------------------------------------
{
  "JUL" :
  [
    {
      "date" : "01-JUL-2022",
      "val" : 0
    },
    {
      "date" : "02-JUL-2022",
      "val" : 0
    }
  ]
}
{
  "JUN" :
  [
    {
      "date" : "01-JUN-2022",
      "val" : 0
    },
    {
      "date" : "02-JUN-2022",
      "val" : 0
    },
    {
      "date" : "03-JUN-2022",
      "val" : 0
    },
    {
      "date" : "04-JUN-2022",
      "val" : 0
    },
    {
      "date" : "05-JUN-2022",
      "val" : 0
    },
    {
      "date" : "06-JUN-2022",
      "val" : 0
    },
    {
      "date" : "07-JUN-2022",
      "val" : 1
    },
    {
      "date" : "08-JUN-2022",
      "val" : 0
    },
    {
      "date" : "09-JUN-2022",
      "val" : 2
    },
    {
      "date" : "10-JUN-2022",
      "val" : 3
    },
    {
      "date" : "11-JUN-2022",
      "val" : 0
    },
    {
      "date" : "12-JUN-2022",
      "val" : 0
    },
    {
      "date" : "13-JUN-2022",
      "val" : 0
    },
    {
      "date" : "14-JUN-2022",
      "val" : 0
    },
    {
      "date" : "15-JUN-2022",
      "val" : 0
    },
    {
      "date" : "16-JUN-2022",
      "val" : 0
    },
    {
      "date" : "17-JUN-2022",
      "val" : 0
    },
    {
      "date" : "18-JUN-2022",
      "val" : 0
    },
    {
      "date" : "19-JUN-2022",
      "val" : 0
    },
    {
      "date" : "20-JUN-2022",
      "val" : 0
    },
    {
      "date" : "21-JUN-2022",
      "val" : 0
    },
    {
      "date" : "22-JUN-2022",
      "val" : 0
    },
    {
      "date" : "23-JUN-2022",
      "val" : 0
    },
    {
      "date" : "24-JUN-2022",
      "val" : 0
    },
    {
      "date" : "25-JUN-2022",
      "val" : 0
    },
    {
      "date" : "26-JUN-2022",
      "val" : 0
    },
    {
      "date" : "27-JUN-2022",
      "val" : 0
    },
    {
      "date" : "28-JUN-2022",
      "val" : 0
    },
    {
      "date" : "29-JUN-2022",
      "val" : 0
    },
    {
      "date" : "30-JUN-2022",
      "val" : 0
    }
  ]
}
{
  "MAY" :
  [
    {
      "date" : "22-MAY-2022",
      "val" : 0
    },
    {
      "date" : "23-MAY-2022",
      "val" : 0
    },
    {
      "date" : "24-MAY-2022",
      "val" : 0
    },
    {
      "date" : "25-MAY-2022",
      "val" : 0
    },
    {
      "date" : "26-MAY-2022",
      "val" : 0
    },
    {
      "date" : "27-MAY-2022",
      "val" : 0
    },
    {
      "date" : "28-MAY-2022",
      "val" : 0
    },
    {
      "date" : "29-MAY-2022",
      "val" : 0
    },
    {
      "date" : "30-MAY-2022",
      "val" : 0
    },
    {
      "date" : "31-MAY-2022",
      "val" : 0
    }
  ]
}

SQL>

SY.

Marked as Answer by user525840 · Jun 7 2022
user525840

Thank you everyone for quick turnaround.
Using the suggested query, I can see the expected output. I will now pass it to the Oracle JET web component to use this json data instead of static json file.
I will create a procedure returning clob output from this SQL and use that in array data provider in front end.
Thank you.

Solomon Yakobson

Keep in mind NEXT_DAY is NLS dependent so if your stored procedure will be executed by, say, client in Israel:

SQL> create or replace
  2    procedure p1
  3      is
  4      begin
  5          dbms_output.put_line(NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY'));
  6  end;
  7  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1;
22-MAY-22

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_language=hebrew;

Session altered.

SQL> exec p1;
BEGIN p1; END;

                                                                          *
ERROR at line 1:
ORA-01846: not a valid day of the week
ORA-06512: at "SY47755.P1", line 4
ORA-06512: at line 1

SQL>

You could use 'IW' format instead but it is simpler to use pre-defined date (any Sunday):

TRUNC(SYSDATE,'MONTH') - 15 + 1 + MOD(ABS(TRUNC(SYSDATE,'MONTH') - 15 - DATE '2022-05-29') - 1,7)

Similarly you can get next Saturday (or any day).
SY.

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 20 2008
Added on Sep 19 2008
8 comments
596 views