Avatar of newtoperlpgm
newtoperlpgmFlag for United States of America asked on

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]'));
Oracle DatabaseSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon
slightwv (䄆 Netminder)

Post the explain plan.

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

SELECT * FROM TABLE(dbms_xplan.display);
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
David VanZandt

Might help to include the from clause in the statement....
Terry Woods

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).
Your help has saved me hundreds of hours of internet surfing.
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.