Link to home
Start Free TrialLog in
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]'));
Avatar of slightwv (䄆 Netminder)
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Might help to include the from clause in the statement....
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).
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of newtoperlpgm

ASKER

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

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