JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 11: Views.


Addenda

Partition Views (19th April 2001)

Using * in views (3rd Mar 2001)

 

Errata

 

Back to Book Index

 

Partition Views:

I have devoted a couple of pages (p.233-235) lamenting the passing of partition views. However, there have been a couple of comments on newsgroups and public presentations recently of a new partitioning option in Oracle 9 called list partitions. From the odd details that have been mentioned, it sounds as if these may address many of the issues that partition views were so good for. I have no idea what the syntax is, or whether you can use list partitions inside range partitions, but the approach seems to be something like:

	partition by list (country_code) (
		partition1 values ('UK'),
		partition2 values ('France'),
		partition3 values('Belgium','Netherlands','Luxembourg')
	)

(Compare this with the alternative of hash-partitioning by US state that I describe in the book).

If you are working on an application that looks as if it would be best served by discrete partitioning, it may be a good idea to start trawling the Oracle Technet for any details you can glean on this feature. I think it has the potential to be very useful to lots of systems.

Top of page

 

Using * in views

There is an interesting difference between using a simple '*' to define a view, and using 'alias.*' to list all the columns in a table, and this could have some startling, and painful, side-effects. Let's start with a simple example that uses the same base table, and creates the same view using the two different forms of syntax. (Assume we drop all relevant objects before we start).

	create table t1(a varchar2(1), b varchar2(1));
	insert into t1 values ('a','b');
	commit;

	create or replace view v1 as
	select * from t1;

	create or replace view v2 as
	select t1.* from t1;

	select * from v1;
	select * from v2;

Everything is find so far, in both cases, the output will be:

	A B
	- -
	a b

However, looking at the view texts stored in the database (view user_views) we can see that the two views have different definitions, even though the descriptions of the view content (view user_tab_columns) appears to be identical:

	select text from user_views
	where view_name in ('V1','V2')
	order by view_name;

	select table_name, column_name, column_id 
	from user_tab_columns
	where table_name in ('V1','V2')
	order by table_name, column_id;
	TEXT
	----------------------
	select "A","B" from t1
	select t1.* from t1
	
	TABLE_NAME           COLUMN_NAME            COLUMN_ID
	-------------------- ---------------------- ---------
	V1                   A                              1
	V1                   B                              2
	V2                   A                              1
	V2                   B                              2

So where do things go wrong - let's try two different experiments, starting from the point above. In the first experiment, we drop and recreate the table with the columns in the opposite order (and people do rebuild tables with changed column orders from time to time - it can add a little efficiency to the system). In the second experiment we will simply add a column to the table, (poeple do add columns to tables from time to time - the result is the same whether we drop it first or not.)

	drop table t1;
	create table t1(b varchar2(1), a varchar2(1));
	insert into t1 values ('b','a');
	commit;

	select * from v1;
	A B
	- -
	a b

	select * from v2
	A B
	- -
	b a		-- Whoops, something is clearly wrong


	drop table t1;
	create table t1(a varchar2(1), b varchar2(1), c varchar2(1));
	insert into t1 values ('a','b','c');
	commit;

	select * from v1;
	A B
	- -
	a b

	select * from v2;
	*
	ERROR at line 1:
	ORA-04045: errors during recompilation/revalidation of JPL.V2
	ORA-01730: invalid number of column names specified
	--	A crash is better than a wrong answer.

The problem comes from two directions. First the way Oracle has stored the view definition for V2, and secondly the way in which Oracle does something strange when it is trying to re-populates user_tab_columns as the view is recompiled. I suspect that in both cases, t1.* is expanded at compile time, and Oracle attempts to match the results with the content of user_tab_columns. In the first case, t1.* turns into t1.b, t1.a, which is then aligned with with v2.a, v2.b - hence our contradictory result. In the second case, t1.* returns 3 columns, which cannot be aligned with the two available columns, and at this point, Oracle crashes out instead of trying to delete and re-populate user_tab_columns.

No doubt there are other variations on this theme that you could build to cause further confusion to your applications (try join views, and switching columns from table to table, for example), but this should be sufficient to warn you that there are nasty traps hidden in using the 'select alias.*' lazy option for creating views.

Never use *, always use an explicit list of alias.column_name.

Top of page

.


Errata

Very early on in this chapter (p. 213), I point that if you use 'select table.*' in a view, this is expanded to the list of column available in the table at the time the view was created. There are two errors built into this statement: the first is that I have not included a 'special thought' to say "never use * in the definition of a view" (even though I have done it several times in the chapter to avoid clutter in the examples). The second is that Oracle treats table_name.* differently from just a simple *. This has some interesting side-effects, discussed in the addenda above.

 

Another very early error, was the claim: "if you can run a select statement to select data, you can precede it with ...". This claim highlights a problem that is very easy to forget: that of privileges. There are many occasions when a user complains 'I can do so-and-so in SQL, but as soon as I put the SQL into a stored procedure I get an error about things not existing, or 'insufficent privileges'. The same sort of issue is relevant to views. To be able to create a view, the user must have the create view privilege, but must also have the privilege to select from the underlying objects as a directly granted privilege, not just as a role-based privilege.

 

Top of page


Back to Book Index