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]'));
newtoperlpgmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Post the explain plan.

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


then
SELECT * FROM TABLE(dbms_xplan.display);
0
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.
0
DavidSenior Oracle Database AdministratorCommented:
Might help to include the from clause in the statement....
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Terry WoodsIT GuruCommented:
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).
0
PortletPaulfreelancerCommented:
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()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
newtoperlpgmAuthor Commented:
Thank for all your input, I indexed the tables and that helped.
0
manzoor_dbaCommented:
Hi,

If you got the answer, then pls close the thread and assign points to those who have assisted you :). Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.