Skip to Main Content
Forums

Oracle Database Discussions

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!

SQL plan indicates table scan

1000029Jun 7 2013 — edited Jun 10 2013
Oracle Database 10g Release 10.2.0.1.0 
select  m.makeupid, j.jobnumber
from    Makeup m, Job j, Jobcrew jc
where   m.workdate > sysdate
and     m.jobcrewid = jc.jobcrewid
and     jc.jobid = j.jobid
shows a plan of 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |   159 |  6519 |   125   (2)| 00:00:02 |
|*  1 |  HASH JOIN                    |               |   159 |  6519 |   125   (2)| 00:00:02 |
|*  2 |   HASH JOIN                   |               |   321 |  9309 |    98   (2)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MAKEUP        |   353 |  6707 |    75   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_MAKEUP_DT |   355 |       |     4   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | JOBCREW       | 11627 |   113K|    22   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL           | JOB           |  5082 | 60984 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("JC"."JOBID"="J"."JOBID")
   2 - access("M"."JOBCREWID"="JC"."JOBCREWID")
   4 - access("M"."WORKDATE">SYSDATE@!)
Table Makeup has an index on WorkDate, which is being used, but a full table scan happens on both JobCrew and Job.
The primary key for table Job is field JobId, which being the primary key should be indexed on
Similarly, the primary key on table JobCrew is JobCrewId which is also not being used
ALTER TABLE JOBCREW ADD CONSTRAINT SYS_C005219  PRIMARY KEY (JOBCREWID)
ALTER TABLE JOB ADD CONSTRAINT  SYS_C005144  PRIMARY KEY (JOBID)
Any ideas on why these table scans happen and how these can be prevented? Thanks.

Edited by: 997026 on Jun 7, 2013 3:54 PM

Comments

Timo Hahn

User, tell us your exact JDev version, please!
Your REST endpoint wouldn't make sense this way. The 'Employees' represent a collection that isn't parameterized by default. That's why you normally add something like 'action' or 'acton/test1' to the endpoint to make sure everybody knows that you do something on the collection of employees.
I would not encourage you to use the method at all, as in 12.2.1.4 you don't even get it in the wizard. It's easier to create your own REST service and call the application module methods from there. This way you can design your own API, e.g more like OpenAPI instead of relying on JDev and ADF.

Timo

Joby-Oracle

I am using Jdev 12.2.1.5.0.
I used the above link for reference.
My actual URL looks like:
{{hostAddress}}/restApp/resources/11.13.18.05/employees
I have added manually methodAction tag in the resource xml file , to enable the custom method.
So you are saying that this is not possible using ADF .

Timo Hahn
Answer

There is no official version 12.2.1.5. You are using an internal version not available to the public.
All I'm saying is that you don't even see the method tab in 12.2.1.4 and custom methods are depricated. So, yes, from my point of view you can't do it with ADF REST service.
It might be possible that you see the tab in your version, but then you have to ask this question to some internal resource.

Timo

Marked as Answer by Joby-Oracle · Jun 2 2022
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 8 2013
Added on Jun 7 2013
#general-database-discussions, #index
16 comments
715 views