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.
Thanks.

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
manzoor_dba

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

Post the explain plan.

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


then
SELECT * FROM TABLE(dbms_xplan.display);
SOLUTION
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
or
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
to_number(regexp_replace(vw_table.col1,'[^0-9]'));
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.
fblack61
ASKER CERTIFIED SOLUTION
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.
ASKER
newtoperlpgm

Thank for all your input, I indexed the tables and that helped.
manzoor_dba

Hi,

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