Home » SQL & PL/SQL » SQL & PL/SQL » nested views with hints (oracle 9i)
nested views with hints [message #358278] Mon, 10 November 2008 08:30 Go to next message
peterb
Messages: 1
Registered: November 2008
Junior Member
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
Re: nested views with hints [message #358280 is a reply to message #358278] Mon, 10 November 2008 08:41 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about your posting the explain plan of the query and the tkprof output of the query. Hints are not silver bullet. There are few hints which are useful but most of the hints are used for the wrong purpose in other words you are using hints to mask an existing issues which might eventually make the database crawl. So don't rely on hints all the times instead of analysing the root cause of the problem.

Regards

Raj
Previous Topic: Date data type
Next Topic: CREATE OR REPLACE FUNCTION
Goto Forum:
  


Current Time: Sat Dec 03 01:07:08 CST 2016

Total time taken to generate the page: 0.21329 seconds