It's possible in SQL Server, you can create a clustered index on a view and then non-clustered indexes(the clustered index is a must for the non-clustered to be added). Not sure about Oracle though.
I thought it might be of some help to know it's possible on at least one DB server. Ade -----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: 24 August 2005 22:38 To: CF-Talk Subject: SQL/Oracle Indexing a temporary view. Can you create an index to a view created with SQL? I would presume so, but I am not sure what it would look like. I am creating a view with the following syntax. It takes between 1/20th and 1/10th of a second on average to create a view of a few thousand records. <cfquery datasource="STPR_ORA"> CREATE OR REPLACE VIEW CREATED AS ( SELECT ... </cfquery> I then use this view in a fairly complex query to turn values in various rows into columns. <cfquery datasource="STPR_ORA" name="createdQuery"> SELECT DISTINCT D_VST_DATE AS aDATE, N_LOC_AREA AS AREA, ( SELECT SUM(TOTAL_RECORDS) FROM CREATED FOO WHERE FOO.D_VST_DATE = T.D_VST_DATE AND FOO.N_LOC_AREA = T.N_LOC_AREA AND FOO.D_DNT_CNTTYP = 'WB' ) AS WB, --This block repeated for three different D_DNT_CNTTYP values. ... FROM CREATED T ... </cfquery> This works but it is rather slow. Taking about thirty seconds to process the above view of a few thousand records. I would think an index on the D_VST_DATE and/or N_LOC_AREA fields in the view might speed this up a bit. -------------- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216277 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
