Re: Problem: only sensible queries allowed

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 19 Oct 2009 06:47:48 +0100
Message-ID: <7765c8970910182247y4e2c73bdy545dd560c1d47670_at_mail.gmail.com>

Does resource manager not work for you? If not and you already have BI tools like the ones Ken mentions you can achieve 2 things.

1. a view of the data that makes business sense so people are more likely to ask sensible questions of the data and
2. most of them have built in resource governors that limit queries by duration etc.

Niall

On Mon, Oct 19, 2009 at 4:45 AM, Tony Adolph <tony.adolph.dba_at_gmail.com>wrote:

> Hi All,
>
> I've been asked to provide our marketing people select access on some large
> tables, but want to stop them using "stupid" where clauses that stop
> partition pruning / index lookup.
>
> E.g.
>
> SELECT ......
> FROM PM_RATED_CDRS
> WHERE to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') > '20090914 00:00:00'
> AND to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') <= '20090917 13:00:31'
>
> or (this one's a cracker,..)
>
> select ....
> from....
> where
> and (trunc(a.CALL_DATE) >=
> trunc(to_date('20070101000000', 'yyyyMMddHH24MISS')))
> and (trunc(a.CALL_DATE) <
> trunc(to_date('20090922235959', 'yyyyMMddHH24MISS') + 1))
>
> BTW: The above 2 examples are real and I've feedback some polite
> suggestions on the use of dates, how trunc works etc. to our application
> developers. Note: I don't want to create a load of unnecessary functional
> indexes when its the code that needs fixing.
>
> So I can try to fix the application developer's dodgy stuff.
>
> But is there any way that I can block a query if it breaks some simple
> rules? Or allow only a short list of columns that can be used in the
> query?
>
> Thoughts:
>
> 1) I was thinking about creating some views on the tables in question and
> giving these to marketing.
> But how can I stop select * from CDR_V1 where to_char(RATED_ON, 'YYYYMMDD
> HH24:MI:SS') > '20090914 00:00:00'
>
> 2) Initially I was thinking about a load of pipelined functions that could
> be used something like
> select * from table(get_cdrs_for_date_range(to_date('20070101000000',
> 'yyyyMMddHH24MISS'), to_date('20090922235959', 'yyyyMMddHH24MISS') + 1));
>
> but I may asking for a lot of work here,... "can we please have another one
> that......"
>
>
> Any ideas folks?
>
> Cheers
> Tony
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 19 2009 - 00:47:48 CDT