A virtual table that represents the data in one or more tables in an alternative way.
0
votes
0answers
22 views
Procedure after commit
I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
0
votes
1answer
30 views
Mysql database user specified as a definer
I have a view in my database. problem is below
Error
SQL query:
SELECT *
FROM `lumiin_crm_prod`.`v_contact`
LIMIT 1 ;
MySQL said:
1449 - The user specified as a definer ('lumicrm'@'%') ...
0
votes
2answers
24 views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
3
votes
1answer
89 views
What is a partitioned view?
What is a partitioned view? I googled but could not get a satisfactory result.
1
vote
3answers
67 views
do MYSQL views occupy physical space?
do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
-1
votes
1answer
44 views
Odd syntax error when creating view with SCHEMABINDING [closed]
I've been working with SQL Views for a while now, and I recently read that something that I want to do requires that one of my views be created with schemabinding. That sounds fine, but I get a ...
-1
votes
1answer
111 views
How to create view in SQLite using INSERT INTO? [closed]
I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly ...
1
vote
1answer
73 views
Does the SQL Server Backup Database Command also backup views from a Database?
I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This ...
1
vote
1answer
46 views
Can't query one view from another?
Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, ...
2
votes
2answers
68 views
View Disc Usage
Is this statement correct?
Behind the scenes SQL Server actually stores the data in the view as a
physical table which must be updated if any of the data behind the
view changes
I did some ...
2
votes
1answer
110 views
Can a view affect ALTER TABLE commands?
I provide a database system to a number of customers. The database is installed locally on Oracle.
We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
2
votes
1answer
88 views
Indexed view not being used - SQL Server [duplicate]
I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
0
votes
0answers
20 views
View is not dropping
I tried removing a view in HeidiSQL, and it froze bunch of times. So I connected to a database with Putty, ran a command
DROP VIEW item_view;
and nothing happened, its still running and command ...
4
votes
1answer
140 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
1answer
23 views
Two dichotomy views of a table, and means to express this as a stored procudure?
There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events.
How to express ...
1
vote
1answer
58 views
Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view
I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization"
It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
0
votes
1answer
61 views
View dropped, now shows as table “in use”, can't drop
I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid.
I tried dropping the view so I could recreate it, then MySQL said it was a table ...
1
vote
0answers
30 views
CouchDB views and references
Not sure if/how it is intended to be done but i am trying to match up all the active document in my database with the author of it:
function(doc) {
if (doc.data.active && doc.data.active ...
1
vote
1answer
47 views
Is it a best practice to contrast table and dependent view metadata to target which views to refresh?
We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table ...
0
votes
1answer
77 views
Create a VIEW that spans 3 tables
Sorry if the title is not clear - feel free to improve.
Here are 3 tables (not all data is relevant to the question):
CREATE TABLE `content` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
0
votes
2answers
114 views
MySQL Reusing calculation in SELECT clause in a VIEW
I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all?
...
0
votes
0answers
25 views
how to tranlate MySQL code so that it can be used in PHP MyAdmin
I have a problem with my database view that is answered in another post. The answer shows the following solution:
mysql -uusername -ppassword -A --skip-column-names -e"
SELECT CONCAT('SHOW ...
3
votes
1answer
112 views
Adding indices to views
I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table.
...
5
votes
1answer
130 views
Are view references in a query properly called “derived tables” as such?
While answering a question on stackoverflow, I presented a definition of derived tables:
A derived table is a complete query, inside of parentheses, that is used as if it were a real table.
But ...
0
votes
1answer
44 views
Where can I read about views for MySQL?
I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing.
I found this view And I am sure that 5 minutes for you to answer this ...
4
votes
2answers
177 views
Oracle View Ignoring External WHERE Clause
Good day,
This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but ...
1
vote
1answer
50 views
What is the locking behavior for Updatable/Insertable Views?
Consider I have a View that is merely a simple join between 2 tables, selecting all columns.
The View is Updatable and Insertable.
What would be the locking behavior when executing an UPDATE or ...
2
votes
2answers
97 views
Migrating SQL Server 2000 to SQL Server 2008
I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause?
Thanks.
4
votes
1answer
228 views
need suggestions to improve view performance
I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
0
votes
1answer
17 views
viewing Original Object under synonyms
I have certain synonyms in my schema which represent views from another schema. Now in oracle sqldeveloper, I am unable to see the actual SQL for those views but I see only an SQL like:
CREATE OR ...
0
votes
1answer
90 views
Retrieving a fixed range of rows from Postgresql
I have a query:
select qid,ansid,ans from table1 where askerid='something'
This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
1
vote
2answers
624 views
SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?
I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group ...
4
votes
1answer
303 views
Determining whether column in view can only contain unique values
I have a SQL Server 2008 database with several views. I'm looking for a way to definitively determine whether a particular view has a column that can only contain unique values (i.e. can be used as a ...
0
votes
2answers
58 views
View for a MANY_MANY_MANY relationship
Let's say I have 3 main tables:
category, tag, url
category and tag have a MANY_MANY relationship (category_tag).
tag and url have a MANY_MANY relationship (url_tag).
I would like for a given ...
12
votes
1answer
624 views
Execution plan shows expensive CONVERT_IMPLICIT operation. Can I fix this with indexing or do I need to change the table?
I have a really important, really slow view which includes some really ugly conditions like this in its where clause. I am also aware that the joins are gross and slow joins on varchar(13) instead of ...
2
votes
2answers
117 views
Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?
SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in.
I'm looking for the equivalent in MySQL - ...
2
votes
1answer
572 views
Grant select to dbo view without select on non dbo tables
SQL Server 2008 R2
We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database.
We then granted it SELECT on a dbo ...
1
vote
2answers
902 views
Why an object became invalid in oracle databse.
There are some invalid views and functions in my database.
I am not sure what changes caused these objects to became invalid since many people are working on the same database.
How can I see which ...
1
vote
2answers
2k views
SQL query to return a column per date in date range?
Let me explain what I've got and what I'm trying to achieve.
Let's say I have Table A: BookingsPerPerson
Person_Id ArrivalDate DepartureDate
123456 2012-01-01 2012-01-04
213415 ...
2
votes
2answers
93 views
(MySQL) How to change the results of a view according to the user that's viewing it?
I have a table, named servers, with a single column id.
This table is accessed by various machines each with a different username for the database.
I want to make it so each server does not see all ...
2
votes
2answers
483 views
IN Clause causes Execution plan to change from Nested Loops to Hash Match
I'm tuning a query and have discovered some behaviour I'm not clear about.
If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.
There only 7 rows returned in the ...
3
votes
1answer
78 views
Slow MySQL query, not sure why
I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect:
create or replace view propertytaxessub0 as select
...
0
votes
1answer
131 views
Combining a tree structured Group model with its HABTM User model in one VIEW
I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a ...
4
votes
2answers
426 views
SQL Server Linked Server to Progress is slow with an openquery view
We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database. We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code ...
2
votes
2answers
1k views
How to list all views in SQL in PostgreSQL?
How do I list all views for a database using an SQL command in PostgreSQL?
I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,
SELECT ...
5
votes
2answers
446 views
Create index view with self join
I use SQL Server 2008 R2 and know that in Indexed View cant have self join.
I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
-1
votes
1answer
98 views
Selecting just one table from Oracle view [closed]
I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins.
...
14
votes
4answers
1k views
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
3
votes
3answers
221 views
Performance of Inline-TVF vs. Views
I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
1
vote
1answer
125 views
What is a good way to query data stored in multiple identical tables split by date/time
I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples ...
