Apologies in advance for the non technical nature and nursery school level of this post but I’m trying to determine whether a client of ours (the BOXI user) is feeding us a line.
My company provides an application that this client uses. Our app is uses Oracle DB and as part of the deliverable(s) we have a provided a number of Oracle Views. At the risk of stating the obvious, the client will use the views provided to write their own reports using BOXI.
They have some performance issues with their reports, in so much as the views are retrieving far more information than they need (e.g. a years worth of account data when the report is only interested in last months).
So my question is, and I hope its a valid one, is there any way through BOXI, that conditions/filters (or similar) so say a date range, can be passed to the database layer, whilst still using the views supplied, so that only pertinent subset of data is retrieved?
Obviously new views can be provided however I’m trying to explore simpler options to begin with.
No they’re not materialised views and of course this is an option however involves Client/Vendor change requests, cost time etc. etc. probably the best way forward.
However in the short term is there anything that cna be sent from BOXI?
Obviously, making the views materialised will increse performance, providing the stats are up to date.
The link, I provided above, explains how to create various date time filters in the universe. You can also enhance these with @Prompt functionality for user interaction.
To be clear, this will work with what you already have now.
Thanks for all your help…and just one final thickie PM question if I may…
so by applying the appropriate filters (e.g. date/time related) to the existing views, there will only be a one time retrieval of the appropriate subset of data? This would then be available for specific onward reporting?
No, I would set up a series of filters or prompts, in the universe, do not directly filter the actual views themselves.
By setting these in the universe you can have flexible filters that can be set for This Year, Last Year, This Week, Last Week and so on.
Once these are done it is childs play to schedule the report outputs, in any case, ready for the user to access instantly.
How complicated are the views that you provide to your client?
The solution described by Mak 1 is perfectly fine provided that the Oracle optimizer pushes the predicates (the new where clauses applied on the views) down to the views’ SQL statements.
However, if your views are more complex (for instance view on top of a view on top of a view) then the optimizer is not able to do this “predicate pushdown”. So first the SQL of a view is executed and only then the additional WHERE clause is applied on the resulting data set. In this case you would probably not see the expected boost in performance.
The good thing is that you should be able to try this predicated pushdown for every of your views in your environment so you can test whether it works and you see a faster response or not.
To the best of my understanding the views are directly against a single table but do get a bit complex with aggregates and back referencing other data in the table in a sort of parent/child hierarchy.
However, our DBA ran a select against the view saw the full table scan then ran the same query but added a date condition and saw index scans
so I guess we just want BOXI to replicate this behaviour…
If you avoid any date transformations / SQL functions at the universe side, then the indexes will be used. The ideal way to do this is to create a calendar table and index the table appropriately.
B!
(BOB member since 2005-01-06)
(BOB member since 2003-11-27)