Home » SQL & PL/SQL » SQL & PL/SQL » Get Main Filters (10g)
Get Main Filters [message #347421] Thu, 11 September 2008 10:23 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I'm currently trying to query a view from another database. Say we have a_vw, then when I try to view it's contents jut to browse the data it's taking too long on TOAD. So I ask the other team what *main filters* should I used on that view to filter out minimal data. At first I tried checking the base tables to chek on the indexes, etc. but it just says object not exists, I also tried describing on schema.table way but still no access on it. So I then told the other team about it but then the reply we just got is this:

Quote:

find the indexes on the table
write the query so you walk the index
use F5 in pl/sql to see the explain plan



But the user we have had only access to the view. Am I missing something here? or was the advise they gave would work? I tried doing a query on that view but just extracting explain plan and it just says no grants on underlying tables. Just want to know If there's other way to identify the main filters for that view or table with just the current grants we have. Thanks.

[Updated on: Thu, 11 September 2008 10:26]

Report message to a moderator

Re: Get Main Filters [message #347422 is a reply to message #347421] Thu, 11 September 2008 10:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I'm not sure, but maybe the SQL*Plus tracing facility still works without access to the base tables.

Can you try an

SQL> set autotrace traceonly
SQL> select * from a_vw;


and maybe an
SQL> set autotrace traceonly
SQL> select /*+FIRST_ROWS */  * from a_vw;


to see if that gives any meaningful output?

(With the first_rows hint Oracle should try to give you the first rows quicker, and thus you might see some other indexes used there in comparison to the query without the hint.)

[Updated on: Thu, 11 September 2008 10:44]

Report message to a moderator

Re: Get Main Filters [message #347424 is a reply to message #347422] Thu, 11 September 2008 10:51 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks for the reply, here's what I got:

SQL> select t.* --t.eti
  2  from prism.trade_queue_vw t
  3  where t.pricing_dt = to_date('09-sep-08', 'dd-mon-yy')
  4    and t.event_id = 2;

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report
SQL> select /*+ FIRST_ROWS */ t.*
  2  from prism.trade_queue_vw t
  3  where t.pricing_dt = to_date('09-sep-08', 'dd-mon-yy')
  4    and t.event_id = 2;

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report
SQL>


Does that mean I really should have grants (select) on the base tables?
Re: Get Main Filters [message #347426 is a reply to message #347424] Thu, 11 September 2008 10:57 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep, it looks that way.

Either that, or they tell you which columns you should use to filter.

Otherwise you could only go the "trial and error" route.
Re: Get Main Filters [message #347430 is a reply to message #347426] Thu, 11 September 2008 11:24 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Whewww! I guess that's the only way =) Will use rownum to browse.
Previous Topic: Update query problem (merged)
Next Topic: Select Count Within Cursor Loop
Goto Forum:
  


Current Time: Wed Dec 07 14:36:59 CST 2016

Total time taken to generate the page: 0.11238 seconds