create view on union -- workaround?
От | Brian Haney |
---|---|
Тема | create view on union -- workaround? |
Дата | |
Msg-id | 000d01bf3a41$a0cedce0$8101a8c0@specter.fresno.cybernaut.com обсуждение исходный текст |
Список | pgsql-sql |
I just discovered that pg does not support views on unions. Is there a workaround for this? TIA --bkh > -----Original Message----- > From: owner-pgsql-sql-digest@hub.org > [mailto:owner-pgsql-sql-digest@hub.org] > Sent: Saturday, November 27, 1999 8:01 PM > To: pgsql-sql-digest@hub.org > Subject: pgsql-sql-digest V1 #426 > > > > pgsql-sql-digest Saturday, November 27 1999 Volume 01 : > Number 426 > > > > Index: > > Usage of index in "ORDER BY" operations > Re: [SQL] Usage of index in "ORDER BY" operations > Design Question > Re: [SQL] Design Question > Re: [SQL] Design Question > Re: [SQL] Design Question > recusrion > > ---------------------------------------------------------------------- > > Date: Sat, 27 Nov 1999 16:32:15 +0100 > From: Matthias Ackermann <matt@webcraft.ch> > Subject: Usage of index in "ORDER BY" operations > > I notice following behaviour: > > I have a table "adress" with 100'000 adresses > with columns (last_name, first_name, adressline1, etc.) > and an index last_name_idx on the column "last_name". > > The query > "SELECT * FROM adress ORDER BY last_name LIMIT 20 OFFSET 0;" > > takes forever and "EXPLAIN" shows that the index on last_name > is not being used. > > On the other hand > > "SELECT * FROM adress WHERE last_name > '' ORDER BY last_name > LIMIT 20 OFFSET 0;" > > returns the result immediately and "EXPLAIN" shows that the index on > last_name is being used. > > So it seems that inserting a WHERE-clause, even if it doesn't do > anything at all (i.e. doesn't reduce the result-set), > is necessary to force the DB to make use of the index. > > It even says in the FAQ under 4.9) > "Indexes are not used for ORDER BY operations." > > So I was wondering: > Am I doing something wrong here or is the lesson simply: > "Include all attributes of an index in a where-clause > if you want the indexes to be used"? > > Is there a better way to tell the DB to make use of the index? > > BTW: This seems to be true for indexes on multiple columns, i.e. > if having an index on (last_name, first_name) the query had to be: > SELECT * FROM adress WHERE last_name >'' AND first_name >'' > ORDER BY last_name, first_name LIMIT 20 OFFSET 0; > Omitting the where-clause again leads to a very slow query. > > I apologize if this has been discussed many times before ... > > Thanks for your help. > Matt > > ------------------------------ > > Date: Sat, 27 Nov 1999 12:18:02 -0500 > From: Tom Lane <tgl@sss.pgh.pa.us> > Subject: Re: [SQL] Usage of index in "ORDER BY" operations > > Matthias Ackermann <matt@webcraft.ch> writes: > > So it seems that inserting a WHERE-clause, even if it doesn't do > > anything at all (i.e. doesn't reduce the result-set), > > is necessary to force the DB to make use of the index. > > This is true in 6.5: it never even considers an indexscan plan unless > there is a WHERE clause that could make use of the index. 7.0 will > be smarter. (Current CVS sources already know about making an indexscan > plan with no other purpose than to satisfy an ORDER BY; in fact they are > probably *too* eager to make use of an index, and will pick that method > even when a linear scan and explicit sort would be faster. I need to > rejigger the cost estimates to be more realistic, especially by taking > LIMIT into account.) > > regards, tom lane > > ------------------------------ > > Date: Sat, 27 Nov 1999 12:25:55 -0600 (CST) > From: Andy Lewis <alewis@roundnoon.com> > Subject: Design Question > > Hello All! > > Lets say that I have a DB that I am using to save address info on > different brokers. Broker name, address, city, state, zip > > Some brokers have representation in different states. > > How would I be able to save that info so that it can be later selected by > users looking for a broker in a certain state(s)? > > What type of field/table/DB could I save that in for easy reference? > > Thanks! > > Andy > > ------------------------------ > > Date: Sat, 27 Nov 1999 10:41:42 -0800 (PST) > From: Rich Shepard <rshepard@appl-ecosys.com> > Subject: Re: [SQL] Design Question > > On Sat, 27 Nov 1999, Andy Lewis wrote: > > > Lets say that I have a DB that I am using to save address info on > > different brokers. Broker name, address, city, state, zip > > > > Some brokers have representation in different states. > > > > How would I be able to save that info so that it can be later > selected by > > users looking for a broker in a certain state(s)? > > > > What type of field/table/DB could I save that in for easy reference? > > Andy, > > I suggest that you buy (or borrow from a library) a book on relational > data base design. To give you the simple answer to your question: have one > table for the broker's name and identification and a separate table for > their addresses. It's called a many-to-one structure and is created by > normalizing the data. > > Rich > > Dr. Richard B. Shepard, President > > Applied Ecosystem Services, Inc. (TM) > Making environmentally-responsible mining happen. > (SM) > -------------------------------- > 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A. > + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | > rshepard@appl-ecosys.com > > ------------------------------ > > Date: Sat, 27 Nov 1999 12:49:10 -0600 (CST) > From: Andy Lewis <alewis@roundnoon.com> > Subject: Re: [SQL] Design Question > > Thanks, will do.... > > On Sat, 27 Nov 1999, Rich Shepard wrote: > > > On Sat, 27 Nov 1999, Andy Lewis wrote: > > > > > Lets say that I have a DB that I am using to save address info on > > > different brokers. Broker name, address, city, state, zip > > > > > > Some brokers have representation in different states. > > > > > > How would I be able to save that info so that it can be later > selected by > > > users looking for a broker in a certain state(s)? > > > > > > What type of field/table/DB could I save that in for easy reference? > > > > Andy, > > > > I suggest that you buy (or borrow from a library) a book on relational > > data base design. To give you the simple answer to your > question: have one > > table for the broker's name and identification and a separate table for > > their addresses. It's called a many-to-one structure and is created by > > normalizing the data. > > > > Rich > > > > Dr. Richard B. Shepard, President > > > > Applied Ecosystem Services, Inc. (TM) > > Making environmentally-responsible mining happen. > (SM) > > -------------------------------- > > 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A. > > + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | > rshepard@appl-ecosys.com > > > > ------------------------------ > > Date: Sun, 28 Nov 1999 09:50:33 +1100 > From: Julien Cadiou <julienc@vicnet.net.au> > Subject: Re: [SQL] Design Question > > Hi Andy, > > > actually, the postgres site has pretty much what you're after I think. > > If you go under documentation, and going through the "tutorial", > the example they use is quite similar (if I remember correctly) > to what you're after ... > > > Cheers, > > > At 12:25 27/11/1999 -0600, you wrote: > > >Hello All! > > > > > >Lets say that I have a DB that I am using to save address info on > > >different brokers. Broker name, address, city, state, zip > > > > > >Some brokers have representation in different states. > > > > > >How would I be able to save that info so that it can be later selected by > > >users looking for a broker in a certain state(s)? > > > > > >What type of field/table/DB could I save that in for easy reference? > > > > > >Thanks! > > > > > >Andy > > > > > > > > >************ > > > > > > > > > > > <bold>Julien CADIOU > > </bold>Database Administrator > > <bold>VICNET</bold> - Victoria's network > > Phone: (03) 9669 9710 > > Fax: (03) 9669 9805 > > Web: http://www.vicnet.net.au/ > > ------------------------------ > > Date: Sun, 28 Nov 1999 10:00:41 +1100 > From: Julien Cadiou <julienc@vicnet.net.au> > Subject: recusrion > > Hi, > > I'm doing a portal and have a recursion problem. > I just need advice on whether or not I'm wasting my time in thinking I can > do what I need with SQL. > We're basically building a yahoo-like portal. My categories table is as > follows this email. > Each category has a primary key and an owner (the owner is the > primary key > of the category owning that category). I want to extract a site map in one > query: ie: extract the cetagory and its subcategories if any and > any of the > subcategories' subcategories if any etc .... > Right now, in failure to do so with SQL, I'm selecting the whole > thing in a > hash array in perl and reorganising it (which is fine, it worksm but if it > can be "cleaner", that's better !), but I was wondering if I should > continue looking for the SQL answer ... I've seen similar things done, but > somehow it's slightly different to this case and I can't seem to see it ! > I've written a few functions that lead me nowhere, performed self > joins etc > ... but I think I'm looking at it from the wrong angle ... any > suggestions ? > Thanks. > > id|owner|name > - --+-----+------------------------- > 2| 0|Business > 9| 7|How to learn > 12| 9|Good schools > 13| 12|Good schools in Melbourne > 14| 13|Good schools in Carlton > 16| 6|Victorian Private Banks > 17| 1|Barbeques > 18| 17|Victorian barbeques > 3| 1|Sports > 4| 2|Banking > 5| 3|Tennis > 6| 4|Victorian Banks > 7| 5|Lessons > 8| 2|Finance > 1| 0|Outdoors > 0| 0|Home > 19| 5|Tennis Clubs > 20| 19|Melbourne CLubs > 21| 5|Tournaments > 22| 13|Free tuition > > ------------------------------ > > End of pgsql-sql-digest V1 #426 > ******************************* > > > ************ >
В списке pgsql-sql по дате отправления: