I have a view: v1 with 30 tables linked to each other. some with 300,000 records. but all indexed etc.
When i do
A)
SELECT *
FROM v1
WHERE panel_name LIKE '1234%'
This is solved very fast within a few seconds. The cost if I do a executing plan is a bout 7283
Now I do
B)
SELECT v1.*
,ROW_NUMBER() OVER(PARTITION BY
PANEL_ID, STRIP_ID, TERM_ID
ORDER BY l_CABLE_ID,
l_CABLE_SET_ID, l_WIRE_ID,
r_CABLE_ID, r_CABLE_SET_ID,
r_WIRE_ID) DOUBLE_WIRE
FROM v1
WHERE panel_name LIKE '1234%'
This is too solved very fast within a few seconds. The cost if I do a executing plan is a bout 7366
So this is a bit more but still aceptable.
Now I create v2 as
C)
SELECT v1.*
,ROW_NUMBER() OVER(PARTITION BY
PANEL_ID, STRIP_ID, TERM_ID
ORDER BY l_CABLE_ID,
l_CABLE_SET_ID, l_WIRE_ID,
r_CABLE_ID, r_CABLE_SET_ID,
r_WIRE_ID) DOUBLE_WIRE
FROM v1
After this I do
D)
SELECT *
FROM v2
WHERE panel_name LIKE '1234%'
Now this is solved in about 5 minutes. The cost if I do a executing plan is about 500000
I want to put in some hints so that oracle solves this simulair to B)
Can anybody tell me what hints I need and where to put them?
Regards,
Peter