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:216275
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
