• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Oracle sql query

I have a query that can probably be better structured.  I am quite a novice and would like input on how to build a better query, because I suspect this is a slow query based on the OR that I have in there several times.  See my query below.  I'm using the same text field for my query parameter, and based on a radio select button, the parameter will query a specific column (i.e., they can click the radio button for col1, col2, col3, col4, etc. to search by that column.

SELECT col1, col2, col3, col4, col5, col6, col7, col8
where vw_table.col1 = :P1_PARM
OR vw_table.col2 = :P1_PARM
OR vw_table.col3 = :P1_PARM
OR vw_table.col4 = :P1_PARM
OR vw_table.col5 = :P1_PARM
OR vw_table.col6 = :P1_PARM
OR (vw_table.col7 >= to_date(:P1_DATE, 'mm/dd/yyyy')
and vw_table.col7 < to_date(:P1_DATE, 'mm/dd/yyyy') + 1)
OR vw_table.col8 >= to_date(:P1_DATE_BEGIN, 'mm/dd/yyyy')
and vw_table.col8 < to_date(:P1_DATE_END, 'mm/dd/yyyy') + 1
order by vw_table.col1, to_number(regexp_replace(vw_table.col1,'[^0-9]'));
2 Solutions
slightwv (䄆 Netminder) Commented:
Post the explain plan.

explain plan for
select  col1, col2, col3, col4, col5, col6, col7, col8

SELECT * FROM TABLE(dbms_xplan.display);
slightwv (䄆 Netminder) Commented:
You are probably doing a full table scan all the time unless all columns are indexed.

Based on what you have, not sure you can get around a full table scan.
DavidSenior Oracle Database AdministratorCommented:
Might help to include the from clause in the statement....
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Terry WoodsIT GuruCommented:
To order by
is very inefficient if you have a large number of rows. I believe this by itself forces a full table scan, whereas indexes can possibly speed up all other aspects of the query.

If you can store that value within the table itself (and keep it up to date over time) and index it then you may be able to improve the performance (assuming you have indexes on all the other columns used to filter in the query too).
2 small observations
a. there's no from clause  :) {but assume there really is one}
b. think you need extra parentheses around the final 2 where conditions

OR ( vw_table.col8 >= to_date(:P1_DATE_BEGIN, 'mm/dd/yyyy')
and vw_table.col8 < to_date(:P1_DATE_END, 'mm/dd/yyyy') + 1 )

btw: for a novice you have chosen wisely for how to filter by a date range

an explain plan would be useful (nb: please post the text, not an image)
but also think you probably do have a full table scan
and also agree with the comment on the order by including regexp_replace()
newtoperlpgmAuthor Commented:
Thank for all your input, I indexed the tables and that helped.

If you got the answer, then pls close the thread and assign points to those who have assisted you :). Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now