Hot answers tagged indexed-view
11
All three columns are persisted to disk in the clustered index on the indexed view (no different, really, from a clustered index on a regular table). You can validate this using DBCC PAGE. I created the following structure in tempdb:
USE tempdb;
GO
CREATE TABLE dbo.a(a INT);
CREATE TABLE dbo.b(a INT, b DATETIME);
CREATE TABLE dbo.c(a INT, c CHAR(32));
...
10
Not allowing customers to have a less than 0 balance is a business rule (which would change quickly as fees for things like over draft are how banks make most of their money). You'll want to handle this in the application processing when rows are inserted into the transaction history. Especially as you may end up with some customers having overdraft ...
10
An indexed view is physically stored ("materialised") on disk = requires memory
A standard view is simply an expandable macro: there is no persistence of the data and the base tables are always used
Other notes:
Both will return the correct data from the base tables
SQL Server will consider whether to use the indexed view or just expand it like a macro ...
8
So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more ...
7
Matching indexed views is a relatively expensive operation, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan ...
7
Resolving indexes on Views:
In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used.
I'm guessing that you're on Standard Edition.
6
The obvious risk from making this change is that vendor queries that previously ran correctly could start to throw errors, or return incorrect results. The ARITHABORT setting partly controls whether arithmetic overflow and divide-by-zero errors return a NULL result, terminate the statement with an error, or terminate the batch with an error. How the vendor ...
6
NEXT SYSDATE +1 means the materialized view will be automatically refreshed every day at the same time when the view is created.
In order to refresh the materialized view every 6 hours use NEXT SYSDATE +6/24
DISABLE QUERY REWRITE indicates that the materialized view is not eligible to be used by the query optimizer to rewrite sql queries on the base ...
6
A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.
Do you need a materialized view? Could you maintain your own staging table and create a custom ...
5
After reading these two discussions, I decided on option 2
Having read those discussions too, I am not sure why you decided on the DRI solution over the most sensible of the other options you outline:
Apply transactions to both the transactions and balances tables.
Use TRANSACTION logic in my stored procedure layer to ensure that
balances and ...
5
A basic version of materialized view are being delivered in 9.3, see depesz's article. However, it's a very limited version as you have to call REFRESH MATERIALIZED VIEW to get the database to update the view. This will hopefully improve to become automatic in later versions soon.
4
I can't understand why you can't aggregate on the fly and why it's so slow. Is the "Valid" idea a workaround to deal with the lag of OrderTotals or some business process
Both of these ideas discard the InvalidOrder table which is a workaround for poor indexing.
Suggestion 1:
Create a computed column
ALTER TABLE dbo.Order ADD PriceXQuantity AS Price * ...
4
240 msec isn't that bad for a complex view involving unions and subqueries. It's not great, but I've seen a lot worse. Beyond this, I'm afraid I have more questions than answers.
It's not clear from your question whether this is currently an indexed view. Is it?
It sounds like the first user to hit stale data will have to wait at least a full second for ...
4
A slightly different approach (similar to your 2nd option) to consider is to have just the transaction table, with a definition of:
CREATE TABLE Transaction (
UserID INT
, CurrencyID INT
, TransactionDate DATETIME
, OpeningBalance MONEY
, TransactionAmount MONEY
);
You may also want a transaction ...
4
It appears to do it in sequence in your plan. Not in parallel. The insert happens into the heap. The inserted rows are inserted into the eager spool which is a blocking operator. When all rows are inserted into the base table the rows are inserted from the spool to the view.
As for the percent issue it appears that the spool confuses SSMS. Despite showing ...
4
I believe that the cast to XML is killing performance for you and what is happening is described by Paul White Compute Scalars, Expressions and Execution Plan Performance
The cast to XML is deferred to where you actually use the XML column so in your query the cast happens three times for each row returned.
3
Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH.
In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g).
You could also define your ...
3
Typically you index views if you are often running aggregates, not to magically speed up joins. Also if you are not using table partitioning there is no good reason to investigate partition-aligned indexed views.
You should be focusing on optimizing the query, irrespective of the view, IMHO. An indexed view is not a magic turbo button (though a lot of ...
3
This happens often if the optimiser thinks it can do better with the base tables.
If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it.
Example:
SET STATISTICS IO ON;
SET ...
3
You want to store the details of the transaction at the time it happens and not calculate at the time you select the record. This is because the information the calculation is based on (such as tax rate ) is subject to change. So yes, store the tax calculation. This is not an issue of performance, it is an issue of temporal data than must be shown as of teh ...
2
This is just intended to demonstrate redo usage of various insert operations rather than answer the whole question. Results on my 10g instance are not 100% deterministic, but the broad picture remained the same each time I ran through.
For the heap tables, I do not know why the insert /*+ append */ generated more redo.
testbed:
create table ...
2
If the cost would not be too high, you could consider moving the IsValid flag into the Detail table and index it? This would slow your transactions but improve query performance as the (presumably) large Detail table would be accessed in one big range scan
2
Nick.
Main idea is storing balance and transaction records in the same table. It happened historically I thought. So in this case we can get balance just by locating the last summary record.
id user_id currency_id amount is_summary (or record_type)
----------------------------------------------------
1 3 1 10.60 ...
2
If drop materialized view is leaving an entry in dba_summaries, then you are hitting a bug - perhaps the one described here
As you do not have a support contract, you can't raise a TAR. However the first question Oracle Support would ask you is whether you are running the latest patchset, 11.2.0.3 - I suggest you consider this option first as the root ...
2
I am not familiar with accounting, but I solved some similar problems in inventory-type environments. I store running totals in the same row with the transaction. I am using constraints, so that my data is never wrong even under high concurrency. I have written the following solution back then in 2009::
Calculating running totals is notoriously slow, ...
2
You need to set JOB_QUEUE_PROCESSES to a positive number in order to enable database jobs to run. JOB_QUEUE_PROCESSES controls the number of jobs that are allowed to run simultaneously so if you set the parameter to 1, only 1 job could run at a time. If you're going to be creating multiple materialized views that you want to refresh at approximately the same ...
2
To support Raihan's correct answer+1, here are some excerpts from the 11.2 SQL Language Reference.
START WITH Clause
Specify START WITH date to indicate a date for the first automatic
refresh time.
NEXT Clause
Specify NEXT to indicate a date expression for calculating the
interval between automatic refreshes.
QUERY REWRITE Clause
...
2
OK I found the solution, and I am writing it here not just to help others that encounter a similar problem, but it is also something worth for everyone that works with indexed views.
First some points on indexed views.
Indexed Views
An index view although it is also an "index" it is also a "view", thus it might benefit when the system is updating the ...
2
The key phrase is "after batch row loading". If your process does not meet this criteria then you do not need to drop/rebuild the bitmap indexes.
Jonathan Lewis has an excellent article on this. A key point he gives is
You can get lucky -- but in general you should start with the
assumption that even a serialized batch update will be most effective
...
2
You could create a materialized view containing data just from the last 24 hours. To do so, you'll need to create a single row table containing just the date after which you want data to be returned however. Your refresh process will need to update this table to the date you want before doing the fast refresh:
create table tab (x integer primary key, ...
Only top voted, non community-wiki answers of a minimum length are eligible
